Text to Column using a Formula

smalik

Board Regular
Joined
Oct 26, 2006
Messages
152
Office Version
  1. 365
Platform
  1. Windows
I like to know if there is a formula that can be used to parse the name into three different fields.

Currently, I have the following scenario option:
Full name is listed in Column B (see how it is a free fall and the name could be in any format. I need to parse the name into First, Middle, & Last. The desired results are more structured & Organized

I have used the Text to column option but the way the data is, I don't get the right results 1/2 the time.

Any help is greatly appreciated.
Due to my office compute limitations I cannot install the Add-in so I am only showing a snapshot.

1637607249293.png
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

earthworm

Well-known Member
Joined
May 19, 2009
Messages
703
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Kindly share sample that can be copied to provide solution instead of sharing image.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,598
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@smalik literally just copy and paste your range directly into the reply window and post, it is not as good as XL2BB but it will allow it to be copied as per the request by @earthworm
 

smalik

Board Regular
Joined
Oct 26, 2006
Messages
152
Office Version
  1. 365
Platform
  1. Windows
Kindly share sample that can be copied to provide solution instead of sharing image.
Names.xlsx
ABCDEFGHI
1
2CurrentDesired
3NameFirstMiddleLast
4
5John SmithJohnSmith
6Smith, JohnJohnSmith
7John F. SmithJohnFSmith
8John F SmithJohnFSmith
9John Flynn SmithJohnFlynnSmith
10Smith, John F.JohnFSmith
11Smith, John FJohnFSmith
12Smith, John FlynnJohnFlynnSmith
13John & Jill SmithJohn & JillSmith
14John/Jill SmithJohn & JillSmith
15John & Jill SmithJohnSmithThis could work if it is easier this way
16John/Jill SmithJohnSmithThis could work if it is easier this way
17
18
19
Sheet1


Let's see if this works
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,568
Office Version
  1. 365
Platform
  1. Windows
The best thing would be to get the original data cleaned up and provided in a uniform format.
If that is not possible, this is the best I can come up with based of those sample data formats.

21 11 25.xlsm
ABCDEF
1
2CurrentDesired
3NameFirstMiddleLast
4
5John SmithJohn Smith
6Smith, JohnJohn Smith
7John F. SmithJohnFSmith
8John F SmithJohnFSmith
9John Flynn SmithJohnFlynnSmith
10Smith, John F.JohnFSmith
11Smith, John FJohnFSmith
12Smith, John FlynnJohnFlynnSmith
13John & Jill SmithJohn & Jill Smith
14John/Jill SmithJohn & Jill Smith
Extract Names
Cell Formulas
RangeFormula
D5:D14D5=IFERROR(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&", ",""),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E14E5=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&", ","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F14F5=IFERROR(LEFT(B5,FIND(",",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",50)),50)))
 

smalik

Board Regular
Joined
Oct 26, 2006
Messages
152
Office Version
  1. 365
Platform
  1. Windows
The best thing would be to get the original data cleaned up and provided in a uniform format.
If that is not possible, this is the best I can come up with based of those sample data formats.

21 11 25.xlsm
ABCDEF
1
2CurrentDesired
3NameFirstMiddleLast
4
5John SmithJohn Smith
6Smith, JohnJohn Smith
7John F. SmithJohnFSmith
8John F SmithJohnFSmith
9John Flynn SmithJohnFlynnSmith
10Smith, John F.JohnFSmith
11Smith, John FJohnFSmith
12Smith, John FlynnJohnFlynnSmith
13John & Jill SmithJohn & Jill Smith
14John/Jill SmithJohn & Jill Smith
Extract Names
Cell Formulas
RangeFormula
D5:D14D5=IFERROR(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&", ",""),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E14E5=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&", ","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F14F5=IFERROR(LEFT(B5,FIND(",",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",50)),50)))

Thank you .... Absolutely fantastic ...... You are a life saver ......... This works. However, a few questions:
  • What does "t" represent in the formula in D5?
  • What is the significance of 50 in the F5 formula?
  • How do I modify this formula if the name is written as "Smith,John" or "Smith,John F" or "Smith,John F."?
As I said earlier, you are a life saver even if there is no solution to the newly added option (Smith,John or Smith,John F or Smith,John F.).

Thanks.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,568
Office Version
  1. 365
Platform
  1. Windows
