Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Separate Excel Data into Columns

Cody Schneider••7 min read

Wrangling messy data is a rite of passage for anyone who works with spreadsheets. One of the most common headaches is finding a single column packed with combined information, like a full name, an address, or product codes. This article will show you several straightforward methods to split that tangled data into separate, clean columns in Microsoft Excel, making your information much easier to sort, filter, and analyze.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Using the "Text to Columns" Wizard

The Text to Columns wizard is Excel's classic, built-in tool for this exact job. It’s powerful and reliable, offering two main approaches: splitting by a specific character (like a space or comma) or by a fixed width.

Method 1: Splitting Data with Delimiters (Commas, Spaces, etc.)

This is the most common scenario. You have data separated by a consistent character, known as a delimiter. This could be a comma, a space, a hyphen, a semicolon, or any other symbol. For this example, let's say you have a single column (A) with full names that you want to split into "First Name" and "Last Name" columns.

Example Data (Column A):

  • Jane Doe
  • John Smith
  • Maria Garcia

Here’s the step-by-step process:

  1. Select Your Data: Click the column header (e.g., 'A') to highlight all the data you want to separate.
  2. Open the Wizard: Navigate to the Data tab on the Ribbon and click on Text to Columns.
  3. Choose "Delimited": The wizard's first step will open. Ensure that Delimited is selected, as your data is separated by a character (a space). Click Next.
  4. Select Your Delimiter: In step two, you'll tell Excel what character separates your data. For our full name example, uncheck "Tab" and check Space. You'll see a preview at the bottom showing how Excel will split the names. If your delimiter was a comma, you'd select "Comma." If it's something else, like a hyphen, you can select "Other" and type the hyphen into the box. Once you're happy with the preview, click Next.
  5. Set the Destination and Format: This final step is crucial.
  6. Finish: Click Finish. Your data will now be neatly split into two columns.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Method 2: Splitting Data by Fixed Width

Sometimes, your data isn't separated by a delimiter but is instead structured in aligned columns, like in data exports from older systems. For instance, you might have an ID and a department code smooshed together in one column.

Example Data (Column A):

  • EMP001SALES
  • EMP002MARKETING
  • FIN003ACCOUNTS

In this case, an employee or department code is always a certain number of characters long.

  1. Select Your Data and Open the Wizard: Just as before, select your column and go to Data > Text to Columns.
  2. Choose "Fixed Width": This time, select Fixed width and click Next.
  3. Set Your Breaks: The wizard will show you a preview of your data with a ruler. To create a break line, simply click at the position where you want to split the data. For EMP001SALES, you would click right after the '1' to separate the ID from the department. You can create multiple breaks, drag lines to move them, or double-click a line to remove it. Once your break lines are set, click Next.
  4. Set the Destination: Just like with the delimited method, choose a destination cell (like $B$1) to avoid overwriting your original data, and then click Finish.

Leveraging Flash Fill for Instant Results

If you're using Excel 2013 or newer, Flash Fill is an almost magical feature that can save you a ton of time. It recognizes patterns without you needing to open any wizards or write formulas. It's perfect for simple splits.

Using our full name example from before ("Jane Doe" in A1), here’s how Flash Fill works:

  1. Prepare Your Columns: Make sure there are empty columns next to your source data. In column B, you might have a header for "First Name," and in column C, one for "Last Name."
  2. Provide an Example: In the first empty cell (B1), type the first piece of data you want to extract. In this case, type Jane and press Enter.
  3. Start the Second Example: In the cell below (B2), start typing the next piece of data: John.
  4. Let Flash Fill Work: As you type, Excel will recognize the pattern and show a ghost-like preview of all the other first names filled in down the column. Simply press Enter to accept the suggestion.
  5. Repeat for Other Columns: Do the same thing for the last names. Go to C1, type Doe, press Enter, and start typing Smith in C2. Press Enter to accept the Flash Fill suggestion.

If the preview doesn't appear automatically, you can trigger Flash Fill manually by going to the Data tab and clicking the Flash Fill button or by using the keyboard shortcut Ctrl + E. Flash Fill is incredibly smart but works best with clean, consistent data. If yours is messy, you might get unpredictable results, so always give the output a quick review.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Advanced Separation: Using Excel Formulas

For those who need a dynamic solution - meaning the split data will automatically update if the original data changes - formulas are the way to go. They look more intimidating, but they offer the most flexibility.

Let's again assume "Jane Doe" is in cell A1.

Extracting Text from the Left (First Name)

We'll combine the LEFT and FIND functions to pull out the first name.

  • FIND(" ", A1) finds the position of the space character in cell A1. For "Jane Doe", this is 5.
  • LEFT(text, num_chars) extracts a certain number of characters from the start (left side) of a text string.

The formula to get the first name is:

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

This formula tells Excel: "Find the space in A1, then give me all the characters to the left of it, minus the space itself." The result is "Jane."

Extracting Text from the Right (Last Name)

Extracting the last name is a bit trickier. We need to find the total length of the text (LEN), find the position of the space (FIND), and subtract to figure out how many characters make up the last name.

  • LEN(A1) gets the total number of characters in the cell. For "Jane Doe", this is 8.
  • RIGHT(text, num_chars) extracts a certain number of characters from the end (right side).

The formula to grab the last name is:

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

This tells Excel: "Calculate the total length of the text in A1 and subtract the position of the space. Then, give me that many characters from the right end of the cell." The result is "Doe."

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

The Modern Approach: TEXTSPLIT Function (Microsoft 365)

If you have a Microsoft 365 subscription, there's a much simpler formula: TEXTSPLIT.

Just type this formula into a single cell:

=TEXTSPLIT(A1, " ")

This one formula automatically splits the text in A1 using the space as a delimiter and spills the results into the adjacent columns. "Jane" will appear in the cell with the formula, and "Doe" will automatically pop into the cell next to it. It’s the easiest and most powerful formula-based method available today.

Final Thoughts

Whether you're a beginner or an analyst, Excel gives you multiple ways to tackle the job of separating data into columns. You can use the guided Text to Columns wizard for reliability, the clever and convenient Flash Fill for speed on simple tasks, or powerful formulas for a flexible and dynamic solution that updates automatically.

While mastering these spreadsheet skills is incredibly valuable, manually cleaning and reporting on all your business data becomes a bottleneck when you’re pulling it from different places like Google Analytics, Shopify, and your CRM. We built Graphed to eliminate that drudgery. Instead of exporting CSVs and wrestling with data in spreadsheets, you simply connect your tools to Graphed, describe the dashboard you want in plain English, and have a live, real-time report in seconds, not hours.

Related Articles