Separate text string into separate columns in Excel

Question

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.

Answer 1

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))

Answer 2

I will show you a very basic example which you should be able to use similarly in your case. I kept the formulas broken apart to make learning easier.

First I start with some data, split by a space like you have. Essentially you want to delimit your data at every other space so that is what I will do.

First I find the location of the first space:

Secondly I find the location of the second space, starting from where we found the first one:

Now that we know the location of the second space, simply trim the original text up to the second space:

Lastly, show the remaining text with the front trimmed off:

Now to keep splitting apart, simply repeat the previous methods from the last cell. As I stated in the beginning, the formulas are broken apart so you can learn but to simplify how much space is taken up, you can combine formulas.

Hope this helps!

Answer 3

With data in A1, in B1 enter:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:A)*999-998,999)) & " " & TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:B)*999-998,999))

In C1 enter:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:C)*999-998,999)) & " " & TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:D)*999-998,999))

and in D1 enter:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:E)*999-998,999)) & " " & TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:F)*999-998,999))