Solved

breaking up text fields firstname lastname

Posted on 2013-05-17
6
334 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Location of files for Quick Access Toolbar (QAT) settings in Office 2010 3 35
VBA Help 18 44
Delete all empty columns using VBA 7 41
VBA Works in Excel 2010 Not 2016 Help! 5 18
My experience with Windows 10 over a one year period and suggestions for smooth operation
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 the scrolling table in Microsoft Excel using the INDEX function.

770 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