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]

Rick, the number of digits is not what seems to be causing the problem, as it seems like the max is 2 digits ... here are two examples of companies that the function did not work for:

[TABLE="width: 664"]
<tbody>[TR]
[TD]111222333444 XYZOK COR COMP CED ABC 5 10-31-2013* BLAH BLAH BLAH BLAH * P[/TD]
[/TR]
[TR]
[TD]555666777888* WOW WONDER AMAZE XYZ G 2 11-20-2013* BLAH BLAH P
[/TD]
[/TR]
</tbody>[/TABLE]

OF course, the formatting is the only thing I retained from the actual data because of confidentiality issues...

Other than the leading space being generated by the asterisk (which you did not tell us about prior to now) immediately after the 12-character code for that second one (easily handled with a Trim function call), what is wrong with the outputs? I get all the text from after the 12-character code up to the number after what you told us in Message #1 was the company name.

 

[Rick Rothstein]

Try...

=LEFT(REPLACE(A2,1,13,""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},REPLACE(A2,1,13,"")&"0123456789")-1))

Try your formula where the company name is 3M or any other name contain a number.

 

[Aladin Akyurek]

Try your formula where the company name is 3M or any other name contain a number.

Try to post what that example is...

 

[Rick Rothstein]

Try to post what that example is...

446677999005 3M Corp 5 11-25-2013 BLAH BLAH Y O

446677999005 Direct2U Inc. 5 11-25-2013 BLAH BLAH Y O

 

[Aladin Akyurek]

446677999005 3M Corp 5 11-25-2013 BLAH BLAH Y O

446677999005 Direct2U Inc. 5 11-25-2013 BLAH BLAH Y O

I failed to think of such names indeed...

 

[István Hirsch]

Try this 3 function formula:

=MID(A1;14;MIN(FIND(" "&{0;1;2;3;4;5;6;7;8;9};A1&" 0 1 2 3 4 5 6 7 8 9 "))-14)

 

[Rick Rothstein]

Try this 3 function formula:

=MID(A1;14;MIN(FIND(" "&{0;1;2;3;4;5;6;7;8;9};A1&" 0 1 2 3 4 5 6 7 8 9 "))-14)

It does not work on one where the company name starts with a number; for example...

555666777888 3M Corp. 2 11-20-2013* BLAH BLAH P

555666777888* 3M Corp. 2 11-20-2013* BLAH BLAH P

 

[István Hirsch]

Modified to include new samples:

=TRIM(MID(A1,14,MIN(FIND(" "&{0,1,2,3,4,5,6,7,8,9},A1&" 0 1 2 3 4 5 6 7 8 9 ",FIND(" ",A1)+1))-14))

NOTE: formula in post #9 seems to treat each sample correctly, more reliably, using less functions.

 

[Aladin Akyurek]

Try this 3 function formula:

=MID(A1;14;MIN(FIND(" "&{0;1;2;3;4;5;6;7;8;9};A1&" 0 1 2 3 4 5 6 7 8 9 "))-14)

I devised MIN/SEARCH|FIND for extracting a numeric part from a target string in case the string does not contain multiple numeric bits. A manipulation to overcome the foregoing restriction in both the search and concatenation string is a good idea for it would sometimes help.

A counter example that we might have here is:

667889000123 2MY2 FIRST INC CORP 13 02-09-2013*BLAH BLAH N O

 

[Rick Rothstein]

Modified to include new samples:

=TRIM(MID(A1,14,MIN(FIND(" "&{0,1,2,3,4,5,6,7,8,9},A1&" 0 1 2 3 4 5 6 7 8 9 ",FIND(" ",A1)+1))-14))

Excellent... this formula works with everything I could think of to throw at it... well done.

NOTE: formula in post #9 seems to treat each sample correctly, more reliably, using less functions.

Yes, with a small modification which I will address in my next comment below.