Splitting and Extracting Text in Microsoft Excel
Microsoft Excel offers several text functions that are useful for extracting, splitting, and manipulating text. These functions, introduced in August 2022, provide more flexibility than older functions like LEFT, RIGHT, and MID. Here’s a breakdown of three key functions for working with text: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT.
1. TEXTBEFORE Function
The TEXTBEFORE function is used to extract the part of a string that appears before a specified delimiter.
Syntax:
excelCopyEditTEXTBEFORE(text, delimiter, instance, match_mode, match_end, if_not_found)
- text: The text string or cell reference to extract from.
- delimiter: The character or word that defines the point before which the text is extracted.
- instance: (Optional) The occurrence of the delimiter to use (e.g., 1 for the first instance, 2 for the second).
- match_mode: (Optional) 0 for case-sensitive, 1 for not case-sensitive (default: 0).
- match_end: (Optional) 0 to not match the delimiter to the end of the text, 1 to include it.
- if_not_found: (Optional) What to return if the delimiter is not found.
Example Uses:
- Extract text before “from” in cell A2:excelCopyEdit
=TEXTBEFORE(A2, "from") - Extract text before the second instance of “text”:excelCopyEdit
=TEXTBEFORE(A2, "text", 2) - Case-sensitive extraction before “TEXT”:excelCopyEdit
=TEXTBEFORE(A2, "TEXT", , 0)
2. TEXTAFTER Function
The TEXTAFTER function works the opposite of TEXTBEFORE, allowing you to extract the part of a string after a specified delimiter.
Syntax:
excelCopyEditTEXTAFTER(text, delimiter, instance, match_mode, match_end, if_not_found)
- text: The text string or cell reference to extract from.
- delimiter: The character or word after which the text is extracted.
- The other arguments function the same way as in
TEXTBEFORE.
Example Uses:
- Extract text after “from” in cell A2:excelCopyEdit
=TEXTAFTER(A2, "from") - Extract text after the second instance of “text”:excelCopyEdit
=TEXTAFTER(A2, "text", 2) - Case-sensitive extraction after “TEXT”:excelCopyEdit
=TEXTAFTER(A2, "TEXT", , 0)
3. TEXTSPLIT Function
The TEXTSPLIT function allows you to split a text string into multiple cells, either across columns or rows, based on a specified delimiter (e.g., a space or semicolon).
Syntax:
excelCopyEditTEXTSPLIT(text, column_delimiter, row_delimiter, ignore, match_mode, pad_with)
- text: The text string or cell reference to split.
- column_delimiter: (Optional) The character that separates values in columns (e.g., a space).
- row_delimiter: (Optional) The character that separates values in rows.
- ignore: (Optional) TRUE or FALSE to determine whether to ignore consecutive delimiters.
- match_mode: (Optional) 0 for case-sensitive, 1 for not case-sensitive.
- pad_with: (Optional) Value to display if the split results in empty cells.
Example Uses:
- Split a text string in cell A2 across columns using a space as the delimiter:excelCopyEdit
=TEXTSPLIT(A2, " ") - Split a text string in cell A2 across rows using a space as the row delimiter:excelCopyEdit
=TEXTSPLIT(A2, , " ") - Split a text string in cell A2 after each semicolon into columns:excelCopyEdit
=TEXTSPLIT(A2, ";") - Split a text string in cell A2 after each semicolon into rows:excelCopyEdit
=TEXTSPLIT(A2, , ";")