Splitting list of names with formula - can you spot what's wrong
Solved/Closed
excelamateur
Posts
2
Registration date
Tuesday November 27, 2012
Status
Member
Last seen
November 28, 2012
-
Nov 27, 2012 at 10:16 AM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Nov 30, 2012 at 05:02 PM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Nov 30, 2012 at 05:02 PM
Related:
- Splitting list of names with formula - can you spot what's wrong
- My contacts list names - Guide
- Ideogram ai names - Guide
- Wow monk names - Guide
- Counter strike 1.6 cheats list - Guide
- How to change your best friends list on snapchat to 3 - Guide
3 responses
Zohaib R
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
69
Nov 27, 2012 at 01:05 PM
Nov 27, 2012 at 01:05 PM
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
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
Posts
2
Registration date
Tuesday November 27, 2012
Status
Member
Last seen
November 28, 2012
Nov 28, 2012 at 04:37 AM
Nov 28, 2012 at 04:37 AM
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
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
69
Nov 30, 2012 at 05:02 PM
Nov 30, 2012 at 05:02 PM
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.
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.
Nov 29, 2012 at 04:34 AM