Description
Splits text strings by using column and row delimiters.
NOTE
- By default, TEXTSPLIT is case sensitive function and does not support wildcards.
- Common error:
– If instance_num greater than the length of text, the function returns the #VALUE error.
– If the text input does not contain a delimiter, or [instance_num] is greater than the number of occurrences of delimiter, the function returns the #NA error.
Syntax
Copied!=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
SAMPLE data
| | A | B | C | D |
|---|----------------------------------------|----|----|----|
| 1 | Excel 2024, Word 2025, PowerPoint 2026 | | | |EXAMPLE 1
Copied!//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘪𝘨𝘯𝘰𝘳𝘦_𝘦𝘮𝘱𝘵𝘺=[0,1], 𝘮𝘢𝘵𝘤𝘩_𝘮𝘰𝘥𝘦=[0,1], 𝘱𝘢𝘥_𝘸𝘪𝘵𝘩=[#𝘕𝘈] //𝘈1="𝘌𝘹𝘤𝘦𝘭 2024, 𝘞𝘰𝘳𝘥 2025, 𝘗𝘰𝘸𝘦𝘳𝘗𝘰𝘪𝘯𝘵 2026" ------------------------------ //𝘴𝘱𝘭𝘪𝘵 𝘪𝘯𝘵𝘰 𝘤𝘰𝘭𝘶𝘮𝘯𝘴 =TEXTSPLIT(A1, " ")
| | A | B | C |
|:-:|:-----------|:----------|:----------------|
| 1 | Excel 2024 | Word 2025 | PowerPoint 2026 |EXAMPLE 2
Copied!//𝘴𝘱𝘭𝘪𝘵 𝘪𝘯𝘵𝘰 𝘳𝘰𝘸𝘴 =TEXTSPLIT(A1, , ", ")
| | A |
|---|-----------------|
| 1 | Excel 2024 |
| 2 | Word 2025 |
| 3 | PowerPoint 2026 |EXAMPLE 3
Copied!//𝘵𝘳𝘢𝘯𝘴𝘧𝘰𝘳𝘮 𝘵𝘰 2-𝘥 =TEXTSPLIT(A1, " ", ", ")
| | A | B |
|---|------------|------|
| 1 | Excel | 2024 |
| 2 | Word | 2025 |
| 3 | PowerPoint | 2026 |example 4
Copied!//𝘶𝘴𝘦 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘤𝘰𝘭𝘶𝘮𝘯 𝘥𝘦𝘭𝘪𝘮𝘪𝘵𝘦𝘳𝘴 =TEXTSPLIT(B26, {" ", ", "})
| | A | B | C | D | E | F |
|---|------|------|------|------------|------|----|
| 1 | 2024 | Word | 2025 | PowerPoint | 2026 | |