Spreadsheet Practice

How to split text by a multi-character delimiter in Google Sheets

Google Sheets formula

=SPLIT(A2," - ",FALSE)

Set SPLIT's third argument to FALSE so the whole delimiter is used: =SPLIT(A2," - ",FALSE). By default SPLIT splits at each character.

Result on the example
one result per row in B2, filled down to B6
Excel
See below

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

SPLIT's third argument, split_by_each, is TRUE by default: every character of the delimiter is its own delimiter, so " - " splits at spaces and dashes alike and "Sioux Falls" falls apart. With FALSE, only the exact text " - " splits the cell.

=SPLIT(A2," - ",FALSE)

Result{1 row × 2 cols} "Minneapolis", "Saint Paul"

  1. A2"Minneapolis - Saint Paul"

    The value in A2.

  2. SPLIT(A2," - ",FALSE){1 row × 2 cols} "Minneapolis", "Saint Paul"

    SPLIT: Splits text across columns at a delimiter.

    • textA2"Minneapolis - Saint Paul"
    • delimiter" - "
    • split_by_eachFALSE

In Excel

Excel 365's TEXTSPLIT always treats the delimiter as one piece of text: =TEXTSPLIT(A2," - ").

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SPLIT(A2," - ")Minneapolis, Duluth, Madison…Different resultThe default, to see the problem. Splits at every space too, so "Sioux Falls" becomes "Sioux" and "Falls".

Common mistakes

  • SPLIT also removes empty pieces by default (the fourth argument). Set it to FALSE to keep blank fields in delimited data.
  • SPLIT turns number-like pieces into numbers, which drops leading zeros. Use REGEXEXTRACT when you need text.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Split text by a multi-character delimiter

In B2, split the route at " - " so the start city lands in B and the end city in C. Fill down to B6.

Questions

How do I split by any of several characters on purpose?

Leave split_by_each on: =SPLIT(A2,",;") splits at commas and semicolons.

How do I split into rows?

Wrap it in TRANSPOSE: =TRANSPOSE(SPLIT(A2," - ",FALSE)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.