Removing text

[Guest]

I have a column of names with text and numbers at the end

eg

J Smith ABC 5.0

I'd like to remove the last characters leaving the names only.

Can anyone help?

Thanks

DR

 

[Frank Kabel]

Hi
maybe a formula like
=TRIM(LEFT(A1,LEN(A1)-3))
in an adjacent column

 

[Jason Morin]

One way:

=LEFT(A1,MIN(IF(ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN
(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))))-1)

Array-entered (press ctrl/shift/enter).

HTH
Jason
Atlanta, GA

 

[Guest]

Thanks for that, it works a treat.

DR

Hi
maybe a formula like
=TRIM(LEFT(A1,LEN(A1)-3))
in an adjacent column

 

[Cesar Zapata]

Hi,

it depends for example if all the names are in this format
firstname last name abc 5.0 then use this quite long formula.

=LEFT(A1,FIND(" ",A1)-1)&" " & IF(ISERR(MID(A1,FIND("
",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND("
",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND("
",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND("
",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))




if you know that the last characters are alwasy the same number of
letters like for example "abc 5.0" contains 7 characters then you can use.

=LEFT(A1,LEN(A1)-7) ' to remove the last 7 characters.




Hope it helps.

Thanks,

Cesar Zapata

 

[Evan]

If there are always two words to the names, try Data >
Text to Columns > Space Delimited and concatenate the
names.

Evan Weiner
Richland WA