Excel – Combine 3 columns into one in Excel 2011 for Mac

Use FIND-function to determine the position of your first and last space. After that, use theMID-function to split your data into three parts. If you know the position of your spaces, the splitting is easy. Finding the first space is also trivial since FIND goes from left to right. Unfortunatly, there is no way to tell Excel to search from right to left.

So the difficult part is, to determine the last space! Here we use an array formula.
To enter an array formula, you paste the formula into a cell and don't hit Enter.
Instead you press Ctrl+Shift+Enter.

{=MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A))}

If done successfully, Excel will append a starting and ending curly bracket.

Insert all formulas at the given cells and use autofill down

A2: your data goes here
B2:=FIND(" ",A2,1)
C2:=MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A)) <-array formula!
D2:=MID(A2,1,B2-1)
E2:=MID(A2,B2+1,C2-B2-1)
F2:=MID(A2,C2+1,LEN(A2))

_{Result screen}

Logic for writing VBA code is there but it becomes too complicated for me. So I am humbly suggesting a manual method instead which uses Concatenate Function of Excel:

1. In Cell B2 write Formula : =Concatenate(B3,B4,B5,B6,B7,B8) ( If you dont want to write Formula in detail ; You can hold Control Key Pressed and Select the Cells to Concatenate )

2. Copy Formula of B2 and Paste it in the range (C2....Bt2)

3. You will get the desired result in Row 2.
4. Now Select Row 2 Copy it and Paste Special the same in Row 2 as VALUES
5. Delete all the Rows Row 3 to Row 8 now.
6. Repeat the same process Step 1 to Step 5 One by One.

For any doubt, you may please revert back.

With Regards.