By breaking up a long column into multiple cells, Excel documents become easier to read and are less likely to contain poorly structured data. The simplest example of splitting Excel cells is when you have a full name and split it into the first and last names. Now that you’ve understood what splitting into multiple columns actually implies, multiple ways are using which you can ungroup a single column into two. This post will guide you to split data into multiple columns in Microsoft Excel.
How to Split Data Into Multiple Columns in Excel
There are various ways using which one can go ahead and split the columns into two. We’ve compiled a list of the fastest and the easiest ways you can go around with.
- Utilizing the Text to Column Feature
- Using Flash Fill
There can be more ways to achieve this, but these work the best.
1] Utilizing the Text to Column Feature
Microsoft Excel offers its users an option named Text to Columns which can get the task accomplished in a matter of a few clicks. Here’s how you can go around with the Text to Column feature in Excel:
- Start by dragging and selecting the entire range of cells you would like to split.
- Once selected, navigate to the Data tab from the Excel ribbon.
- Now, find and select the Text to Columns button present under Data Tools.
- You’ll find a new wizard popping up on your screen, with two options Delimited and Fixed Width. You can try out both options and check how that works via the Preview pane right below. You can use any delimiter which fits your data.
- Delimited: This option works well when the text is separated using commas, as seen in a CSV file.
- Fixed Width: The option that might be suitable for most users works great when a space in a single column separates the text.
- Click on Next, select the cell where you would like the data to be extracted, and tap on Finish.
You’ll now find the columns split into two, compared to one single column at the beginning.
2] Using Flash Fill
A much simpler and faster way than using Text to Columns, Flash Fill is a smarter way to copy and extract text from a column to another. Flash Fill automatically fills your data when it senses a pattern. Here’s how to split data using Flash Fill in Microsoft Excel.
- To demonstrate this, we will be using the example of the original column containing the Full Name and two new columns with First and Last Name.
- To begin filling the First name with Flash Fill, start by entering the person’s first name in the first row alone.
- Next, select the next appropriate cell in the column and press CTRL + E on your keyboard. Alternatively, you can also head over to the Flash Fill option by heading over to the Editing section under Home Tab and choosing Flash Fill from the dropdown stating Fill.
- Perform the same process for all the other columns, and you’ll be good to go.
Microsoft Excel allows you to split data into multiple columns. It is useful when you have data that needs to be organized into separate columns, such as a year, month, and day. I hope this tutorial was able to help you with the task of splitting data into multiple columns in Microsoft Excel. In most cases, you will find that columns will be the best way to ahead.
