How to Split Column in Excel

With Excel’s “Text to Feature” there are two simple ways of splitting your columns. If there is an evident delimiter such as a comma, use the “Delimited” option. However, the “Fixed method is ideal for splitting the columns manually.”

To learn how to split a column in excel and make your worksheet easy-to-read, follow these simple steps. You can also check our Microsoft Office Excel Cheat Sheet here. But, first, why should you split columns in excel? 

Jump to:

• Why you need to split cells
• How do you split a column in excel?
• Method 1- Delimited Option
• Method 2- Fixed Width
• How to Split One Column into Multiple Columns in Excel
• Method 3- Split Columns by Flash Fill
• Method 4- Use LEFT, MID and RIGHT text string functions 

Why you need to split cells

If you downloaded a file that Excel can’t divide, then you need to separate your columns. You should split a column in excel if you want to divide it with a specific character. Some examples of familiar characters are commas, colons, and semicolons.

How do you split a column in excel?

Method 1- Delimited Option

This option works best if your data contains characters such as commas or tabs. Excel only splits data when it senses specific characters such as commas, tabs, or spaces. If you have a Name column, you can separate it into First and Last name columns

1. First, open the spreadsheet that you want to split a column in excel
2. Next, highlight the cells to be divided. Hold the SHIFT key and click the last cell on the range
3. Alternatively, right-click and drag your mouse to highlight the cells
4. Now, click the Data tab on your spreadsheet.
5. Navigate to and click on the “Text to Columns” From the Convert Text to Columns Wizard dialog box, select Delimited and click “Next.”
6. Choose your preferred delimiter from the options given and click “Next.”
7. Now, highlight General as your Column Data Format
8. “General” format converts all your numeric values to numbers. Date values are converted to dates, and the rest of the data is converted to text. “Text” format only converts the data into text. “Date” allows you to select your desired date format. You can skip the column by choosing “Do not import column.” 
9. Afterward, type the “Destination” field for your new column. Otherwise, Excel will replace the initial data with the divided data
10. Click “Finish” to split your cells into two separate columns.

Method 2- Fixed Width 

This option is ideal if spaces separate your column data fields. So, Excel splits your data based on the character counts, be it 5th or 10th characters.  

1. Open your spreadsheet and select the column you wish to split. Otherwise, the “Text Columns” will be inactive. 
2. Next, click Text Columns in the “Data” Tab
3. Click Fixed Width and then Next
4. Now you can adjust your column breaks in the “Data preview.” Unlike the “Delimited” option that focuses on characters, in “Fixed Width,” you select the position of splitting the text.
5. Tips: Click the preferred position to CREATE a line break. Double-Click on the line to delete it. Click and drag a column break to move it
6. Click Next if you’re satisfied with the results
7. Select your preferred Column Data Format 
8. Next, type the “Destination” field for your new column. Otherwise, Excel will replace the initial data with the divided data.
9. Finally, click Finish to confirm the changes and divide your column into two

How to Split One Column into Multiple Columns in Excel

A single column can be split into multiple columns using the same steps outlined in this guide

Tip: The number of columns depends on the number of delimiters that you select. For instance, Data will be split into three columns if it contains three commas.

Method 3- Split Columns by Flash Fill

If you are using Excel 2013 and 2016, you are in luck. These versions are integrated with a Flash fill feature that extracts data automatically once it recognizes a pattern. You can use flash fill to split the first and last name in your column. Alternatively, this feature combines separate columns into one column.

If you don’t have these professional versions, quickly upgrade your Excel version. Follow these steps to divide your columns using Flash Fill. 

• Assume your data resembles the one in the image below

• Next, in cell B2, type the First Name as below

• Navigate to the Data Tab and click Flash Fill. Alternatively, use the shortcut CTRL+E
• Selecting this option automatically separates all the First Names from the given column

Tip: Before clicking Flash Fill, ensure that cell B2 is selected. Otherwise, a warning appears that

• Flash Fill cannot recognize a pattern to extract the values. Your extracted values will look like this: 

• Apply the same steps for the last name as well.

Now, you have learned to split a column in excel into multiple cells 

Method 4- Use LEFT, MID and RIGHT text string functions 

Alternatively, use LEFT, MID, and RIGHT string functions to split your columns in Excel 2010, 2013 and 2016. 

• LEFT function: Returns the first character or characters on your left, depending on the specific number of characters you require.
• MID Function: Returns the middle number of characters from string text beginning from where you specify.
• RIGHT Function: Gives the last character or characters from your text field, depending on the specified number of characters on your right.

However, this option is not valid if your data contains a formula such as VLOOKUP. In this example, you’ll learn how to split Address, City, and zip code columns.

To extract the Addresses using the LEFT function:

1. First select cell B2
2. Next, apply the formula =LEFT(A2,4)

Tip: 4 represents the number of characters representing the address

    3. Click, hold and drag down the cell to copy the formula to the entire column

To extract the City data, use the MID function as follows:

• First, Select cell C2
• Now, apply the formula =MID(A2,5,2)

Tip: 5 represents the fifth character. 2 is the number of characters representing the city.

• Right-click and drag down the cell to copy the formula in the rest of the column

Finally, to extract the last characters in your data, use the Right text function as follows:

• Select Cell D2
• Next, apply the formula =RIGHT(A2,5)

Tip: 5 represents the number of characters representing the Zip Code

• Click, hold and drag down the cell to copy the formula in the entire column

Tips to remember

• The shortcut key for Flash Fill is CTRL+E
• Always try to identify a shared value in your column before splitting it
• Familiar characters when splitting columns include commas, tabs, semicolons, and spaces. 

Text Columns is the best feature to split a column in excel. It might take you several attempts to master the process. But once you get the hang of it, it will only take you a couple of seconds to split your columns. The results are professional, clean, and eye-catching columns. 

 If you’re looking for a software company you can trust for its integrity and honest business practices, look no further than SoftwareKeep. We are a Microsoft Certified Partner and a BBB Accredited Business that cares about bringing our customers a reliable, satisfying experience on the software products they need.   We will be with you before, during, and after all the sales.