How To Split Text Into Columns In Google Sheets

Splitting text into columns in Google Sheets is one of the most practical spreadsheet cleanup tasks because real business data often arrives in combined text fields instead of clean structured columns. A customer name may be joined with an ID, a city may be attached to a country, or a product code may be merged with a product description in one cell.

That creates a problem for reporting.

When information stays combined in one field, it becomes harder to:

• sort correctly
• filter by one part of the value
• use lookups
• count categories
• build dashboards
• clean imported data
• create structured reporting tables

That is why splitting text into columns matters so much.

This guide explains how to split text into columns in Google Sheets using both the built-in tool and the SPLIT function, how delimiters work, how to handle messy real-world data, and how to decide when each method makes the most sense.

Overview

Splitting text into columns means taking a combined text string and separating it into multiple cells based on a delimiter.

A delimiter is the character or separator that divides one part of the text from another.

Examples of delimiters include:

• comma ,
• space
• dash -
• slash /
• pipe |
• semicolon ;
• tab

For example, if a cell contains:

P100 - Laptop

you may want:

• P100 in one column
• Laptop in another column

That is a split-text task.

Google Sheets supports this in two main ways:

• the built-in Split text to columns tool
• the SPLIT function

The best option depends on whether the split should be one-time or dynamic.

Why splitting text into columns matters so much

A lot of spreadsheet reporting depends on structured data.

That usually means:

• one row per record
• one column per field
• clean categories
• stable values for filtering and formulas

But imported or copied data often breaks that structure.

Examples:

• Jane Smith
• Cape Town, South Africa
• North | Approved
• P100-Laptop
• Finance: Closed
• INV-2026-1001

When those values stay combined, analysis becomes harder.

Splitting the text into columns makes it easier to:

• filter by country
• match product codes
• count departments
• sort by city
• report on statuses
• clean imported records

That is why this skill is so useful in spreadsheet analytics.

The built-in Split text to columns tool

Google Sheets includes a built-in feature called Split text to columns.

This is a quick one-time tool that separates selected text based on a delimiter.

It is useful when:

• the cleanup is simple
• the source data is already in the sheet
• you want fast manual separation
• the split does not need to stay dynamic

For example, if a column contains: Cape Town, South Africa

the tool can split that into:

• Cape Town
• South Africa

based on the comma delimiter.

This is one of the easiest ways to perform a quick cleanup task.

How Split text to columns works

The built-in tool works by:

• selecting the column or cells
• opening the split feature
• choosing the delimiter
• letting Google Sheets separate the content across columns

This modifies the sheet directly.

That means it is useful for:

• one-time cleanup
• imported files
• copied exports
• ad hoc separation work

But it is not always the best option if the source data changes often.

When the built-in tool is the right choice

Use Split text to columns when:

• the task is one-time
• the dataset is static
• you want quick manual cleanup
• the source cells are already in their final place
• the output does not need to keep updating

This is often the fastest method for basic data cleanup.

The SPLIT function

The SPLIT function is the formula-based way to split text into columns.

The basic syntax looks like this:

=SPLIT(text, delimiter)

This means:

• text is the original cell
• delimiter is the separator that tells Sheets where to split

A simple example:

=SPLIT(A2,", ")

If A2 contains:

Cape Town, South Africa

the result becomes:

• Cape Town
• South Africa

in separate columns.

This is the core idea of the SPLIT function.

Why the SPLIT function is so useful

The main benefit of SPLIT is that it is dynamic.

If the source cell changes, the split result updates automatically.

That makes SPLIT especially useful when:

• imported data updates often
• the sheet is shared
• formulas should remain live
• the cleanup logic should stay visible
• the output is part of a reporting workflow

This is one of the biggest differences between SPLIT and the built-in tool.

• Split text to columns is manual and one-time
• SPLIT is formula-based and dynamic

A simple SPLIT example

Suppose A2 contains:

P100 - Laptop

Formula:

=SPLIT(A2," - ")

Result:

• P100
• Laptop

This is one of the most common real-world examples.

It is useful for:

• code and description fields
• status labels
• combined categories
• imported identifier fields

Delimiters in Google Sheets

A delimiter is the separator that tells Google Sheets where to split the text.

Common delimiters include:

• comma and space: ", "
• space: " "
• dash: "-"
• spaced dash: " - "
• pipe: " | "
• slash: "/"
• colon: ": "

The key is that the delimiter in your formula or tool must match the real structure of the source data.

For example:

• "-" and " - " are not the same
• "|" and " | " are not the same

That detail matters a lot.

Why spaces matter

One of the most common splitting mistakes is ignoring spaces around delimiters.

Suppose a cell contains:

Finance - Closed

If you split using only "-" instead of " - ", the result may still work, but you may end up with extra spaces in the output.

That can cause later issues in:

• filtering
• lookups
• comparisons
• reporting labels

This is why exact delimiter choice matters.

A location example

Suppose A2 contains:

Cape Town, South Africa

Formula:

=SPLIT(A2,", ")

Result:

• Cape Town
• South Africa

This is useful for:

• customer location cleanup
• CRM imports
• region/country separation
• reporting dimensions

A code-and-name example

Suppose A2 contains:

P100 - Laptop

Formula:

=SPLIT(A2," - ")

Result:

• P100
• Laptop

This is useful for:

• product lookup prep
• label cleanup
• operational item lists
• inventory analysis

A pipe-separated example

Suppose A2 contains:

North | Approved

Formula:

=SPLIT(A2," | ")

Result:

• North
• Approved

This is useful in:

• workflow statuses
• report labels
• operations summaries
• category cleanup

One-time splitting versus dynamic splitting

