Text string manipulation...

[Guest]

Hi.

Seems simple, but not for me...

I have a list of names in the format John N Jones (no commas). I want to
only capture the last name (i.e. Jones) in another cell. How do I accomplish
this. I already tried the Pearson site, but no help. No code please.

Thank you.

Mark

 

[Ragdyer]

With list of names starting in A1, try this in B1:

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

Copy down as needed.

 

[Guest]

Thank you. Can you please explain the use of the ^^^ symbols as used in your
function?

Thank you again.

Mark

 

[Guest]

Try this:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,99)

 

[Peo Sjoblom]

It's just a dummy, something that normally does not occur in a text string

this part

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

will count how many spaces there are in the string, using your example of

John N Jones

gives the result of 2, there are 2 spaces, one after the first name and one
after the initial, so the part of the formula above will return 2

substitute works as follows

string, old_text,new_text,occurrence, so the part that returns 2 is in
occurrence telling excel to substitute the last space with ^^^, then find
will find where it is counting from the left.

So if we replace the above formula with the result 2 it will look like

SUBSTITUTE(A1," ","^^^",2)

replace the second space with ^^^

then

FIND("^^^",SUBSTITUTE(A1," ","^^^",2))

will return the number of characters counted from the left where ^^^ is
located after we replaced the second space with it

that number is 7

LEN(A1)

will count the number of characters in A1

which is 12

so

=RIGHT(A1,12-5)

gives

=RIGHT(A1,5)

return the last 5 characters which is Jones



--

Regards,

Peo Sjoblom