How to Convert Text to Columns in Excel with Multiple Spaces

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you may have a dataset with all the information stored within one column using spaces, but you want to keep them within different columns. So, if you are looking for how to convert Text to Columns in Excel with multiple spaces, then you have come to the right place. Today, in this article, I’m going to tell you how to convert Text to Columns in Excel with multiple spaces. Furthermore, for conducting the session, I will use the Microsoft 365 version.


Convert Text to Columns in Excel with Multiple Spaces: 4 Methods

Here, I will demonstrate 4 suitable methods with detailed steps of how to convert Text to Columns in Excel with multiple spaces. For your better understanding, I am going to use the following dataset. Which contains two columns. The dataset is given below where the column named Name City  Designation has mixed information using multiple spaces. So, now I want to separate them into different columns.

Dataset for Converting Text to Columns in Excel with Multiple Spaces


1. Use of Text to Columns Feature in Excel

Here, you can use the Text to Columns feature to convert text to columns in Excel with multiple spaces. The steps are given below to convert Text to Columns in Excel.

Steps:

  • Firstly, select cell C4:C15.
  • Secondly, from the Data tab >> go to the Data Tools option.
  • Finally, choose the Text to Columns feature.

Use of Text to Columns Feature in Excel

At this time, you will see a new dialog box named Convert Text to Columns Wizard – Step 1 of 3.

  • Then, you have to mark Delimited – Characters such as commas or tabs separate each field.
  • After that, press Next.

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

  • Now, you have to mark Space which is under the Delimiters. Here, you can immediately see the modified data in the Data preview box.
  • Then, press Next.

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

  • Firstly, you have to mark Text which is under the Column data format. Here, you should mark the Column data format based on your data type.
  • Then, keep the Destination as it is.
  • Finally, press Finish.

At this time, you will see the warning from Microsoft Excel.

  • Then, press OK on it.

Lastly, you will see the separated columns.

Converted Text with Multiple Spaces to Columns in Excel

Read More: How to Convert Column to Text with Delimiter in Excel


2. Applying Keyboard Shortcuts in Excel

Here, you can use the Keyboard shortcuts to convert text to columns in Excel with multiple spaces. The steps are given below.

Steps:

  • Firstly, select the dataset.
  • Secondly, press ALT+A+E.

Using Keyboard shortcuts to convert text to columns in Excel with multiple spaces

As a result, you will see the dialog box named Convert Text to Columns Wizard – Step 1 of 3.

  • Then, you have to mark Delimited – Characters such as commas or tabs separate each field.
  • After that, press Next.
  • Then, follow the steps of method-1. Actually, this process is exactly similar to the previous method, so I skip these steps here.

After completing these steps, you will get the separated columns.

Converted Columns from Text with Multiple Spaces in Excel


3. Employing Flash Fill Feature to Convert Text to Columns

In this method, I’m going to use the Flash Fill feature to convert text to columns in Excel with multiple spaces. Actually, this is an interesting and simple way. Basically, you will convert the text to columns by following a Pattern. Now, let’s see the steps given below.

Steps:

  • Firstly, you have to write the target result manually up to which you can see Excel’s suggestion. For example, I have written up to 3 rows and then I got the suggestion.

Actually, you have to do this to show Excel a pattern. So, when Excel understands your pattern then Excel will suggest you the output. Below, I have attached the image.

Employing Flash Fill Feature to Convert Text to Columns

  • Subsequently, press ENTER to get the result.

  • Similarly, write the target result manually up to which you can see Excel’s suggestion for the other columns.

On the other hand, if you are using an older version of Excel instead of 365, then you should follow the below steps.

  • Firstly, you have to write the target result manually up to some cells. For example, I have written up to 3 rows. Similarly, I have to do this to show Excel a pattern.

  • Then, select cells including D4 to D6, keep the mouse pointer to the right bottom corner of the D6 cell, and then drag the Fill Handle icon.

Using Fill Handle icon to Convert Text to Columns in Excel

  • After that, click on Auto Fill Options >> then select Flash Fill.

As a result, you will see the following output.

  • Then, do the same thing for the other columns (Column E, and Column F).

Finally, you will get the result. Here, you must do this individually for every column.

Converted Text to Columns with Multiple Spaces using Flash Fill


4. Using VBA to Separate Text into Columns with Multiple Spaces

You can employ a VBA code to separate text into columns with multiple spaces in Excel. So, follow the steps given below.

Steps:

  • Firstly, you need to open your worksheet. Here, you must save the Excel file as an Excel Macro-Enabled Workbook (*xlsm).
  • Secondly, you have to choose the Developer tab >> then select Visual Basic.

Using VBA to Separate Text into Columns with Multiple Spaces

  • At this time, from the Insert tab >> you have to select Module.

  • After that, write down 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

VBA Code to Convert Text to Columns with Multiple Spaces

Code Breakdown

  • Here, I have created a Sub Procedure named Converting_Text_to_Columns.
  • Next, I declare some variables both 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.
  • Then, I used VBA Split functions to split the text into columns based on space delimiters.
  • After that, I used a For Loop within the IF Statement along with the VBA Trim function to trim spaces from multiple texts.
  • As I want to convert multiple rows of values that is why used the outer For Loop.

  • Now, Save the code then go back to Excel File.
  • Then, select the cells containing text >> then from the Developer tab >> select Macros.

  • At this time, select Macro (Converting_Text_to_Columns) and click on Run.

Lastly, you will see the separated columns.

Read More: How to Split Text to Columns Automatically with Formula in Excel


How to Convert Text to Columns with Multiple Delimiters in Excel

Here, in this section, I’m going to tell you how to convert Text to Columns in Excel with multiple delimiters. For your better understanding, see the following dataset where the column named Name City  Designation has mixed information using not only multiple spaces but also commas. So, now I want to separate them into different columns.

How to Convert Text to Columns with Multiple Delimiters in Excel

  • Now, follow the steps of method-1. Actually, this process is exactly similar to method-1 so I skip these steps here.
  • But you have to mark both the Comma and Space which is under the Delimiters in the Convert Text to Columns Wizard – Step 2 of 3 dialog box.
  • Then, again follow the steps of method-1.

Lastly, you will see the separated columns.

Read More: How to Use Line Break as Delimiter in Excel Text to Columns


Practice Section

Now you can practice the explained methods by yourself.

Practice Section to Convert Text to Columns in Excel with Multiple Spaces


Download Practice Workbook

You can download the Practice Workbook that we used to prepare this article. Furthermore, you can change or modify data & find new outputs accordingly.


Conclusion

I hope you found this article helpful. Here, I have explained 4 suitable methods to convert Text to Columns in Excel with multiple spaces. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Excel Text to ColumnsSplitting TextSplit in ExcelLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo