Extract Text from Middle of Alphanumeric String Up to a Number

[croiss]

Hi guys, I'm having trouble figuring out how to complete this data cleanup:

I have a list of items that all follow the same format:

123456789012 Abc company 5 10-04-2013 bcdefgh company y p

The items were copied and pasted from a PDF file, hence the messiness, and the misalignment of the columns... They all pasted over into one column, and I was able to start extracting text.
Some notes about the data:

1. They all start with a 12-character code that is followed by a space before the company title.
2. The company titles are all different lengths, which is why delimiting would not work.
3. Each company name is followed by a number, and then a date, and then another description...

My question is, how can I pull the "ABC company" part out the string without pulling the rest of the string? In other words, I want to pull the text UP TO a number. So in this case, "ABC COMPANY" up to 5.

So far, I have been played around with mid, len, find, search... But haven't been able to figure it out. I've tried also basing it off the =left(a2,min(find...) but couldn't get it to work within the MID function.

Thank you!

 

[Rick Rothstein]

They all work well for the company list... Thank you all so much! Now, how can I modify it to pick up the digit after for the next column, and then the date? The bold and underlined portions of the examples:

555666777888 3M in 6W Corp. 2 11-20-2013 BLAH BLAH P
555666777888 9 to 5 Diners 2 11-20-2013 BLAH BLAH P

Before we could get you those answers, we needed a stable method of getting you the company name what you are asking for above is located after it. Now, with the stable formula I posted in Message #30, we can get you all that you asked for.

Company Name (Normally Entered)
------------------------------------------------------------------------------
=TRIM(MID(A1,14,SUMPRODUCT(IFERROR(SEARCH(" "&{"?","??"}&" ??-??-????",A1),0))-14))

Number After Company Name (Normally Entered)
------------------------------------------------------------------------------
=0+MID(A1,1+SUMPRODUCT(IFERROR(SEARCH(" "&{"?","??"}&" ??-??-????",A1),0)),2)

Date (Array Entered **)
------------------------------------------------------------------------------
=MAX(IFERROR(0+MID(A1,1+SUM(IFERROR(SEARCH(" "&{"?","??"}&" ??-??-????",A1),0))+1+LEN({"?","??"}),10),0))

**Array Entered means the formula is committed with CTRL+SHIFT+ENTER, not just Enter by itself

Note: The number is returned as a real number... the date is returned as a "serial date number" meaning you have to format the cell you place the first formula in as a Date before you copy it down.

 

[István Hirsch]

Very good, but you need to change the red highlighted 16 to 17, then I would agree with you (with 16, the first digit of a two-digit number following the company name is retrieved... 17 cuts it out and still makes it work with single digit numbers following the company name).

Thanks, Rick. So the formula in post #26 correctly:


=TRIM(MID(A1,14,MATCH(TRUE,ISNUMBER(DATEVALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),10))),0)-17))


Back to post #9, if the dates are always of this century (as in the samples), the formula can be made even more reliable by replacing the date ??-??-???? with ??-??-20??

 

[mfexcel]

Learned so much by reading this thread!