Extract Last Name

[shivya]

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 Name	Middle Name	Last Name
Shivya Jain	Shivya		Jain
Shweta Jain	Shweta		Jain
Mohan Prakash Sharma	Mohan		Prakash Sharma
Juhi Chawla	Juhi		Chawla

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

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

 

[NickRed18]

Your

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:

=RIGHT(A17,LEN(A17)-IF(ISERROR(SEARCH(" ",A17,SEARCH(" ",A17)+1)),SEARCH(" ",A17),SEARCH(" ",A17,SEARCH(" ",A17)+1)))

The

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.

=IF(ISERROR(SEARCH(" ",A17,SEARCH(" ",A17)+1)),"",MID(A17,SEARCH(" ",A17)+1,SEARCH(" ",A17,SEARCH(" ",A17)+1)-SEARCH(" ",A17)-1))