Excel Formula Opposite of CONCATENATE: Split Text Apart

Formulas & functions · Updated November 18, 2026

The closest Excel formula to the opposite of CONCATENATE is =TEXTSPLIT(A2,"|"): it takes text in A2 and splits it wherever it finds a vertical bar, returning the pieces in separate cells. Replace the bar with the delimiter in your data; TEXTSPLIT is available in Microsoft 365 and Excel 2024.

What “the opposite” means in Excel

CONCATENATE joins text strings into one, while TEXTSPLIT separates one text string into parts. In current Excel, CONCAT and TEXTJOIN are also common ways to combine text. For example, a combined value such as Maya Chen|Sales|Toronto can be split into a name, department, and city when the vertical bar is the agreed separator.

There is an important limitation: splitting is not always a true reversal. Once values have been combined, Excel cannot know where the original values ended unless the combined text contains a delimiter or another reliable pattern. If someone joined “Ann” and “A Lee” without a separator, the result “AnnA Lee” does not reveal where one original value stops. Even with a delimiter, the original pieces may be ambiguous if that delimiter also appears inside a value.

So the practical answer is TEXTSPLIT when the combined text uses a consistent separator. If the text has no separator, or its structure varies, use a different cleanup method or inspect the source data rather than expecting one formula to reconstruct it perfectly.

How TEXTSPLIT works

TEXTSPLIT searches the text for the delimiter you specify and returns each segment in a separate cell. With the source text in A2, the formula below splits at each vertical bar and spills results across the row, starting in the cell where you enter the formula.

With Maya Chen|Sales|Toronto in A2, enter =TEXTSPLIT(A2,"|") in B2. Excel returns Maya Chen in B2, Sales in C2, and Toronto in D2.

The delimiter is not part of the returned pieces. TEXTSPLIT can also split on a word, multiple characters, or a line break. Its second argument is the column delimiter; an optional third argument is the row delimiter. If you supply both, Excel can split text into a two-dimensional range. This is useful for structured text, but the input still needs consistent separators and ordering.

TEXTSPLIT returns a dynamic array: one formula can fill several neighboring cells automatically. Those results are called a spill range. If a cell where a result needs to go already contains a value, Excel displays a #SPILL! error; clear the obstructing cells or move the formula.

Split a cell with a delimiter

To use the formula, identify the exact character or text sequence that separates the pieces. Then enter the formula in an empty cell beside the source data. For a vertical bar-delimited value in A2, the formula is =TEXTSPLIT(A2,"|"). The result is a horizontal set of values, one for each segment.

  1. Check several source cells to confirm they use the same delimiter, such as a comma, semicolon, hyphen, or pipe.
  2. Select an empty cell where the first result can appear and where the cells to its right are clear.
  3. Enter TEXTSPLIT with the source cell and the delimiter in quotation marks, then press Enter.
  4. Review the spilled values, especially records with missing pieces or extra delimiters.
  5. If you need fixed values rather than a live formula, copy the results and use Paste Special > Values.

For example, if A2 contains INV-2048-2026, using a hyphen as the delimiter returns three pieces: INV, 2048, and 2026. If a segment is a number or date, consider the result’s data type before using it in calculations. Text split from a string may need conversion or formatting to behave as a numeric value or date.

Choose the right delimiter and direction

TEXTSPLIT normally places pieces in columns when you provide the column delimiter. The optional row delimiter lets you split into rows instead. This distinction matters when a list needs to run downward rather than across. A line break can be used as a delimiter with CHAR(10), often useful for text copied from cells with in-cell line breaks.

Delimiters can contain more than one character. If a record uses the sequence / (space, slash, space) between fields, use that whole sequence as the delimiter rather than splitting at every slash. Choosing a specific delimiter helps avoid accidentally breaking values that contain a similar character.

For a list separated by commas, first check whether commas also appear inside individual fields. A value such as Paris, France contains a comma that may be part of the city field. TEXTSPLIT treats every matching delimiter as a split point; it does not interpret quoted fields using the full CSV rules. For a proper CSV file with quoted commas, import it through Excel’s text/CSV import tools instead of assuming a simple split will preserve every field.

Empty fields, extra delimiters, and errors

Repeated delimiters can create empty pieces. For instance, North||2026 has nothing between the two bars. By default, TEXTSPLIT ignores empty segments; its optional ignore_empty argument controls this behavior. Set that argument to FALSE when blank positions matter, such as when the second field is intentionally missing.

