Home>Blogs>Excel Tips and Tricks>TEXTSPLIT function with three use cases in Excel
TEXTSPLIT Function in Excel
Excel Tips and Tricks

TEXTSPLIT function with three use cases in Excel

TEXTSPLIT is extremely useful function in Excel. We use it 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. We will explain TEXTSPLIT function with 3 use cases in this blog post.


Syntax of the TEXTSPLIT function

Below is the syntax of TEXTSPLIT function in Excel

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

Below are the explain of each parameter of TEXTSPLIT  function

  • 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.




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.




Sorted Name column
Sorted Name column


Watch the step-by-step video tutorial:

Click here to download the practice file

Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!