Textsplit Full name

[ExcelNewbie2020]

Sirs,

I have a data from column A consist of full name arrange by Given Name, Middle Initial and Surname. I would like to separate the full names to 3 columns. As seen in the table..I hope this could be done by a formula or vba.. many thanks

Book3
	A	B	C	D
1		Expected Result
2	Arrange by Given Name/Middle Initial/Surname	Surname	Given Name	Middle Initial
3	Aidan D Simpson	Simpson	Aidan	D
4	Keith Beau S Caldwell Sr	Caldwell Sr	Keith Beau	S
5	Isis Jon D Johnson	Johnson	Isis Jon	D
6	Mark Joseph Martin K Prince	Prince	Mark Joseph Martin	K
7	Zayne J Shah Jr	Shah Jr	Zayne	J
8	Victoria Monique Z Dominguez	Dominguez	Victoria Monique	Z
9	Sammy Charles T De Vera	De Vera	Sammy Charles	T
10	Penelope P De Leon	De Leon	Penelope	P
11
Sheet3

 

[Snakehips]

@ExcelNewbie2020 Does this help?

MayMrXL.xlsm
	A	B	C	D
1		Expected Result
2	Arrange by Given Name/Middle Initial/Surname	Surname	Given Name	Middle Initial
3	Aidan D Simpson	Simpson	Aidan	D
4	Keith Beau S Caldwell Sr	Caldwell Sr	Keith Beau	S
5	Isis Jon D Johnson	Johnson	Isis Jon	D
6	Mark Joseph Martin K Prince	Prince	Mark Joseph Martin	K
7	Zayne J Shah Jr	Shah Jr	Zayne	J
8	Victoria Monique Z Dominguez	Dominguez	Victoria Monique	Z
9	Sammy Charles T De Vera	De Vera	Sammy Charles	T
10	Penelope P De Leon	De Leon	Penelope	P
11
Sheet16

Cell Formulas
Range	Formula
B3:B11	B3	=TRIM(RIGHT(SUBSTITUTE(A3," "&D3&" ",REPT(" ",999)),999))
C3:C11	C3	=TRIM(LEFT(SUBSTITUTE(A3," "&D3&" ",REPT(" ",999)),999))
D3:D11	D3	=IFERROR(MID(A3,SEARCH(" ? ",A3)+1,1),"")

 

[ExcelNewbie2020]

@ExcelNewbie2020 Does this help?

MayMrXL.xlsm
	A	B	C	D
1		Expected Result
2	Arrange by Given Name/Middle Initial/Surname	Surname	Given Name	Middle Initial
3	Aidan D Simpson	Simpson	Aidan	D
4	Keith Beau S Caldwell Sr	Caldwell Sr	Keith Beau	S
5	Isis Jon D Johnson	Johnson	Isis Jon	D
6	Mark Joseph Martin K Prince	Prince	Mark Joseph Martin	K
7	Zayne J Shah Jr	Shah Jr	Zayne	J
8	Victoria Monique Z Dominguez	Dominguez	Victoria Monique	Z
9	Sammy Charles T De Vera	De Vera	Sammy Charles	T
10	Penelope P De Leon	De Leon	Penelope	P
11
Sheet16

Cell Formulas
Range	Formula
B3:B11	B3	=TRIM(RIGHT(SUBSTITUTE(A3," "&D3&" ",REPT(" ",999)),999))
C3:C11	C3	=TRIM(LEFT(SUBSTITUTE(A3," "&D3&" ",REPT(" ",999)),999))
D3:D11	D3	=IFERROR(MID(A3,SEARCH(" ? ",A3)+1,1),"")

Thank you sir.. it does work.. while i'm running the formula, i noticed that my raw data has an input of middle name in it instead of initial. can we possibly tweak the formula so it can also extract the middle names?

Book3
	A	B	C	D
1		Expected Result
2	Arrange by Given Name/Middle Initial/Surname	Surname	Given Name	Middle Name/Initial
3	Aidan D Simpson	Simpson	Aidan	D
4	Keith Beau S Caldwell Sr	Caldwell Sr	Keith Beau	S
5	Isis Jon D Johnson	Johnson	Isis Jon	D
6	Mark Joseph Martin K Prince	Prince	Mark Joseph Martin	K
7	Zayne J Shah Jr	Shah Jr	Zayne	J
8	Victoria Monique Z Dominguez	Dominguez	Victoria Monique	Z
9	Sammy Charles T De Vera	De Vera	Sammy Charles	T
10	Penelope P De Leon	De Leon	Penelope	P
11	Camilla Wyatt Lennon	Lennon	Camilla	Wyatt
12	Larry Walker Portland	Portland	Larry	Walker
13	Aubrey Chelsea Truett Beckley	Beckley	Aubrey Chelsea	Truett
14	Mark Donald Rhodes De Chavez	De Chavez	Mark Donald	Rhodes
Sheet3

Cell Formulas
Range	Formula
B3:B10	B3	=TRIM(RIGHT(SUBSTITUTE(A3," "&D3&" ",REPT(" ",999)),999))
C3:C10	C3	=TRIM(LEFT(SUBSTITUTE(A3," "&D3&" ",REPT(" ",999)),999))
D3:D10	D3	=IFERROR(MID(A3,SEARCH(" ? ",A3)+1,1),"")

 

[Snakehips]

@ExcelNewbie2020 Sorry but for me the absence of the single initial makes the overall structure of the names too ill-defined to sensibly provide a formula solution.

 

[ExcelNewbie2020]

@ExcelNewbie2020 Sorry but for me the absence of the single initial makes the overall structure of the names too ill-defined to sensibly provide a formula solution.

thanks man, really appreciate..

 

[KevCarter]

@ExcelNewbie2020 Sorry but for me the absence of the single initial makes the overall structure of the names too ill-defined to sensibly provide a formula solution.

Mr. Snakehips. For what it’s worth, your method of getting the first initial is very helpful to me. My names tend to have a period after the initial so I modified the find to “?.” Works like a charm.

Thank you!

 

[Snakehips]

Mr. Snakehips. For what it’s worth, your method of getting the first initial is very helpful to me. My names tend to have a period after the initial so I modified the find to “?.” Works like a charm.

Thank you!

Mr Carter. Worth a lot to think it has proved useful. Thank you!