With North||2026 in A2, use =TEXTSPLIT(A2,"|",,FALSE) in B2 to retain the empty middle field. The spilled results are North, a blank cell, and 2026. The two consecutive commas in the formula leave the optional row-delimiter argument empty so that FALSE is supplied as the fourth argument.

A different problem occurs when records have different numbers of delimiters. TEXTSPLIT may return different numbers of pieces for different source cells, so each formula can spill a different width. For a single formula splitting several rows and columns, uneven row lengths can require padding; TEXTSPLIT has an optional pad_with argument for that situation. Decide what a missing field should mean before filling it with a placeholder such as a blank or “N/A.”

  • #SPILL! usually means cells in the result range are occupied, or the formula is inside a layout that cannot accept the spill.
  • Unexpected extra pieces often mean the chosen delimiter also appears within some values.
  • One long result may mean the delimiter typed in the formula does not exactly match the one in the source.
  • Numbers treated as text may need conversion after splitting if you intend to calculate with them.

TEXTSPLIT compared with Text to Columns

Text to Columns is Excel’s built-in, formula-free option for splitting a selected column. In Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, select Delimited, and follow the wizard to choose the separator and destination. It is useful for a one-time cleanup, especially when you want to inspect a preview before applying the split.

Unlike TEXTSPLIT, Text to Columns writes results into cells rather than maintaining a formula linked to the original text. Choose a destination with enough empty columns so existing data is not overwritten. If the source changes later, you generally need to run the operation again. The command is also available in desktop Excel for Mac, although the ribbon layout can differ; Excel for the web may offer a different set of data tools, so a formula is often the more portable approach there.

Choose TEXTSPLIT when you want results that update as the source changes, or when the split should be part of a reusable worksheet. Choose Text to Columns for a quick, one-off transformation or when its preview and import options better suit the cleanup. For files with quoted CSV fields or complicated structure, use a proper import process rather than either a basic delimiter split or manual guesswork.

Can you join the pieces back together?

To combine a row of split values with a delimiter, TEXTJOIN is a useful counterpart. It can also ignore empty cells if you choose TRUE for its second argument. With the split values in B2:D2, enter =TEXTJOIN(" | ",TRUE,B2:D2) in E2. For Maya Chen, Sales, and Toronto, the result is Maya Chen | Sales | Toronto.

That result is not identical to the original if the original had no spaces around its bars. To recreate the exact original, use the same delimiter string that was used to join the fields. Also note that ignoring empty cells can remove meaningful blank positions: if a blank middle field must remain represented by two adjacent delimiters, do not ignore empty cells when joining.

TEXTJOIN can restore a joined string only when you know how the fields should be combined. It cannot recover leading or trailing spaces that were lost, infer missing values, or distinguish delimiter characters that were part of the original field from delimiters used between fields. Keeping the original columns or storing a clear data format is safer when exact reconstruction matters.

Frequently Asked Questions

What is the opposite function of CONCATENATE in Excel?
The closest modern counterpart is TEXTSPLIT, which separates text at a delimiter. It is not a perfect inverse: it needs a known separator and cannot infer boundaries that were never recorded.

Can I split text without a formula?
Yes. In desktop Excel, select the source cells and use Data > Text to Columns. Choose Delimited and set the separator in the wizard. This is convenient for a one-time split; TEXTSPLIT is better when the result should update with its source.

Why is TEXTSPLIT not recognized?
TEXTSPLIT requires Microsoft 365 or Excel 2024. In an older Excel edition, use Text to Columns, or use a compatible formula designed for the particular delimiter and position pattern.

Can TEXTSPLIT split a cell into rows instead of columns?
Yes. Use the optional row-delimiter argument to specify what should create a new row. The column-delimiter argument comes first, so leave it empty if you want to provide only a row delimiter.

Does TEXTSPLIT handle commas inside quoted CSV fields?
No. It splits wherever its specified delimiter appears and does not apply CSV quotation rules. Import the CSV with Excel’s text/CSV import tools when quoted delimiters need to remain inside a field.

What if I want to split only at the first delimiter?
TEXTSPLIT splits at each matching delimiter. If you need only the text before or after the first occurrence, use a function suited to that task, such as TEXTBEFORE or TEXTAFTER, available in Microsoft 365 and Excel 2024.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.