What does "t" represent in the formula in D5?
The LET function allows you to use variables in your formula. I used the variable "t" to be SUBSTITUTE(B5," & ","/")
The definition of t in the formula is what follows the t between the two commas as shown below.
=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&",","")),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
That is, t is the same as the original data but replaces any " & " with "/"
I wanted to eliminate the those spaces around the "&" so that I could use remaining spaces to help identify the various parts of the name.

What is the significance of 50 in the F5 formula?
The 50 is part of replacing all spaces in the name with 50 consecutive spaces. This only applies in cells that have no comma, meaning that the Last name is the last word in the text.
Having inserted those 50 spaces, the formula then takes the right hand 50 characters then trims off all the spaces. this leaves you with the last word from the text - provided that word is not longer than 50 characters. If you did expect any names to be longer than 50 characters you could increase the two 50's near the end of the formula to a bigger number.

How do I modify this formula if the name is written as "Smith,John" or "Smith,John F" or "Smith,John F."?
The best answer is as I said before. ;)
The best thing would be to get the original data cleaned up and provided in a uniform format.


However, failing that, try these.

BTW, I hope that you don't get any names like "Vincent van Gogh" or "Michael van der Veen"

21 11 25.xlsm
ABCDEF
1
2CurrentDesired
3NameFirstMiddleLast
4
5John SmithJohn Smith
6Smith, JohnJohn Smith
7John F. SmithJohnFSmith
8John F SmithJohnFSmith
9John Flynn SmithJohnFlynnSmith
10Smith, John F.JohnFSmith
11Smith, John FJohnFSmith
12Smith, John FlynnJohnFlynnSmith
13John & Jill SmithJohn & Jill Smith
14John/Jill SmithJohn & Jill Smith
15Smith,JohnJohn Smith
16Smith,John FJohnFSmith
17Smith,John F.JohnFSmith
Extract Names
Cell Formulas
RangeFormula
D5:D17D5=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&",","")),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E17E5=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&",","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F17F5=IFERROR(LEFT(B5,FIND(",",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",50)),50)))
 
Last edited:
Solution

smalik

Board Regular
Joined
Oct 26, 2006
Messages
152
Office Version
  1. 365
Platform
  1. Windows
The LET function allows you to use variables in your formula. I used the variable "t" to be SUBSTITUTE(B5," & ","/")
The definition of t in the formula is what follows the t between the two commas as shown below.
=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&",","")),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
That is, t is the same as the original data but replaces any " & " with "/"
I wanted to eliminate the those spaces around the "&" so that I could use remaining spaces to help identify the various parts of the name.


The 50 is part of replacing all spaces in the name with 50 consecutive spaces. This only applies in cells that have no comma, meaning that the Last name is the last word in the text.
Having inserted those 50 spaces, the formula then takes the right hand 50 characters then trims off all the spaces. this leaves you with the last word from the text - provided that word is not longer than 50 characters. If you did expect any names to be longer than 50 characters you could increase the two 50's near the end of the formula to a bigger number.


The best answer is as I said before. ;)



However, failing that, try these.

BTW, I hope that you don't get any names like "Vincent van Gogh" or "Michael van der Veen"

21 11 25.xlsm
ABCDEF
1
2CurrentDesired
3NameFirstMiddleLast
4
5John SmithJohn Smith
6Smith, JohnJohn Smith
7John F. SmithJohnFSmith
8John F SmithJohnFSmith
9John Flynn SmithJohnFlynnSmith
10Smith, John F.JohnFSmith
11Smith, John FJohnFSmith
12Smith, John FlynnJohnFlynnSmith
13John & Jill SmithJohn & Jill Smith
14John/Jill SmithJohn & Jill Smith
15Smith,JohnJohn Smith
16Smith,John FJohnFSmith
17Smith,John F.JohnFSmith
Extract Names
Cell Formulas
RangeFormula
D5:D17D5=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&",","")),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E17E5=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&",","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F17F5=IFERROR(LEFT(B5,FIND(",",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",50)),50)))

You are Amazing.... Thank you.

The whole purpose of this exercise is to clean up the data. If we encounter names like "Vincent van Gogh" or "Michael van der Veen" then those can be looked at manually. However, with 50K+ records, it may be difficult to spot them easily. I guess this is something that needs to be corrected manually. I don't think anyone would mind fixing 1% of the data manually if 99% of the data is correct.

Once again. Thanks for your help.

Regards,
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,568
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,176,085
Messages
5,901,295
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top