Array formula to extract a word from a string issue

[Folander]

Hi all,

I am trying to extract a surname from a text string, something like thisSmithTaxi. The array formula I am using (and shamelessly nicked off this forum)is below, I think the formula is extracting the characters up until there is anew capital letter in the string.

=TRIM(LEFT(G16,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),G16&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1))

It works brilliantly except when the surname is the same as the next word withthe capital letter, like ThompsonTaxi. It returns the whole word, ThompsonTaxi,rather than just Thompson, like SmithTaxi would return Smith.

Does anyone know of how I can work around this?

Cheers,

Fo.

 

[Eric W]

Maybe:

=TRIM(LEFT(G16,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),"x"&MID(G16,2,999)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),1)-1))

with Control+Shift+Enter. This ignores the first character in the string, and looks for the first upper case letter after that.

 

[Folander]

Maybe:

=TRIM(LEFT(G16,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),"x"&MID(G16,2,999)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),1)-1))

with Control+Shift+Enter. This ignores the first character in the string, and looks for the first upper case letter after that.

That has worked perfectly, thanks very much!

 

[Peter_SSs]

Would this non Ctrl+Shift+Enter formula also work for you?

=LEFT(G16,AGGREGATE(15,6,FIND(CHAR(ROW(INDIRECT("65:90"))),G16,2),1)-1)