Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

breaking up text fields firstname lastname

Posted on 2013-05-17
6
Medium Priority
?
380 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 1336 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 664 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 1336 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

773 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