How to Convert Text to Columns in Excel with Multiple Spaces

The sample dataset is given below where the column named Name City Designation has mixed information using multiple spaces. We want to separate the values.

Method 1 – Use the Text to Columns Feature

Steps:

Select cells C4:C15.
From the Data tab, go to the Data Tools option.
Choose the Text to Columns feature.

You will see a new dialog box named Convert Text to Columns Wizard – Step 1 of 3.

Mark Delimited – Characters such as commas or tabs separate each field.
Press Next.

You will see another dialog box named Convert Text to Columns Wizard – Step 2 of 3.

Mark Space under Delimiters. You will immediately see the modified data in the Data preview box.
Press Next.

You will see a dialog box named Convert Text to Columns Wizard – Step 3 of 3.

Mark Text under the Column data format. You should mark the Column data format based on your data type.
Keep the Destination as-is.
Press Finish.

You will see the warning from Microsoft Excel.

Press OK on it.

You will see the separated columns.

Method 2- Applying Keyboard Shortcuts in Excel

Steps:

Select the dataset.
Press Alt + A + E.

You will see the dialog box named Convert Text to Columns Wizard – Step 1 of 3.

Follow the rest of Method 1 to go through the Text to Columns Wizard.

You will get the separated columns.

Method – Using Flash Fill to Convert Text to Columns

Steps:

Create three additional columns to store the results.
Write the target results manually, row-by-row, until you start getting Excel’s suggestions. We needed to write up to 3 rows to get the suggestion.

Once you see a preview of the result values in cells down the column, press Enter.

Repeat for the other cells.

If you are using an older version of Excel instead of 365:

Write the target results for the first three rows (including the headers).

Select cells D4:D6, go to the bottom-right corner of the D6 cell, then drag the Fill Handle icon down.

Click on Auto Fill Options and select Flash Fill.

You will see the following output.

Repeat for the other columns.

Method 4 – Using VBA to Separate Text into Columns with Multiple Spaces

Steps:

Save the Excel file as an Excel Macro-Enabled Workbook (*xlsm).
Go to the Developer tab and select Visual Basic.

From the Insert tab, select Module.

Insert the code given below in Module1.

Option Explicit
Public Sub Converting_Text_to_Columns()
Dim my_cell As Range
Dim my_first_row As Integer
Dim my_last_row As Integer
Dim split_data() As String
Dim i_L As Long, j_L As Long
my_last_row = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For my_first_row = 4 To my_last_row
Set my_cell = Cells(my_first_row, 3)
split_data = Split(Expression:=my_cell.Value, Delimiter:=" ")
For i_L = LBound(split_data) To UBound(split_data)
If Trim$(split_data(i_L)) <> vbNullString Then
my_cell.Offset(ColumnOffset:=j_L).Value = Trim$(split_data(i_L))
j_L = j_L + 1
End If
Next i_L
j_L = 0
Next my_first_row
End Sub

Code Breakdown

We have created a Sub Procedure named Converting_Text_to_Columns.
We declared some variables my_first_row and my_last_row as Integer, my_cell as Range, split_data as String, and i_L and j_L as Long.
We used VBA Split functions to split the text into columns based on space delimiters.
We used a For Loop within the IF Statement along with the VBA Trim function to trim spaces from multiple texts.
We want to convert multiple rows of values with the outer For Loop.