Skip to main content

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations