Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 93 ยท Text, Date and Time

Joining and Splitting Text

Join cells with &, CONCAT and TEXTJOIN, split them with TEXTSPLIT, TEXTBEFORE and TEXTAFTER, and choose between formulas, Text to Columns and Flash Fill.

Data rarely arrives in the shape you need. A mailing list has first and last names in separate columns, but the label needs one Lovelace, Ada cell. A log file stores 2025;Q1;Sales in one cell, but you want three columns. Joining builds one text value from several pieces; splitting cuts one value into several. Excel gives you three families of tools for this: operators and functions, wizards such as Text to Columns, and the pattern-learning Flash Fill.

This lesson covers &, CONCAT, TEXTJOIN, TEXTSPLIT, TEXTBEFORE and TEXTAFTER, then compares them with Text to Columns and Flash Fill. It builds on Text Functions: LEFT, RIGHT, MID, LEN, TRIM. Excel cannot run here, so formulas are shown read-only and JavaScript, pandas and a Flash Fill stand-in let you run the same logic.

Joining text

Ampersand Works everywhere, one pair at a time.
CONCAT Joins many cells, no separator.
TEXTJOIN Separator plus skip-blanks option.
1 / 2

Joining: the ampersand, CONCAT and TEXTJOIN

The ampersand & joins two values: =A2 & " " & B2 turns Ada and Lovelace into Ada Lovelace. Anything you want literally, such as a space or a comma, goes in double quotes. It works in every version of Excel.

CONCAT(text1, [text2], ...) joins many values, and unlike the old CONCATENATE it accepts ranges: =CONCAT(A2:C2) glues three cells together. It has no separator argument, so you cannot add commas without writing them into the list.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) is the one you will use most. The first argument is the separator; the second, TRUE or FALSE, says whether to skip empty cells.

TEXT
=TEXTJOIN(", ", TRUE, A2:A6)          Ada, Bo, Cy
=TEXTJOIN(" ", TRUE, B2, C2, D2)      first, middle, last name without double spaces
=TEXTJOIN(CHAR(10), TRUE, A2:A4)      lines in one cell (turn on Wrap Text)

A few details about &, CONCAT and TEXTJOIN:

  • Numbers are converted with the General format. 1/3 becomes 0.333333333333333, and a date becomes its serial number (45366), not 15-Mar-2024. Wrap the value in TEXT to control the look; see TEXT, VALUE and Number-as-Text Problems.
  • The result is always text, and a single cell can hold up to 32,767 characters.
  • TEXTJOIN and CONCAT need Excel 2019 or newer; the & operator always works.

Splitting by delimiter: TEXTSPLIT

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) cuts text wherever a delimiter appears and spills the pieces. With a column delimiter, you get pieces side by side; add a row delimiter and you get a grid.

TEXT
=TEXTSPLIT("2025;Q1;Sales", ";")             2025 | Q1 | Sales
=TEXTSPLIT("a,b;c,d,e", ",", ";")            a | b | #N/A
                                             c | d | e
=TEXTSPLIT("a,,b", ",", , TRUE)              a | b   (empty piece skipped)

Useful options:

  • ignore_empty: FALSE (default) keeps empty pieces, for example between two commas, and TRUE drops them.
  • match_mode: 1 ignores case when matching the delimiter.
  • pad_with: the filler for short rows, #N/A by default.
  • The delimiter may be an array constant such as {",",";"} to split on either character.

Because the result spills, other formulas can use the whole block. See Dynamic Arrays and Spilling.

Picking one part: TEXTBEFORE and TEXTAFTER

When you need just one piece, TEXTBEFORE and TEXTAFTER are simpler than LEFT with FIND. Their signature is TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]), and TEXTAFTER is the same.

  • instance_num says which delimiter to use. 1 is the first, 2 the second, and a negative number counts from the end, so -1 is the last.
  • When the delimiter is missing, the result is #N/A unless you give if_not_found.
