Table of Contents
The TEXTSPLIT function in Excel is a useful tool for separating text into multiple columns based on a specified delimiter. This function can be used to quickly and accurately organize large amounts of text data, saving time and effort. To use the TEXTSPLIT function, simply input the text to be split, along with the delimiter, and the function will automatically divide the text into separate columns. This allows for better data analysis and organization within an Excel spreadsheet. The TEXTSPLIT function is a valuable tool for anyone working with text data in Excel.
Splits text strings by using column and row delimiters.
The TEXTSPLIT function works the same as the Text-to-Columns wizard, but in formula form. It allows you to split across columns or down by rows. It is the inverse of the TEXTJOIN function.
Syntax
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
The TEXTSPLIT function syntax has the following arguments:
text The text you want to split. Required.
col_delimiter The text that marks the point where to spill the text across columns.
row_delimiter The text that marks the point where to spill the text down rows. Optional.
ignore_empty Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.
match_mode Specify 1 to perform a case-insensitive match. Defaults to 0, which does a case-sensitive match. Optional.
pad_with The value with which to pad the result. The default is #N/A.
Remarks
If there is more than one delimiter, then an array constant must be used. For example, to split by both a comma, and a period, use =TEXTSPLIT(A1,{“,”,”.”}).
Examples
Copy the example data and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Split a name and a sentence by using a common delimiter.
Data |
Dakota Lennon Sanchez |
To be or not to be |
Formulas |
=TEXTSPLIT(A2, ” “) |
=TEXTSPLIT(A3, ” “) |
The following illustration shows the results.
Split the array constants in A2 into a 2X3 array.
Data |
1,2,3;4,5,6 |
Formulas |
=TEXTSPLIT(A2,”,”,”;”) |
The following illustration shows the results.
Data |
Do. Or do not. There is no try. -Anonymous |
Formulas |
=TEXTSPLIT(A2,,”.”) |
The following illustration shows the results.
Data |
Do. Or do not. There is no try. -Anonymous |
Formulas |
=TEXTSPLIT(A2,,{“.”,”-“},TRUE) |
The following illustration shows the results.
Tip To remove the #NA error, use the IFNA function. Or add the pad_with argument.
Data |
Do. Or do not. There is no try. -Anonymous |
Formulas |
=TEXTSPLIT(A2,” “,”.”,TRUE) |
The following illustration shows the results.
See Also
Cite this article
stats writer (2024). How can I use the TEXTSPLIT function in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-textsplit-function-in-excel/
stats writer. "How can I use the TEXTSPLIT function in Excel?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-textsplit-function-in-excel/.
stats writer. "How can I use the TEXTSPLIT function in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-textsplit-function-in-excel/.
stats writer (2024) 'How can I use the TEXTSPLIT function in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-textsplit-function-in-excel/.
[1] stats writer, "How can I use the TEXTSPLIT function in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use the TEXTSPLIT function in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.