Solved

breaking up text fields firstname lastname

Posted on 2013-05-17
6
324 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now