• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Now, how to find the last name?

R

rahul sharma

New Member
Hi,
Thanks for letting me how to find the first name, now i want to know how to find out last name?​

1)rajeev kumar malhotra
2)james smith
3)alicia sarah jones

The output should be only last name.​
Regards,
Rahul​
 
Hi, rahul sharma!
A normal (non-array) formula alternative valid for just 2 or 3 words in the full name:
=DERECHA(A1;LARGO(A1)-SI(LARGO(A1)-LARGO(SUSTITUIR(A1;" ";""))=1;ENCONTRAR(" ";A1;1);ENCONTRAR(" ";A1;ENCONTRAR(" ";A1;1)+1))) -----> in english: =RIGHT(A1,LEN(A1)-IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,FIND(" ",A1,1),FIND(" ",A1,FIND(" ",A1,1)+1)))
Regards!
 
Sajan said:
Hello Rahul,
Here is one approach...

For value in cell A11
=MID(A11,MATCH(2,1/(MID(A11,ROW(OFFSET(A$1,,,LEN(A11))),1)=" "))+1,LEN(A11))

enter with Ctrl + Shift + Enter

Cheers,
Sajan.
Click to expand...

Hi Sajan,
Thanks for your response. But I am afraid to say that the function that you provided does not work!

Please check and advise.

Regards,
Rahul
 
SirJB7 said:
Hi, rahul sharma!
A normal (non-array) formula alternative valid for just 2 or 3 words in the full name:
=DERECHA(A1;LARGO(A1)-SI(LARGO(A1)-LARGO(SUSTITUIR(A1;" ";""))=1;ENCONTRAR(" ";A1;1);ENCONTRAR(" ";A1;ENCONTRAR(" ";A1;1)+1))) -----> in english: =RIGHT(A1,LEN(A1)-IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,FIND(" ",A1,1),FIND(" ",A1,FIND(" ",A1,1)+1)))
Regards!
Click to expand...

Hi SirJB7,

Thanks for your response. This is very long function. Could you tell easy function/formula for taking out last name.

I hope you would tell me the easy one.

Regards,
Rahul
 
Hi, rahul sharma!
There's no easy function or formula for that, since Excel doesn't support string reverse feature (as VBA -macros- does) so you can't easily find the nth occurrence of a character or substring (space in this case).
Regards!
 
Hi Rahul,

here is another one:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)
 
Hi, rahul sharma!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Hello Rahul,
Did you remember to enter the formula with Ctrl + Shift + Enter (instead of just Enter key)?
 
Hi Rahul,
Not sure what to say... I just copied / pasted the above formula from this page onto an Excel worksheet, and it worked for me as expected. (Please note that the formula refers to cell A11 and not A1)

-Sajan.
 
Sajan said:
Hi Rahul,
Not sure what to say... I just copied / pasted the above formula from this page onto an Excel worksheet, and it worked for me as expected. (Please note that the formula refers to cell A11 and not A1)

-Sajan.
Click to expand...

Hi Sajan,

Can you recheck the formula again as you can see below:

(OFFSET(A$1,,,LEN(A11))) ===> Why A$1 is mentioned over here? Im confused here.

Regards,
Rahul
 
Hi Rahul,
I would encourage you to try out a formula before declaring it as non-functional. I know the formula works correctly since I tried it out multiple times.

Regarding your questions...
A$1 in that formula creates an array (such as {1;2;3;4;5} proportionate to the length of the string in cell A11)

In this case, the OFFSET function is returning a range of cells starting with A1 to A15 (assuming the length of the string in A11 is 15). Then wrapping the results from OFFSET into ROW() function returns the row numbers corresponding to A1:A15.

This numeric array is then used to iterate through the string in A11 one character at a time.

The construct ROW(OFFSET(A$1,,,25)) is just a technique to generate an array of numbers.

Cheers,
Sajan.
 
@shrivallabha
Hi!
Tricky but nice, it remembers me the book Aha! Inspiration from Martin Gardner.
Regards!
 
You must log in or register to reply here.
Back
Top