The TEXTSPLIT function
"Smith;Leeds;LS1" becomes three cells, through a formula that follows the source. Text to Columns does it once; TEXTSPLIT does it always.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
In a French Excel: =FRACTIONNER.TEXTE(texte; séparateur_col; [séparateur_ligne]; [ignorer_vide]; [mode_correspondance]; [remplir_avec]).
Arguments
- text: the cell.
- col_delimiter: where to cut into columns.
- row_delimiter: where to cut into rows.
- ignore_empty: TRUE to skip empty pieces.
An example
=TEXTSPLIT(A2,";")
Each piece of A2 in its own column.
Pitfalls
- Microsoft 365 only.
- Copied down several rows, each row spills to the right: no overlap as long as neighbouring columns are empty.
Going further
- TEXTBEFORE.
- Split or merge columns: a whole column, in every Excel version.
Frequently asked questions
- And without Microsoft 365?
- Data, Text to Columns; or LEFT, MID and FIND, piece by piece.