Extract Last Name

shivya

New Member
Joined
Feb 1, 2016
Messages
1
Hello to all,

This is my Raw Data, I have to extract firstname, middlename and lastname in seperate column.

First Name = =LEFT(A16,SEARCH(" ",A16))

Last Name = =RIGHT(A17,LEN(A17)-SEARCH(" ",A17))

Full Name
First NameMiddle NameLast Name
Shivya JainShivya Jain
Shweta JainShweta Jain
Mohan Prakash SharmaMohan Prakash Sharma
Juhi ChawlaJuhi Chawla


<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Last Name is not working Properly. Please help me out.
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
Your
Code:
Search(" ",A17)
is still looking at the very first space in the string rather than the second space. Works fine for the full names without a middle one - there's only one space to worry about, but it isn't picking up that 2nd space later in the string. Try replacing the formula for the Last Name to this:
Code:
=RIGHT(A17,LEN(A17)-IF(ISERROR(SEARCH(" ",A17,SEARCH(" ",A17)+1)),SEARCH(" ",A17),SEARCH(" ",A17,SEARCH(" ",A17)+1)))
The
Code:
Search(" ",A17,Search(" ",A17)+1)
begin another search 1 character after the first space in the full name. The IF statement determines if there are 2 or 3 total names, and uses the corresponding search function.

You can put this in the middle name column. Let me know if there's any issue with it.
Code:
=IF(ISERROR(SEARCH(" ",A17,SEARCH(" ",A17)+1)),"",MID(A17,SEARCH(" ",A17)+1,SEARCH(" ",A17,SEARCH(" ",A17)+1)-SEARCH(" ",A17)-1))
 
Last edited:

Forum statistics

Threads
1,077,828
Messages
5,336,621
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top