Searching for data in a cell

**SteveClank** · 03-14-2012, 04:23 PM

A customer sent an excel spreadsheet with all the data merged into column A. I'm trying to find a specific code in the cell and return only that code. For example,

A1

51354070-CCD YSE, MDC2, GMC TRANSITION item5264.54.8131 Made in: America

I want to return only the value "5264.51.8131" in Column B and "America" in Column C.

Problem, they are never the same amount of characters in the cell, so I believe I can't do text to columns. At least, there's nothing to deliminate anything that I can think of. The number I want will always have the format "****.**.****" The Text I want will always be after the ":".

Please let me know if this make sense. Thank you.

**NBVC** · 03-14-2012, 04:29 PM

is the number always after the word item?

**SteveClank** · 03-14-2012, 04:51 PM

I wish there was...there is no standard before the number. Sometimes it's a letter, sometimes it's a number, sometimes it's a space. There's no uniformity to it.

**DGagnon** · 03-14-2012, 04:59 PM

with a couple of assumptions:
1- a "." only appears in the number portion
2- number is always the same format
3- "Made in:" is always preceeding the country and the last item in the string

you can use this

in Column B:
=MID(A1,FIND(".",A1)-4,12)

in Column C:
=MID(A1,FIND("Made in:",A1)+9,100)

**SteveClank** · 03-15-2012, 09:22 AM

Thank you for your help. It mostly solved the problems. The only issue came with assumption one. I thought it was only periods in the numbers, but when I went through the list there were 2 rows that had additional periods. However, this solved 98% of problems. Thank you.