Excel: Extract Text from Middle of Alphanumeric String Up to a Number

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!

This question generated 23 answers. To proceed to the answers, click here.

This thread is current as of October 07, 2014.

For more resources for Microsoft Excel