Find last used letter in cell and return all before it

[ScabbyDog]

I have various cells with different length words and names with numbers after them, eg John Jack 28',,,,, . I want to find the last LETTER used in cell and return everything before it so in this example just leave me with John Jack. Possible?

[Harribone]

Is there a a max number of words/names at the start?

[ScabbyDog]

The names could be any length and any amount of words. Could be John Johnathan Joseeeeeeeee Jacko Macnkkkkkkkkkkkkkkkkkko for example! or even longer!

[Ace_XL]

Try

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),LEN(SUBSTITUTE(A1," ",REPT(" ",100)))-100))

[ScabbyDog]

Doesn't do what I want ACE. I should mention there could be any amount of numbers after the name so it could be John Jack 28', 52', 55', 95',

[Alf]

Perhaps a macro like this would work for you?

Please Login or Register  to view this content.

Change range to suit your needs.

Alf

[Ghozi Alkatiri]

use array formula
{=LEFT(A1,MAX(IF(MID(A1,ROW(1:100),1)=" ",ROW(1:100))))}

[benishiryo]

hi there. if you want to find the last letter, shouldn't it be "k" from "John Jack 28'"? if you want everything before the numbers, try:
=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

[ScabbyDog]

Thanks guys. Works great!