[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

breaking up text fields firstname lastname

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
Amanda Walshaw
Asked:
Amanda Walshaw
  • 3
  • 2
3 Solutions
 
redmondbCommented:
Hi, Flyfishtrout15,

Please see attached.

Regards,
Brian.names-V2.xlsx
0
 
byundtCommented:
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
 
byundtCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
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
 
redmondbCommented:
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
 
byundtCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now