Splitting list of names with formula - can you spot what's wrongSolved/Closed

Question

Hi everyone, 

I have a list of 1805 names which are gathered in a single column. These names range in length and "format" (e.g. John Smith, John C Smith, John Albert C Smith, John Paul Albert Jerry Smith) and I basically need to excerpt the last name to one column and the First names (+initials) to another. I am trying to do this by applying first the following formula: 

=RIGHT(A1,SEARCH(" ",A1)) - this is in the second column below 

And then this one: 

=TRIM(LEFT(A1,LEN(A1)-SEARCH(" ",A1)-1)) - this is in the third column below 

The results I'm getting are rather odd. Sometimes they're spot on, and at other times the formula cuts in some (seemingly) random place, which results in a deleted letter (instead of deleting the space before the surname). I have already pasted the original column of data into word and made sure there were no extra spaces at the ends of the cells and I've also pasted into notepad, saved as unicode (to preserve non-English characters) and pasted back in as text. Can anyone see a reason Excel is behaving like this? Here is an example data set: 

(I'm sorry I can't get the columns to align on here, but you can hopefully see what I mean, I put in a dash to mark different columns) 

Abdul G Koroma - Koroma - Abdul G 
Achilles Skordas - s Skordas - Achill 
Achilles Skordas - s Skordas - Achill 
Achilles Skordas - s Skordas - Achill 
Adam Roberts - berts - Adam R 
Adome Blaise Kouassi - ouassi - Adome Blaise 
Agnieszka Jachec-Neale - chec-Neale - Agnieszka J 
Ahila Sornarajah - arajah - Ahila Sor 
Ahmed Mahiou - Mahiou - Ahmed 
Ahmed Mahiou - Mahiou - Ahmed 




Any help and ideas are very much appreciated! 

Thanks!!

p.s. An alternative way to achieve the desired result is of course just as brilliant!

Zohaib R · Answer

Hi excelamateur,  

I see that all the names you have in your list have a common delimiter "space". Using a formula to split such as name is a good idea. However, there is an easier way to do this. Use Text to Column feature in Data tab, split the name into three consecutive cells and then concatenate the cells with first name and the middle initial.  

If you still want to use the formula try the below mentioned formula:  

Put the name in A1, enter this formula in B1:  

=LEFT(A1,SEARCH(" ",A1)-1) & " " & IF(ISERROR(SEARCH(" ",A1,SEARCH(" ",A1)+1))," ",MID(A1,SEARCH(" ",A1)+1,SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1)))  

Enter this formula in C1:  

=IF(ISERROR(SEARCH(" ",A1,SEARCH(" ",A1)+1)),RIGHT(A1,LEN(A1)-SEARCH(" ",A1)),RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(" ",A1)+1)))  

Please revert for clarification.  

Thanks & Regards  
Zohaib R  
#iworkfordell

excelamateur · Answer

Hi Zohaib, 

I first thought of doing this with the Text to Column feature. However, I ran into two problems: I couldn't find a way to merge the first names cells without losing the data in all but the leftmost one, and even though I suspect there must be a feature to do this, I would have still had to do it manually across 1805 rows, all which vary wildly in number of first names. 

Your formula however worked like a charm! Thank you, thank you, thank you ever so much!! 

Kind regards, 
excelamateur

Zohaib R · Answer

Hi vanshu,

I am uploading the sample file here:

http://speedy.sh/jPVRM/NameSplitFormula.xlsx

Hope you will find this useful.

Please do write back to us.

Splitting list of names with formula - can you spot what's wrong

3 responses

Similar discussions

Subscribe To Our Newsletter!