return partial string

[alex]

Hello experts,

I'm attempting to write a function that will return part of a name.

E.g., I have the name John Doe in cell A1. I want the cell B1 to
populate with "Doe."
I could use a number of functions that come to mind, but none that
would deal with different length strings.

E.g., I have the name John Smith in cell A2. I want the cell B2 to
populate with "Smith."

thanks for any help,
alex

 

[John Bundy]

You could use a right/find combo but Data->text to columns Tab delemited with
space checked would be easier

 

[Max]

One way ..

Assume names posted are representative, running in A1 down
In B1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99)
Copy down

 

[Teethless mama]

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

 

[Peo Sjoblom]

If you always want the last text string after the last space

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,256)


however if it is always just one space (if it is first space last) in the
cell you can use

=MID(A1,FIND(" ",A1)+1,256)

 

[alex]

If you always want the last text string after the last space

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,256)

however if it is always just one space (if it is first space last) in the
cell you can use

=MID(A1,FIND(" ",A1)+1,256)

--
Regards,

Peo Sjoblom










- Show quoted text -

Thank you all for your help...I'm excited to go test the suggestions.

alex