How to Split Data into Multiple Columns in Excel

In general, users store Customer Info in a single column. Sometimes users need to split data in Excel into multiple columns. Excel’s multiple features, VBA Macro, Power Query, and functions split data into multiple columns.

Let’s say we have Customer Name and Other Infos in a single column along with their IDs. We want the names and Infos to be split into multiple columns.

Dataset-How to Split Data in Excel into Multiple Columns

In this article, we use features such as Text to Columns and Flash Fill, VBA Macro, Power Query, and Functions to split data in Excel into multiple columns.


Download Excel Workbook


5 Easy Ways to Split Data into Multiple Columns in Excel

Method 1: Using Text to Columns Feature

Excel provides the Text to Columns feature in the Data tab. Text to Columns features allows split data into multiple columns considering delimiters (i.e., Space, Comma, etc.) as separating indicators.

Step 1: Select the entire column you want to split the data from. Go to the Data tab > Click on Data Tools > Select Text to Columns.

Text to Columns-How to Split Data in Excel into Multiple Columns

Step 2: The Convert Text to Columns Wizard appears. In the Wizard,

Mark Delimited as Choose the file type that best describes your data.

Click on Next.

Wizard

Step 3: Convert Text to Columns Wizard- Step 2 of 3 pops up. In the Wizard,

Tick Space as Delimiters. You can choose other options (i.e., Tab, Semicolon, Comma, etc,) as delimiters depending on your data type.

Click on Next.

2nd Wizard

Step 4: Last Convert Text to Columns Wizard appears.

Mark the Column data format as in any preferred option (i.e., General). You can mark Text as outcome data format.

Click on Finish.

3rd Wizard

Return to the Worksheet, furnish the data as you need. At last, the outcome will be similar as depicted in the following picture.

Text to Columns outcome

Read More: How to Split Comma Separated Values into Rows or Columns in Excel


Method 2: Using VBA Macro to Split Data into Multiple Columns

VBA Macros are efficient in achieving objective-oriented outcomes. The VBA SPLIT function can split data from a single column into multiples. From the dataset, it’s obvious that only a single column holds all the data as shown in the below image and we want to split them.

vba macro-How to Split Data in Excel into Multiple Columns

Step 1: To open Microsoft Visual Basic window, Press ALT+F11 altogether. In the window, Click on Insert (from the Toolbar) > Select Module.

Module

Step 2: In the Module, Paste the following macro.

Sub SplitData()
For I = 5 To Cells(Rows.Count, 3).End(xlUp).Row
wrksplit = Split(Cells(I, 3).Value & " #", " ", , vbTextCompare)
Cells(I, 3).Resize(1, UBound(wrksplit)).Value = wrksplit
Next I
End Sub

Macro

The macro starts counting rows from row 5 up to the last used row. Then, wrksplit variable is assigned to the VBA SPLIT function. The SPLIT function takes the Space delimiter as a separation indicator. Afterward, the macro resizes the cells declaring array length using the VBA UBound function.

Step 3: Use the F5 key to run the macro. Now, back to the Worksheet, you see macro splits the Infos into multiple columns.

Raw result

Restructure the macro outcomes according to your desire. In the end, the outcome looks like the image you are seeing below.

Refurbished result

You can split the data into as many columns as you want just you have to insert a Space in separations.


Method 3: Split Data into Multiple Columns Using Power Query

Excel Power Query is a powerful tool to shape data. Power Query Editor offers a customized option to split data into columns.

Step 1: Select the entire range. Then, Go to Data > Click on From Table/Range (within Get & Transform data section).

power query-How to Split Data in Excel into Multiple Columns

Step 2: Excel brings out the Create Table dialog box.

Tick or Untick My table has headers option depending on your data type.

Click on OK.

Create table dialog box

Step 3: The Power Query Editor appears. In the Editor,

Select the specific column that resides the data you want to split.

After that, Select Home > Click on Split Column (from the Transform section) > Select By Delimiter.

Power Query Editor

Step 4: Split Column by Delimiter dialog box opens. In the dialog box,

Choose Space as Select or enter delimiter option.

Mark Each occurrence of the delimiter as Split at options. This enables Power Query to split data as points where space is existing. You can choose other delimiters such as Comma, Semicolon, etc. to split data at.

Click on OK.

Delimiter

Step 5: Clicking OK in the Split Column by Delimiter dialog box takes you to the Power Query Editor window. In the Editor window,

