How to Split Column in Excel

Does anyone know any shortcuts for splitting columns in Microsoft Excel? You might need to divide grouped data into several columns while using Excel. For example, you may need to have distinct columns for the first and last names.

There are two easy ways to divide your columns using Excel’s “Text to Feature.” Use the “Delimited” option if there is a clear delimiter, like a comma. But “the Fixed approach works best when separating the columns by hand.”

Learn how to split a column in Excel in a few easy steps. This will make your spreadsheet easier to read. If you need additional Excel tips, we’ve got you covered with our Microsoft Office Excel Cheat Sheet. But before that, let’s understand why you would want to split columns in Excel. You may make your spreadsheet easier to read by learning how to split a column in Excel with these simple steps. Here is our Microsoft Office Excel Cheat Sheet for you to review as well. Let me start by asking why you would wish to separate columns in Excel.

Why splitting cells is necessary

You will need to divide your columns if the file you downloaded is too large for Excel to divide. If you wish to divide a column with a specific character, you should split it in Excel. A few instances of well-known characters are semicolons, colons, and commas.

In Excel, how do you divide a column?

Method 1: Limited Choice

If your data contains characters like tabs or commas, this option will function best. Excel only divides data when it detects certain characters, like tabs, spaces, or commas. You can divide a Name column into First and Last name columns if you have one.

1. To split a column in Excel, first open the spreadsheet that you wish to split.
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. Select General as your Column Data Format at this point.
8. When importing data, it’s important to know how all of your numerical values will be converted to numbers using the “General” format. The remaining data will be converted to text, and date values will be converted back to dates. If you want to convert your data to text, make sure to select the “Text” format. You can also customize the date format you prefer by selecting “Date.”Additionally, if you want to exclude a column, simply choose “Do not import column.”
9. Afterward, type the “Destination” field for your new column. If not, Excel will substitute the divided data for the original 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 Divide an Excel Column Into Many Columns

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 names 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, given a number, the leftmost character or characters.
• 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. To duplicate the formula throughout the full column, click, hold, and drag the cell.

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.

Go no further than Fastsoftwares if you are searching for a software provider you can rely on for its honesty and morality. Being a BBB Accredited Business and a Microsoft Certified Partner, we are committed to providing our clients with a dependable and fulfilling experience when it comes to the software products they require. We will be there for you before, during, and following every sale.