TEXTSPLIT Function in Excel
Excel Tips and Tricks

TEXTSPLIT function with three use cases in Excel

TEXTSPLIT is used to split text by using column and row delimiters in Excel. This function can enable you to split the text across columns or down by rows.

 

Syntax

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

 

text: Provide the reference for the text you want to split. It is required parameter.

col_delimiter:  The delimiter text that is the point where to spill the text across columns.

row_delimiter: The delimiter text that is the point where to spill the text across rows. It is optional.

ignore_empty: Select TRUE to ignore consecutive delimiters. Defaults value is FALSE to creates a blank cell. It is optional.

match_mode: Select 1 to perform a case-insensitive match. Default value is 0, which is for a non case-sensitive match. It is Optional.

pad_with: Put the value with which to pad the result. The default value is #N/A, you can change it.

 

Here, we have explained the TEXTSPLIT Function with three Use Cases as given below

 

Use case 1:

In the first use case we have a data of Name, Age and Mobile Number in combine format, and we have format it is using TEXTSPLIT function.

 

=TEXTSPLIT(A1,"-",",")

 

Name, Age and Mobile Number Data
Name, Age and Mobile Number Data

 

Use case 2:

In the second use case we have multiple email ids and name which are copied from the outlook mail. We need to get the proper display name and email id in separate column.

 

=SUBSTITUTE(TEXTSPLIT(A1," <","; "),">","")

 

Name and Email ID data
Name and Email ID data

 

Use Case 3:

 

In the third use case, we have used the TEXTSPLIT function with some other functions to highlight the duplicate names even if first name and last name is shuffled.

 

To create the Sorted Name column, we have used below formula then we have conditional formatting on the Name column with the help of Sorted Name column.

 

=TEXTJOIN(" ",TRUE,SORT(TEXTSPLIT([@Name]," "),,1,TRUE))

 

Sorted Name column
Sorted Name column

 

Watch the step-by-step video tutorial:

Click here to download the practice file

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com