How to Split Data in Excel

Splitting data from one Excel cell into multiple cells is one of the most common data cleaning tasks you'll face. Whether you have a list of full names you need to separate into first and last name columns or a column of addresses you need to break apart, doing it manually is a recipe for frustration. This tutorial will walk you through four different ways to split data in Excel, from a simple click-of-a-button method to more powerful and flexible formulas.

Easiest Method: Split Columns with Flash Fill

Flash Fill is one of the most impressive features added to Excel in recent years (available in Excel 2013 and newer). It automatically detects patterns in your data and fills in the rest of the column for you, without requiring any formulas. It's perfect for simple, repetitive splitting tasks.

Let's say you have a single column with full names, and you want to create separate columns for the first and last names.

Step-by-Step Guide to Using Flash Fill:

1. Set Up Your Columns: Make sure you have empty columns next to your source data. For our example, if "Full Name" is in column A, create headers for "First Name" in column B and "Last Name" in column C.

2. Provide an Example: In the first cell of your "First Name" column (B2), manually type the first name from the adjacent cell. In our example, you would type "Sarah".

3. Trigger Flash Fill:

   • Move to the cell below (B3). Start typing the next first name, "John". As you type, Excel will likely show a greyed-out preview of the rest of the column filled with the correct first names. Simply press Enter to accept the suggestion.

   • Alternatively, after typing the first example, you can select the entire column (including your example) and press the shortcut Ctrl + E. Or, go to the Data tab and click the Flash Fill button.

4. Repeat for the Next Column: Do the same thing for the "Last Name" column. In C2, type "Connor". Then, move to C3 and press Ctrl + E or start typing "Wick". Excel will fill in the rest of the last names for you.

Pros and Cons of Flash Fill

Pros:

• Extremely fast and easy for straightforward splits.

• No need to write or understand complex formulas.

• Can handle various patterns, not just simple spaces or commas.

Cons:

• Not dynamic: If you change the original full name, the split columns will not update automatically. You'll have to re-run Flash Fill.

