Note: The other languages of the website are Google-translated. Back to English

Excel TEXTSPLIT function

The TEXTSPLIT function splits text strings by a given delimiter to an array across columns and rows.

Note: This function is only available in Excel for Microsoft 365 on the Insider channel.

Syntax

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Arguments

Text (required): The text you want to split;
Col_delimiter (required): The delimiter or substring that indicates where you want to split the text across columns;
Row_delimiter (optional): The delimiter or substring that indicates where you want to split the text across rows;
Ignore_empty (optional): Specifies whether to ignore empty cells or not.
-- FALSE (default): Preserve empty cells when splitting.
-- TRUE: Ignore empty cells when splitting.
Match_mode (optional): Determines whether the delimiter is case-sensitive.
-- 0 (default): Case sensitive.
-- 1: Case insensitive.
Pad_with (optional): The value to pad missing values in 2D array. Default is #N/A error value.

Remarks

1) This function works the same as the Text To Column wizard in Excel;
2) This function is the reverse of the TEXTJOIN function;
3) If “pad_with” is omitted, then #N/A is used to pad missing values in two-dimensional arrays;
4) Multiple delimiters must be enclosed in an array constant and separated by commas, such as {“delimiter1”,”delimiter2”,...}.

Return value

It returns the split text string.

Example

Here we take some examples to demonstrate how to use the TEXTSPLIT function to split text under different conditions.

#Example 1: Split text by a delimiter into columns or rows

As shown in the screenshot below, to split the text in the cell A2 into columns or rows by comma, you can apply the TEXTSPLIT function as follows.

Split text horizontally into columns by commas

Select a cell, enter the following formula and press the Enter key.

The results are populated with split texts at the same time without using the AutoFill Handle.

=TEXTSPLIT(A2,",")

Split text vertically into rows by commas

To split text vertically into rows by a given delimiter, you need to leave the col_delimiter argument blank and specify a delimiter for the row_delimiter argument. Please do as follows.

Select a cell, enter the following formula and press the Enter key.

The result cells are populated with split texts at the same time without using the AutoFill Handle.

=TEXTSPLIT(A2,,",")

#Example 2: Ignore empty cells when splitting text with the TEXTSPLIT function

As shown in the screenshot below, if the text string contains two or more consecutive delimiters without a value between them, by default, the TEXTSPLIT function will preserve empty cells when splitting text. To ignore empty cells when splitting, you can apply the TEXTSPLIT function as follows.

Select a blank cell, enter the following formula and press Enter to get all split texts and ignore the empty cells. If there is a list of texts to split, please select the result cell and drag its AutoFill Handle down to get the rest of the results.

=TEXTSPLIT(A2,",",,TRUE)

Note: In this formula, the logical value “TRUE” is provided as the ignore_empty argument.

#Example 3: Split text case-sensitive or case-insensitive

By default, the TEXTSPLIT function is case sensitive as the match_mode is set to 0 or left blank.

As shown in the screenshot below, the match_mode argument in the TEXTSPLIT function is left blank, so it only searches for an exact match of the text “Consultant” in the specified text string.

=TEXTSPLIT(A2,"Consultant")

If you don’t want to make exact match search, for example, you need to search for the text that includes both “Consultant” and “CONSULTANT”, in this case, you can specify the match_mode argument as 1 to get it done.

Select a blank cell, enter the formula below and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the rest of the results.

=TEXTSPLIT(A2,"Consultant",,TRUE,1)

#Example 4: Split text into rows and columns at the same time

To split text into rows and columns as the same time by using the TEXTSPLIT function, you need to specify both the col_delimiter and the row_delimiter arguments in the formula at the same time.

As shown in the table below, you can enter the following formula into cell C2 and press the Enter key to get the split texts in corresponding rows and columns at the same time.

=TEXTSPLIT(A2,":", ",")

Note: In this case, a colon “:” is provided as the col_delimiter argument and a comma “,” is provided as the row_delimiter argument.

#Example 4: Customize a value to pad missing values in 2D array

When using the TEXTSPLIT function to split text into both rows and columns at the same time, Excel returns #N/A errors in the result if there are missing values in the original text string.

As shown in the screenshot below, since there is no colon “:” after the text Released date, Excel returns #N/A next to “Released date”.

If you need to pad all missing values with a dash (-) character in the 2D array instead of #N/A error, you just need to specify the last argument “pad_with” as “-” to get it done.

Select a blank cell, enter the following formula and press the Enter key to get all split texts in rows and columns.

=TEXTSPLIT(A2,":",",",,,"-")

#Example 5: Split text by multiple delimiters

The TEXTSPLIT function allows multiple delimiters to be used to split text at the same time. However, the delimiters must be enclosed in an array constant and separated by commas, such as {“delimiter1”,”delimiter2”,...}.

The text string in the table below contains two delimiters “-” and “,”, to split the text by these two delimiters, you need to apply the TEXTSPLIT function as follows.

Select a blank cell, enter the following formula and press the Enter key.

=TEXTSPLIT(A2,{"-",","})

Note: In this formula, the col_delimiter argument is specified as {"-",","}.


Related Functions

Excel TEXTAFTER function
The TEXTAFTER function extracts and returns the text that occurs after a given substring or delimiter.

Excel TEXTBEFORE function
The TEXTBEFORE function returns the text that occurs before a given substring or delimiter.

Excel VALUETOTEXT Function
The VALUETOTEXT function converts any specified value to text.


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL