How to Split Data into Multiple Columns in Excel?

Excel is an extremely versatile tool used widely for management, analysis, and presentation of data. Sometimes, such big datasets need to be reorganized for better readability or functionality. One simple yet common task is taking data from a single column to spread across multiple columns. For example, you might want to split first and last names, split dates, organize addresses, and so forth. This article will take you through a number of methods to split data into multiple columns in Excel with precision and speed.

Using the Text to Columns Feature

The Text to Columns feature is one of the simplest methods for splitting data in Excel. It works well for data separated by delimiters like commas, tabs, or spaces.

Steps:

1. Select Your Data:
   Highlight the column containing the data you want to split.
2. Open the Text to Columns Wizard:
   Go to the Data tab and click on Text to Columns under the Data Tools group.
3. Choose Delimited Option:
   In the wizard, select Delimited if your data is separated by specific characters (e.g., commas, tabs). Click Next.
4. Specify the Delimiters:
   Select the delimiter (e.g., comma, tab, or space). Preview the split data in the window below to ensure it looks correct.
5. Select Destination:
   Choose where the split data will go. By default, Excel will overwrite the original column. If you want the data in a different location, specify the desired range.
6. Finish:
   Click Finish to complete the process. The data will be split into multiple columns based on your selected delimiter.

Splitting Data with Flash Fill

Flash Fill is an intelligent Excel feature that recognizes patterns and applies them automatically. It’s ideal for simple tasks like separating first and last names or extracting parts of text.

Steps:

1. Prepare the Columns:
   Insert empty columns next to the column you want to split.
2. Provide an Example:
   In the first row of the new column, type the desired value. For example, if splitting “John Doe,” type “John” in the first column.
3. Use Flash Fill:
   • Press Ctrl + E, or
   • Go to the Data tab and click Flash Fill.

   Excel will fill the rest of the column based on your example.

4. Repeat for Additional Columns:
   If your data has multiple parts to split, repeat the process for each new column.

Splitting Data with Formulas

Formulas are another dynamic way of splitting data, especially if you have complex datasets. Here are some common formulas you can use:

Splitting by Delimiter

Use formulas like LEFT, RIGHT, MID, combined with FIND or SEARCH, to extract parts of text.

• Example: Splitting “John, Doe” into two columns:
  • First Name:
    =LEFT(A1, FIND(",", A1) - 1)
  • Last Name:
    =RIGHT(A1, LEN(A1) - FIND(",", A1))

Splitting by Space

For data separated by spaces:

• Example: Splitting “John Doe”:
  • First Name:
    =LEFT(A1, FIND(" ", A1) - 1)
  • Last Name:
    =RIGHT(A1, LEN(A1) - FIND(" ", A1))

Using Power Query for Complex Splitting

Power Query is a professional Excel tool for data transformation. It’s very helpful when dealing with large data and complicated splitting logic.

Steps:

1. Load Data into Power Query:
   • Highlight your data.
   • Go to Data > Get & Transform Data > From Table/Range.
2. Split Columns by Delimiter:
   • In Power Query, select the column to split.
   • Click Split Column under the Home tab and choose By Delimiter.
   • Specify the delimiter (e.g., comma, space).
3. Customize and Load Data:
   Adjust settings as needed, then click Close & Load to return the transformed data to Excel.

Automating Splitting with VBA Macros

VBA macros enable the automation of repetitive tasks. Using a VBA script, one can split data into columns based on certain rules.
Example VBA Code :

Sub SplitDataIntoColumns ()
Dim rng As Range
Dim cell As Range
Dim data As Variant
Dim i As Integer


Set rng = Selection
For Each cell In rng
data = Split(cell.Value, ",")
For i = LBound(data) To UBound(data)
cell.Offset(0, i).Value = data(i)
Next i
Next cell
End

How to Use the Code:

1. Press Alt + F11 to open the VBA editor.
2. Insert a new module and paste the code above.
3. Close the VBA editor.
4. Press Alt + F8, select the macro, and click Run.

This macro splits data based on commas and places each part into adjacent columns.

Best Practices for Splitting Data in Excel

1. Backup Your Data: Always create a copy of your original data before applying any splitting techniques.
2. Choose the Right Method: Use Text to Columns for simple tasks, Flash Fill for pattern recognition, and Power Query or VBA for large or complex datasets.
3. Verify Your Results: Double-check your split data for accuracy, especially when working with large datasets or formulas.
4. Automate When Possible: Use VBA macros or Power Query to handle repetitive tasks efficiently.

Conclusion

This splits data into multiple columns in Excel, and the datasets look and feel so much more organized and usable. Whether you’re using intuitive Text to Columns tools, intelligent Flash Fill feature, dynamic formulas, or even more advanced Power Query and VBA methods, there’s always a solution for you within the Excel universe. Know the techniques to take on these data-splitting challenges you’ll encounter.