TEXT
=TEXTBEFORE("[email protected]", "@")                 jane
=TEXTAFTER("[email protected]", "@")                  example.com
=TEXTAFTER("reports/2025/q1/sales.csv", "/", -1)     sales.csv
=TEXTBEFORE("reports/2025/q1/sales.csv", "/", -1)    reports/2025/q1
=TEXTBEFORE("sales.csv", ".")                        sales

Getting the last word, such as a last name that follows a variable number of middle names, used to need a famous and ugly formula with SUBSTITUTE and REPT. TEXTAFTER(A2, " ", -1) does it directly.

Run it: the functions as JavaScript

The stand-in below implements CONCAT, TEXTJOIN, TEXTSPLIT, TEXTBEFORE and TEXTAFTER according to the rules above. Grids are arrays of rows. It is a simplified model, not Excel's code: case-insensitive matching and match_end are left out.

JS TEXTJOIN, TEXTSPLIT, TEXTBEFORE and TEXTAFTER

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Wizards: Text to Columns and Flash Fill

Formulas are live: when the source changes, the result changes. Two tools in the Data tab give you static results instead.

Text to Columns splits the selected column into several columns. Choose Delimited (pick comma, tab, space, semicolon or another character) or Fixed width (click to set break lines), and finally set the data format of each new column. Set a column to Text if it holds codes with leading zeros, or Excel will turn 007 into 7. The result overwrites the cells to the right of your data, so make room first. It is a one-time action: the output does not update.

Flash Fill (Ctrl+E) learns from an example you type next to the data, such as Ada beside Ada Lovelace, and fills the rest. It also writes fixed values. It is excellent for one-off reformatting and risky for anything important: look through the result, especially rows that differ from your example.

Task Best tool
Data that will change and must stay linked Formula (TEXTSPLIT, TEXTBEFORE, TEXTJOIN)
One-time split of a column Text to Columns
Quick reformat from an example Flash Fill
Repeatable import with many cleaning steps Power Query

A Flash Fill stand-in

Flash Fill's real algorithm is Microsoft's own. The model below shows only the idea: from a few examples, find a simple rule that explains all of them, then apply it to the other rows.

JS Learning a pattern from examples

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice what happens with Grace Brewster Hopper and the initials rule: you get GBH, which may or may not be what you wanted. With only one example, several rules can fit, and Flash Fill has to guess. That is why you should check the output and give more examples when the first guess is wrong.

The same ideas in pandas

In pandas the string accessor .str does the splitting, and ordinary operators or agg join the columns. This block runs in the real Pyodide runtime.

Python str.split, str.cat and join in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main advantage of TEXTJOIN over the ampersand operator?

    TEXTJOIN takes a delimiter and an ignore-empty flag, so joining a range with commas needs one short formula.
  2. 2. What does =TEXTAFTER("a/b/c", "/", -1) return?

    A negative instance number counts delimiters from the end, so -1 means after the last slash, which gives c.
  3. 3. Which tool gives results that update automatically when the source text changes?

    TEXTSPLIT is a formula. Text to Columns and Flash Fill write fixed values.
  4. 4. You split a column of codes with Text to Columns and 007 turns into 7. How do you prevent this?

    The wizard lets you choose a data format for each column. Text keeps leading zeros.
  5. 5. =TEXTSPLIT("a,b;c,d,e", ",", ";") returns a grid with two rows. What is in the last cell of the first row?

    The first row has two pieces and the second has three, so the short row is padded with #N/A unless you set pad_with.

Summary

  • & joins any two values, CONCAT joins ranges, and TEXTJOIN adds a delimiter and an option to skip blanks.
  • Joined numbers and dates lose their formatting; use TEXT to control it.
  • TEXTSPLIT spills pieces into columns or rows; TEXTBEFORE and TEXTAFTER pick one part, with negative instances counting from the end.
  • Text to Columns and Flash Fill are fast but static, so use formulas for data that changes.
  • The newest text functions need Microsoft 365 or Excel for the web.

Next: TEXT, VALUE, NUMBERVALUE and Number-as-Text Problems.