Excel – Separate text string into separate columns in Excel

microsoft excelworksheet-function

I need an Excel formula to separate multiple names currently in one column into three. One single cell currently contains: Jackson Hakel Brynn Holohan John Holohan. I need three columns with first & last name, i.e.:

Column1          Column 2          Column3
Jackson Hakel    Brynn Holohan     John Holohan.

Best Answer

With a constraint / assumption that there's one space between each first name & last name and this repeats for 3 names, and there's no fourth name therein try the following.

Assuming your data is in cell B4

Cell C4 

=LEFT(B4,SEARCH(" ",B4,SEARCH(" ",B4)+1)-1)

Cell D4

=LEFT((RIGHT(B4,LEN(B4)-LEN(C4)-1)),SEARCH(" ",(RIGHT(B4,LEN(B4)-LEN(C4)-1)),SEARCH(" ",(RIGHT(B4,LEN(B4)-LEN(C4)-1)))+1)-1)

Cell E4

=RIGHT(B4,LEN(B4)-(LEN(C4)+LEN(D4)+2))
Related Question