• Requires a clear pattern: If your data is inconsistent (e.g., some names have middle initials and others don't), Flash Fill might get confused and produce incorrect results.

Classic Method: Split Using Text to Columns

The "Text to Columns" feature has been a staple in Excel for a long time. It’s a bit more hands-on than Flash Fill but offers more control, especially when your data is separated by a consistent character like a comma, space, or tab. This is called a delimiter.

Let's use an example where you have addresses in one column, like "123 Main St, Anytown, CA 91234". We want to split this into "Street," "City," "State," and "ZIP."

Step-by-Step Guide to Using Text to Columns:

1. Select Your Data: Highlight the entire column of data that you want to split.

2. Open the Wizard: Go to the Data tab on the Excel ribbon and click on Text to Columns. This will open the "Convert Text to Columns Wizard."

3. Choose Your Data Type: You'll see two options: Delimited and Fixed width.

   • Delimited: Choose this when your data is separated by a specific character, like a comma or space. For our address example, the data is separated by commas, so we'll select this.

   • Fixed width: Choose this when your data is aligned in columns with spaces between each field, or when each field has a specific character length. This is less common for manually entered data.

   After selecting Delimited, click Next.

4. Set Your Delimiters: In this step, you tell Excel what character is separating your data. You can choose from common delimiters like Tab, Semicolon, Comma, or Space.

   • For our address example, uncheck any other boxes and check the Comma box.

   • In the Data preview window at the bottom, you'll see a preview of how Excel will split the data into columns. This is a great way to confirm you've chosen the right delimiter before moving on.

   Click Next.

5. Fine-tune and Finish: This final step lets you format your new columns and choose where to put them.

   • Column data format: You can select each column in the preview and assign it a specific format (General, Text, Date). "General" works for most cases.

   • Destination: This is the most important setting. By default, Excel will place the new columns starting in the same cell as your original data, effectively overwriting it. To avoid this, click the icon next to the "Destination" field and select the top cell of the empty column where you want your new data to start (e.g., $B$2).

Click Finish, and Excel will split the data into separate columns for you.

Flexible Method: Splitting Data with Formulas

Formulas are the most powerful and flexible way to split data in Excel. The major advantage is that they are dynamic — if the source data changes, your formula results will update automatically. This method uses a combination of text functions like LEFT, RIGHT, MID, FIND, and LEN.

Figuring out these formulas can feel a bit like solving a logic puzzle, but once you understand how they work, you have complete control over your data.

We'll use our "Full Name" example again, with the list in column A starting at A2.

Getting the First Name Using LEFT and FIND

To get the first name, we need to tell Excel to grab all the characters from the left of the string, stopping just before the first space.

• The FIND(" ", A2) part finds the position of the first space character in cell A2.

• The LEFT(A2, ...) part then extracts a certain number of characters from the left of cell A2.

Put it all together, and here is your formula for the first name:

=LEFT(A2, FIND(" ", A2) - 1)

Let's break that down for "Sarah Connor" in cell A2:

1. FIND(" ", A2) finds the space and returns its position, which is 6.

2. We subtract 1 because we don't want the space itself, so we get 5.

3. LEFT(A2, 5) tells Excel to take the first 5 characters from cell A2, which returns "Sarah".

Getting the Last Name Using RIGHT, LEN, and FIND

Getting the last name is a bit trickier because it varies in length. We need to tell Excel to grab characters from the right of the string.

• The LEN(A2) function calculates the total number of characters in the cell.

• FIND(" ", A2) again finds the position of the space.

• By subtracting the position of the space from the total length, we can figure out exactly how many characters make up the last name.

Here is the formula for the last name:

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

Breaking it down for "Sarah Connor" (total length of 12 characters):

1. LEN(A2) returns 12.

2. FIND(" ", A2) returns 6.

3. The formula becomes =RIGHT(A2, 12 - 6), which is =RIGHT(A2, 6).

4. This tells Excel to grab the last 6 characters from cell A2, which returns "Connor".

Advanced Method: Splitting Data with Power Query

If you're dealing with large datasets or need to perform the same data-splitting task repeatedly, Power Query is your best choice. Power Query (called "Get & Transform Data" in recent Excel versions) is an engine that lets you create a repeatable, non-destructive workflow for importing, cleaning, and shaping your data.

When should you use Power Query?

• When you need to perform the same data cleaning tasks every week or month.

• When you're working with very large datasets that would slow down normal Excel.

• When you want a workflow that doesn't mess with your original source data.

Step-by-Step Guide to Using Power Query:

1. Load Your Data into Power Query: First, format your data as an Excel Table (select your data and press Ctrl + T). Then, with a cell in the table selected, go to the Data tab and click From Table/Range. This will open the Power Query Editor.

2. Select the Column to Split: Inside the Power Query Editor, you'll see a preview of your table. Click the header of the column you want to split.

3. Use the "Split Column" Tool: Go to the Home tab or the Transform tab in the editor's ribbon and click Split Column. A dropdown menu will appear with several options, most commonly By Delimiter.

4. Configure the Split: In the "Split Column by Delimiter" window, Power Query will often automatically detect the delimiter (like a space or comma). You can confirm or change it. You can also choose whether to split at the left-most delimiter, right-most delimiter, or each occurrence. For splitting full names, "left-most delimiter" is usually best. Click OK.

5. Close & Load: Power Query will instantly create new columns with your split data. In the top-left corner, click Close & Load. Excel will create a new worksheet containing your transformed data in a clean table.

The best part? If you add new data to your original source table, you can just go to the Power Query output table, right-click, and select Refresh. The entire workflow will run again automatically, including your new data.

Final Thoughts

Knowing how to split data is a fundamental Excel skill that saves a massive amount of time. From the simple click-and-done of Flash Fill to the robust, automated workflows of Power Query, there's always a technique that fits your needs. Choosing the right method depends entirely on your specific data and whether you need a one-off fix or a repeatable, dynamic solution.

For many teams, wrestling with spreadsheets is just the first step in a long, manual reporting process. Once the data is cleaned, you still have to build charts, create dashboards, and share a report that's likely out of date by the time you're done. We built Graphed to short-circuit that entire cycle. Instead of manually exporting files and wrangling CSVs in Excel, you can connect your data sources directly to us and use natural language to create real-time dashboards and reports in seconds, not hours.