Rearranging a formula

[smalik]

A fellow board member helped me earlier with a formula that I have been using without a problem. However, I have new situation where I need help in modifying the formula.

This formula breaks down the name into three columns by First, Last, and Middle name

Here is what I have and need:

I need a modified formula for First Name and Middle Initial so that if the first name has "&" then the formula considers it as part of the first name. For example, Anthony & Sheryl should go in the first name field and Middle Initial field is blank. Everything else is fine.

Here is the formula I am using:

Since I am unable to upload the XL2BB version, here is the Excel area (it does not look pretty, but hopefully get the job done):

Current Result​

Desired Result​

Full Name

First Name

Last Name

Middle Initial

First Name

Last Name

Middle Initial

Albrecht, Doug

Doug

Albrecht

Doug

Albrecht

Bevis, Anthony & Sheryl

Anthony

Bevis

& Sheryl

Anthony & Sheryl

Bevis

Allford, Daniel W

Daniel

Allford

W

Daniel

Allford

W

First Name

IF(A3="","",IFERROR(TRIM(SUBSTITUTE(LEFT(A3,FIND(" ",A3&" ",FIND(",",A3)+2)-1),C3&",","")),LET(t,SUBSTITUTE(A3," & ","/"),SUBSTITUTE(LEFT(t,FIND(" ",t)-1),"/"," & "))))

Last Name

IF(A3="","",IFERROR(LEFT(A3,FIND(",",A3)-1),TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",50)),50))))

Middle Initial

IF(A3="","",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3&", ",C3&",","",1),"/"," & "),","," "),"."," "),B3&" ","",1)))

As usual, any help is greatly appreciated.

 

[ISY]

HI

=CHOOSECOLS(SUBSTITUTE(TEXTSPLIT(TEXTSPLIT(SUBSTITUTE(A3& ", , "," & ",", &"&CHAR(160))," ")," "),",",""),2,1,3)

 

[smalik]

Thanks for the formula. However, this still gives me "& Sheryl" in the Middle Initial Column.

 

[ISY]

Hi

=CHOOSECOLS(SUBSTITUTE(TEXTSPLIT(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A3," & ",CHAR(160)&"&"&CHAR(160))& ", , "," & ",", &"&CHAR(160))," ")," "),",",""),2,1,3)

 

[smalik]

Awesome ... This works ..... Thank you very much