Extracting a string

[iksotof]

Hi I have a list like so: abcde 12%
abcdefxh 2%

As you see they are of different lengths and I need to extract all the letters to the left of the number. I know there are right and left functions which I can use but my limited knowledge sees these as inflexible where I am workign with different length strings? Is there an alternative?

Many thanks Darren.

[HansV]

Will there always be a space between the letters and the number? And if so, could there be spaces in between the letters?

[iksotof]

Thanks Hans, there is always a space between the numbers but the letters are always multiple names so so it coudl be ABC XYZ, abdfgyyy xx.

Hope this helps, thanks Darren.

[HansV]

Try this, it should work even if there is no space before the first digit. It is an array formula, confirm with Ctrl+Shift+Enter instead of just Enter:

=TRIM(LEFT(A1,MIN(FIND(0,SUBSTITUTE(A1&0,{1,2,3,4,5,6,7,8,9},0)))-1))

where A1 is the cell with the text string. The formula can be filled down.

[Rick Rothstein]

Here is another way to do what you want...

=SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),"")

Note that this formula requires the space before the number; however, unlike Hans' formula, you can have number within the text before that last number. So if your names are company names and the company is "3M, Inc" or people such as "John Jones, 3rd", then this formula will still work.