This is one of the most important decisions.

Use Split text to columns when:

• you want quick manual cleanup
• the source data is static
• you do not need the logic to remain in formula form
• the separation is a one-time task

Use SPLIT when:

• the source data may change
• the output should stay live
• the logic should remain visible
• the workflow is repeatable
• the spreadsheet supports ongoing reporting

This is the practical difference between the two methods.

Splitting full columns with formulas

A common next step is wanting to split more than one row automatically.

If you write:

=SPLIT(A2," - ")

you only split one cell.

If the source column keeps growing, a dynamic approach may be more useful.

In those cases, array-based workflows or structured formula layouts may be helpful, depending on the complexity of the sheet.

For many practical reporting workflows, it is often enough to place the SPLIT formula in a helper area and let it expand row by row where needed.

Why messy source data makes splitting harder

The biggest splitting problems usually come from the source data, not the split tool itself.

Common issues include:

• different rows use different delimiters
• some rows have spaces, others do not
• some rows have no delimiter at all
• some rows contain multiple delimiters
• copied text contains hidden spaces
• imported records are inconsistent

This is why splitting is often part of broader spreadsheet cleanup, not just one isolated action.

What happens if the delimiter is inconsistent

Suppose some rows contain:

• P100 - Laptop and others contain:
• P200-Laptop

A split based on " - " will work for the first pattern but not the second.

This is why it is important to inspect the real source values before choosing the split method.

If the source is inconsistent, you may need:

• cleanup first
• a different delimiter strategy
• formula wrapping
• or a more structured transformation process

Common business use cases

Finance

Finance teams split text into columns for:

• code and account name separation
• cost center parsing
• department and status splitting
• imported ledger label cleanup

Operations

Operations teams split text into columns for:

• queue and priority fields
• site and issue labels
• owner and status combinations
• tracking categories from imported logs

Analytics

Analysts split text into columns for:

• product code cleanup
• campaign label parsing
• region-country separation
• imported export preparation
• better lookup and reporting logic

These are everyday spreadsheet tasks.

Common mistakes when splitting text

Using the wrong delimiter

This is the most common mistake.

If the delimiter in the formula does not match the real source text, the split will fail or behave inconsistently.

Ignoring spaces

A spaced delimiter and a non-spaced delimiter are different.

This matters a lot in messy data.

Splitting without protecting the destination area

The built-in split tool can overwrite adjacent cells if the output columns are not empty.

Always check that there is room for the split result.

Assuming every row follows the same pattern

Real imported data often includes exceptions. That can break a formula-based approach unless the workflow is designed carefully.

Splitting when extraction might be better

Sometimes you do not need full splitting. You only need:

• the part before a delimiter
• or the part after it

In those cases, extraction logic may be more appropriate than full splitting.

Step-by-step workflow

If you want to split text into columns well in Google Sheets, this is a strong process.

Step 1: Inspect the source values

Ask: What delimiter is actually being used? Does it include spaces? Is it consistent across rows?

Step 2: Decide whether the split should be one-time or dynamic

If one-time, use Split text to columns. If dynamic, use SPLIT.

Step 3: Choose the exact delimiter

Examples:

• ", "
• " - "
• " | "
• "/"

Step 4: Make room for the output

Whether using the built-in tool or formula output, make sure adjacent columns are available.

Step 5: Review the result for messy cases

Check:

• rows without delimiters
• rows with multiple delimiters
• rows with extra spaces
• rows with inconsistent formats

Practical formula examples

Split a comma-separated value

=SPLIT(A2,", ")

Split a dash-separated value

=SPLIT(A2," - ")

Split a pipe-separated value

=SPLIT(A2," | ")

Split a slash-separated value

=SPLIT(A2,"/")

These patterns cover many practical business spreadsheet cleanup tasks.

When splitting text is the better choice

Splitting text is usually the better choice when:

• combined fields need to become structured columns
• filtering and sorting will depend on the separated values
• lookups require cleaner source fields
• imported data needs cleanup
• reporting logic works better with one field per column

This is one of the most important spreadsheet design improvements you can make.

When another approach may be better

Sometimes splitting is not the best answer.

Another method may be better when:

• you only need one part of the string
• the source is too inconsistent for a simple delimiter split
• the output should remain combined for presentation purposes
• a larger transformation workflow is needed

The best method depends on the reporting goal.

FAQ

How do I split text into columns in Google Sheets?

You can split text into columns in Google Sheets either by using the built-in Split text to columns tool for one-time cleanup or by using the SPLIT function for a dynamic formula-based result.

What delimiters can Google Sheets use to split text?

Google Sheets can split text by common delimiters such as commas, spaces, dashes, slashes, pipes, semicolons, tabs, and custom characters or strings.

When should I use SPLIT instead of Split text to columns?

Use SPLIT when you want the output to stay dynamic and update automatically when the source text changes. Use Split text to columns when you just need a quick one-time separation.

Why is my split result not working correctly?

Split results usually fail because the delimiter is wrong, the source data is inconsistent, extra spaces are present, or different rows use different separator patterns.

Final thoughts

Splitting text into columns in Google Sheets is one of the most practical cleanup skills in spreadsheet work because so much imported and copied data arrives in combined fields.

The goal is not just to separate text for the sake of it. The goal is to make the data more usable.

Once combined values are split into structured columns, they become easier to filter, sort, match, summarize, and turn into reporting logic. That is why this is such a high-value spreadsheet skill.

The key is understanding the delimiter, choosing the right method, and checking the source data carefully. Once that clicks, splitting text into columns becomes a simple but powerful way to improve spreadsheet quality in Google Sheets.