Text to Column using a Formula

[smalik]

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.

 

[earthworm]

Kindly share sample that can be copied to provide solution instead of sharing image.

 

[MARK858]

@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]

Kindly share sample that can be copied to provide solution instead of sharing image.

Names.xlsx
	A	B	C	D	E	F	G	H	I
1
2		Current		Desired
3		Name		First	Middle	Last
4
5		John Smith		John		Smith
6		Smith, John		John		Smith
7		John F. Smith		John	F	Smith
8		John F Smith		John	F	Smith
9		John Flynn Smith		John	Flynn	Smith
10		Smith, John F.		John	F	Smith
11		Smith, John F		John	F	Smith
12		Smith, John Flynn		John	Flynn	Smith
13		John & Jill Smith		John & Jill		Smith
14		John/Jill Smith		John & Jill		Smith
15		John & Jill Smith		John		Smith	This could work if it is easier this way
16		John/Jill Smith		John		Smith	This could work if it is easier this way
17
18
19
Sheet1

Let's see if this works

 

[Peter_SSs]

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
	A	B	C	D	E	F
1
2		Current		Desired
3		Name		First	Middle	Last
4
5		John Smith		John		Smith
6		Smith, John		John		Smith
7		John F. Smith		John	F	Smith
8		John F Smith		John	F	Smith
9		John Flynn Smith		John	Flynn	Smith
10		Smith, John F.		John	F	Smith
11		Smith, John F		John	F	Smith
12		Smith, John Flynn		John	Flynn	Smith
13		John & Jill Smith		John & Jill		Smith
14		John/Jill Smith		John & Jill		Smith
Extract Names

Cell Formulas
Range	Formula
D5:D14	D5	=IFERROR(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&", ",""),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E14	E5	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&", ","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F14	F5	=IFERROR(LEFT(B5,FIND(",",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",50)),50)))

 

[smalik]

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
	A	B	C	D	E	F
1
2		Current		Desired
3		Name		First	Middle	Last
4
5		John Smith		John		Smith
6		Smith, John		John		Smith
7		John F. Smith		John	F	Smith
8		John F Smith		John	F	Smith
9		John Flynn Smith		John	Flynn	Smith
10		Smith, John F.		John	F	Smith
11		Smith, John F		John	F	Smith
12		Smith, John Flynn		John	Flynn	Smith
13		John & Jill Smith		John & Jill		Smith
14		John/Jill Smith		John & Jill		Smith
Extract Names

Cell Formulas
Range	Formula
D5:D14	D5	=IFERROR(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&", ",""),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E14	E5	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&", ","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F14	F5	=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]

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
	A	B	C	D	E	F
1
2		Current		Desired
3		Name		First	Middle	Last
4
5		John Smith		John		Smith
6		Smith, John		John		Smith
7		John F. Smith		John	F	Smith
8		John F Smith		John	F	Smith
9		John Flynn Smith		John	Flynn	Smith
10		Smith, John F.		John	F	Smith
11		Smith, John F		John	F	Smith
12		Smith, John Flynn		John	Flynn	Smith
13		John & Jill Smith		John & Jill		Smith
14		John/Jill Smith		John & Jill		Smith
15		Smith,John		John		Smith
16		Smith,John F		John	F	Smith
17		Smith,John F.		John	F	Smith
Extract Names

Cell Formulas
Range	Formula
D5:D17	D5	=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&",","")),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E17	E5	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&",","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F17	F5	=IFERROR(LEFT(B5,FIND(",",B5)-1),TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",50)),50)))

 

[smalik]

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
	A	B	C	D	E	F
1
2		Current		Desired
3		Name		First	Middle	Last
4
5		John Smith		John		Smith
6		Smith, John		John		Smith
7		John F. Smith		John	F	Smith
8		John F Smith		John	F	Smith
9		John Flynn Smith		John	Flynn	Smith
10		Smith, John F.		John	F	Smith
11		Smith, John F		John	F	Smith
12		Smith, John Flynn		John	Flynn	Smith
13		John & Jill Smith		John & Jill		Smith
14		John/Jill Smith		John & Jill		Smith
15		Smith,John		John		Smith
16		Smith,John F		John	F	Smith
17		Smith,John F.		John	F	Smith
Extract Names

Cell Formulas
Range	Formula
D5:D17	D5	=IFERROR(TRIM(SUBSTITUTE(LEFT(B5,FIND(" ",B5&" ",FIND(",",B5)+2)-1),F5&",","")),LET(t,SUBSTITUTE(B5," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & ")))
E5:E17	E5	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5&", ",F5&",","",1),"/"," & "),","," "),"."," "),D5&" ","",1))
F5:F17	F5	=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]

You're welcome. Thanks for the follow-up.