Text string manipulation...
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
"NWO" wrote in message
...
Thank you. Can you please explain the use of the ^^^ symbols as used in
your
function?
Thank you again.
Mark :)
"Ragdyer" wrote:
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.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"NWO" wrote in message
...
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
|