Click on Close & Load > Select Close & Load.

Close and load

➤ After that Excel closes the Power Query Editor and loads the split data in Worksheet. Arrange the data according to your taste.

Result

Read More: Excel Split Data into Columns by Comma (7 Methods)


Method 4: Flash Fill to Split Data into Multiple Columns

Flash Fill is a handy tool to fast-fill entries in Excel. Flash Fill mimics the previously inputted entries and fetches data from existing entries. All it needs is to provide sufficient inputs to mimic.

Step 1: Type the 1stentry Under the First Name header. Go to Home > Editing > Fill > Select Flash Fill.

Flash Fill-How to Split Data in Excel into Multiple Columns

Instantly, all the other entries within the First Name header get inserted with First Names. Flash Fill mimics the 1st entry and fetches all the 1st words from the adjacent column.

Result

Step 2: Repeat Step 1 for other columns such as Last Name and Company Name. However, provide a minimum of 2 entries in the Company Name column to allow Flash Fill in other cells successfully.

Final result of splitting data into columns

Flash Fill is efficient for a limited variety of entries. In case you have varieties of entries it’s better not to use Flash Fill. Because in that case, Flash Fill will mimic the data completely wrong.


Method 5: Insert Data into Multiple Columns Using Functions

We can fetch left, right or middle sections of an entry using LEFT, RIGHT, MID, SUBSTITUTE, and SEARCH functions. As it’s not efficient to use lengthy formulas to fetch complicated data types. We use a simple form of our dataset to be extracted by combined functions.

Functions-How to Split Data in Excel into Multiple Columns

Step 1: Type the following formula in any blank cell (i.e., D5) to extract First Names.

=LEFT(C5,SEARCH(" ",C5)-1)

The SEARCH function displays the character length of the 1st Space character in cell C5. By subtracting 1 from the length the LEFT function fetches the rest of the characters until the 1st Space.

Formula insertion

Step 2: Hit ENTER to apply the formula in cell C5. Then, drag the Fill Handle to apply the formula to the other cells within the same column.

Result

Step 3: Now, use the below formula for inserting Last Names.

=IFERROR(MID(C5,SEARCH(" ",C5)+1,SEARCH(" ",C5,SEARCH(" ",C5)+1)-SEARCH(" ",C5)),"")

 In the formula SEARCH(” “,C5)+1 passes 1 plus 1st Space length as the start_num argument for the MID function. After that, the SEARCH(” “,C5,SEARCH(” “,C5)+1)-SEARCH(” “,C5) portion delivers the num_chars to display the last names in the Last Names cells. In instances of error the IFERROR function restricts Excel to display any errors rather than a single Space.

Formula insertion

Step 4: Use the ENTER key to apply the formula and drag the Fill Handle to apply the formula in other cells as depicted in the following image.

Result

Step 5: For Company Name use the below formula in respective cells.

=IF(LEN(C5)-LEN(SUBSTITUTE(C5," ",""))=1,RIGHT(C5,LEN(C5)-SEARCH(" ",C5)),RIGHT(C5,LEN(C5)-SEARCH(" ",C5,SEARCH(" ",C5)+1)))

The formula performs a logical_text (i.e., LEN(C5)-LEN(SUBSTITUTE(C5,” “,””))=1) using the IF function for each entry. If the test result TRUE, the formula inserts performs RIGHT(C5,LEN(C5)-SEARCH(” “,C5)) to fetch characters otherwise RIGHT(C5,LEN(C5)-SEARCH(” “,C5,SEARCH(” “,C5)+1)) inserts the desired characters.

Though it’s obvious from our data that entries contain more than 1 Space and the SUBSTITUTE function converts those entries with no-spaced entries. The RIGHT function results in the characters after what the LEN and SEARCH functions pass to it.

Formula insertion to split data into multiple columns

Step 6: Press ENTER and drag the Fill Handle to apply the formula in the entire Company Name column.

Final result

You can modify any of the formulas to fit them with your dataset. As we already know simpler ways to split data into multiple columns, it’s futile to use lengthy formulas to do the jobs.


Conclusion

In this article, we use features such as Text to Columns, Flash Fill, VBA, Power Query, and functions that split data in Excel into multiple columns. Text to Columns, VBA Macro, and Power Query split data into multiple columns with ease. However, functions and Flash Fill efficiently split less crowded data. Hope you find these above-described methods handy in your case. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo