Solved

breaking up text fields firstname lastname

Posted on 2013-05-17
6
358 Views
Last Modified: 2013-05-26
I have the following list of names that are in one cell, here is a sample below

Brown, Sylvia Anne Mrs
Tanner,  John George and Mary May
Smith, Phyllis Irene Mrs


I have over 5000 names I need the surname is always with a comma after ie smith,

I need to put into 3 fields if possible

Column  1 = Salutation eg Mr
Column 2 = Firstname eg Sylvia Anne
Column 3 = Lastname  eg Brown
names.xlsx
0
Comment
Question by:Amanda Walshaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 39176424
Hi, Flyfishtrout15,

Please see attached.

Regards,
Brian.names-V2.xlsx
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 334 total points
ID: 39176428
I put the following formulas in B1:D1, then copied down:
=IFERROR(VLOOKUP(TRIM(RIGHT(SUBSTITUTE(A1," ","   "),4)),Salutations,1,FALSE),"")
=TRIM(MID(LEFT(A1,LEN(A1)-LEN(B1)),LEN(D1)+2,99))
=LEFT(A1,FIND(",",A1)-1)
namesQ28131968.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39176432
In my first formula, Salutations is a named range that contains possible salutations (Mr, Mrs, Rev, Dr, Atty). The formula allows up to 4 letters for these salutations, though that can be easily increased.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Amanda Walshaw
ID: 39178223
hello only the last column worked, here is the list of the full sample of names. I have done exactly your formula only changing it for the columns.
Cannot understand why its not working as it is straight forward to me.
full-sample.xlsx
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 166 total points
ID: 39178543
Flyfishtrout15,

Please see attached. A few points...
(1) The main change was to handle the extra salutations - the other formulas are unchanged.
(2) Column I is a work column. It makes the formulas a bit more straightforward, but let me know if you want me to get rid of it.
(3) I checked "my" last names against yours and they agree.

Regards,
Brian.full-sample-V2.xlsx
0
 
LVL 81

Accepted Solution

by:
byundt earned 334 total points
ID: 39178622
For the full sample of names, I needed to adjust the formula for salutations to allow five letters. I also had to add Miss, Ms, Lady and Major to the named range Salutations. Finally, I had to adjust the formula to refer to column E rather than A.
=IFERROR(VLOOKUP(TRIM(RIGHT(SUBSTITUTE(E2," ","    "),5)),Salutations,1,FALSE),"")

The other two formulas were unchanged (except for differences in column references):
=TRIM(MID(LEFT(E2,LEN(E2)-LEN(F2)),LEN(H2)+2,99))          first name
=LEFT(E2,FIND(",",E2)-1)                last name

The revised formulas agreed perfectly with the list of last names in your workbook.
full-sampleQ28131968.xlsx
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question