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

[Solved/Closed]
Report
Posts
2
Registration date
Tuesday November 27, 2012
Status
Member
Last seen
November 28, 2012
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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!

3 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Can please explain with example because when I try with a name the result will not come what I read in the above. requesting you to help on this.
Posts
2
Registration date
Tuesday November 27, 2012
Status
Member
Last seen
November 28, 2012

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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.