TEXTSPLIT Function in Excel Tutorial

Excel Tutorial

Introduction

The TEXTSPLIT function in Excel helps you divide text from a single cell into multiple columns or rows using specific delimiters. It is useful for separating employee names, email addresses, product details, course information, and other combined data. In this tutorial, you will learn how to use TEXTSPLIT with practical examples, understand its arguments, and practise splitting real-world data.

TEXTSPLIT Function Syntax

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

Arguments:

  • text: The text or cell reference you want to split.
  • col_delimiter: The character or text used to split data into columns.
  • row_delimiter: The character or text used to split data into rows.
  • ignore_empty: TRUE ignores empty values; FALSE keeps them.
  • match_mode: 0 performs a case-sensitive match; 1 performs a case-insensitive match.
  • pad_with: The value used to fill empty positions when splitting into rows and columns.

Note: TEXTSPLIT is available in Microsoft 365 and Excel 2024.

Question 1: Split a Full Name into First Name and Last Name

Problem Statement

An HR department stores employees’ full names in a single cell. Separate the first name and last name into two columns.

Full Name
Rahul Sharma
Priya Verma
Amit Kumar
Neha Singh

Assume the names are in cells A2.

Excel Formula

=TEXTSPLIT(A2," ")

Expected Result

First NameLast Name
RahulSharma
PriyaVerma
AmitKumar
NehaSingh

Explanation: The space character is used as the column delimiter. Excel separates the text wherever it finds a space and places the results in separate columns.

Student Task: Use the same formula to split these names:

Full Name
Karan Mehta
Simran Kapoor

New Expected Result

First NameLast Name
KaranMehta
SimranKapoor

Question 2: Split Employee IDs Using a Hyphen

Problem Statement

A company maintains employee IDs in a combined format. Separate the department code and employee number.

Employee ID
HR-101
IT-205
FIN-310
MKT-415

Assume employee IDs are in cells A2.

Excel Formula

=TEXTSPLIT(A2,"-")

Expected Result

Department CodeEmployee Number
HR101
IT205
FIN310
MKT415

Explanation: The hyphen (-) acts as the delimiter. TEXTSPLIT separates each employee ID into two parts.

Student Task: Split these additional employee IDs:

Employee ID
ACC-520
ADM-625

New Expected Result

Department CodeEmployee Number
ACC520
ADM625

Question 3: Split an Email Address into Username and Domain

Problem Statement

A company maintains employee email addresses in one column. Separate the username and domain.

Email Address
rahul@gmail.com
priya@yahoo.com
amit@company.in
neha@outlook.com

Assume email addresses are in cells A2.

Excel Formula

=TEXTSPLIT(A2,"@")

Expected Result

UsernameDomain
rahulgmail.com
priyayahoo.com
amitcompany.in
nehaoutlook.com

Explanation: The @ symbol separates the username from the domain. TEXTSPLIT places both parts into separate columns.

Student Task: Split these email addresses:

Email Address
karan@vsit.in
simran@codermantra.com

New Expected Result

UsernameDomain
karanvsit.in
simrancodermantra.com

Question 4: Split Product Details Using a Comma

Problem Statement

An inventory department stores product name, category, and brand in one cell. Separate all three details into individual columns.

Product Details
Laptop,Electronics,Dell
Keyboard,Accessories,Logitech
Monitor,Electronics,HP
Mouse,Accessories,Lenovo

Assume product details are in cells A2.

Excel Formula

=TEXTSPLIT(A2,",")

Expected Result

ProductCategoryBrand
LaptopElectronicsDell
KeyboardAccessoriesLogitech
MonitorElectronicsHP
MouseAccessoriesLenovo

Explanation: The comma is the delimiter. Each comma separates one piece of product information into a new column.

Student Task: Split these product details:

Product Details
Printer,Electronics,Canon
Chair,Furniture,Godrej

New Expected Result

ProductCategoryBrand
PrinterElectronicsCanon
ChairFurnitureGodrej

Question 5: Split Text into Multiple Rows

Problem Statement

A training institute stores multiple course names in a single cell, separated by commas. Display each course in a separate row.

Course List
Python,Java,PHP,SQL

Assume the course list is in cell A2.

Excel Formula

=TEXTSPLIT(A2,,",")

Expected Result

Course Name
Python
Java
PHP
SQL

Explanation: The second argument is left blank because we do not want to split into columns. The third argument specifies the comma as the row delimiter.

Student Task: Split this course list into separate rows:

Excel,Power BI,Tableau,MySQL

New Expected Result

Course Name
Excel
Power BI
Tableau
MySQL

Question 6: Split Text Using Multiple Delimiters

Problem Statement

A sales department receives product details separated by either a comma or a hyphen. Separate the product name, category, and code into columns.

Product Information
Laptop-Electronics,101
Chair-Furniture,102
Mouse-Accessories,103

Assume product information is in cells A2.

Excel Formula

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

Expected Result

ProductCategoryCode
LaptopElectronics101
ChairFurniture102
MouseAccessories103

Explanation: The delimiter argument contains two characters: comma and hyphen. TEXTSPLIT uses either character to separate the text.

Student Task: Split these records using both delimiters:

Product Information
Keyboard-Accessories,104
Monitor-Electronics,105

New Expected Result

ProductCategoryCode
KeyboardAccessories104
MonitorElectronics105

Question 7: Ignore Empty Values While Splitting Text

Problem Statement

A company has employee names separated by commas, but some records contain extra commas that create empty spaces. Split the names while ignoring empty values.

Employee Names
Rahul,,Priya,Amit
Neha,,,Karan

Assume the data is in cells A2.

Excel Formula

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

Expected Result

For the first record:

Name 1Name 2Name 3
RahulPriyaAmit

For the second record:

Name 1Name 2
NehaKaran

Explanation: The fourth argument is TRUE, which tells Excel to ignore empty values created by consecutive commas.

Student Task: Split the following data while ignoring empty values:

Amit,,Neha,,Simran

New Expected Result

Name 1Name 2Name 3
AmitNehaSimran

Question 8: Keep Empty Values While Splitting Text

Problem Statement

A company stores employee information in a fixed sequence. Some records have missing middle values. Split the information while keeping empty positions.

Employee Details
Rahul,Sales,Delhi
Priya,,Mumbai
Amit,IT,Noida

Assume the data is in cells A2.

Excel Formula

=TEXTSPLIT(A2,",",,FALSE)

Expected Result

EmployeeDepartmentCity
RahulSalesDelhi
Priya(Blank)Mumbai
AmitITNoida

Explanation: The fourth argument is FALSE, so Excel keeps empty values. This preserves the original positions of employee details.

Student Task: Split this record while keeping the missing department value:

Neha,,Gurugram

New Expected Result

EmployeeDepartmentCity
Neha(Blank)Gurugram

Question 9: Split Text Using Case-Insensitive Delimiters

Problem Statement

A company receives product details where the separator may appear in uppercase or lowercase. Split the records using a case-insensitive delimiter.

Product Details
LaptopXDELL
Mousexdell
KeyboardXLOGITECH

Assume the data is in cells A2.

Excel Formula

=TEXTSPLIT(A2,"x",,FALSE,1)

Expected Result

ProductBrand
LaptopDELL
Mousedell
KeyboardLOGITECH

Explanation: The fifth argument is set to 1, which makes delimiter matching case-insensitive. Excel can therefore recognize both X and x as the separator.

Student Task: Split these records using the same case-insensitive delimiter:

Product Details
MonitorXHP
PrinterxCanon

New Expected Result

ProductBrand
MonitorHP
PrinterCanon

Question 10: Split Data into Rows and Columns with Padding

Problem Statement

A training institute stores course information in one cell. Each course has a course name and duration, separated by a comma. The records are separated by a semicolon. One record is missing its duration.

Course Information
Python,3 Months;Java,4 Months;PHP

Assume the information is in cell A2.

Excel Formula

=TEXTSPLIT(A2,",",";",FALSE,0,"Not Provided")

Expected Result

Course NameDuration
Python3 Months
Java4 Months
PHPNot Provided

Explanation: The comma separates columns, while the semicolon separates rows. The sixth argument fills a missing position with Not Provided, keeping the output in a consistent two-column structure.

Student Task: Split this information into rows and columns, using Not Available for missing values:

Excel,2 Months;Power BI,3 Months;SQL

New Expected Result

Course NameDuration
Excel2 Months
Power BI3 Months
SQLNot Available

Student Practice Tasks

Try these exercises independently using only the TEXTSPLIT function.

  1. Split Aarav Sharma into two columns using a space.
  2. Split IT-205 into department code and employee number.
  3. Split support@company.com into username and domain.
  4. Split Laptop,Electronics,Dell into three columns.
  5. Split Python,Java,PHP,SQL into separate rows.
  6. Split Delhi,,Mumbai while ignoring empty values.
  7. Split Rahul,,Sales while keeping empty values.
  8. Split MonitorXHP using a case-insensitive delimiter.
  9. Split Excel,2 Months;Python,3 Months into rows and columns.
  10. Split HR-101,Sales;IT-205,Technology into a two-row, two-column result.

Final Student Challenge: Employee and Department Data Separation

Problem Statement

A company stores employee records in a single cell. Each record contains an employee name, department, and city. The information is separated by commas, while employee records are separated by semicolons.

Your task is to split the complete information into three columns and multiple rows.

Given Data

Employee Records
Rahul,HR,Delhi;Priya,IT,Mumbai;Amit,Sales,Noida;Neha,Finance,Gurugram

Assume the complete record is stored in cell A2.

Your Task

Use TEXTSPLIT to separate:

  • Employee Name
  • Department
  • City

Expected Result

Employee NameDepartmentCity
RahulHRDelhi
PriyaITMumbai
AmitSalesNoida
NehaFinanceGurugram

Excel Formula

=TEXTSPLIT(A2,",",";")

Explanation: The comma separates the three employee details into columns, while the semicolon separates each employee record into a new row.

Key Takeaways

  • TEXTSPLIT divides text into multiple columns or rows.
  • The second argument defines the column delimiter.
  • The third argument defines the row delimiter.
  • Multiple delimiters can be supplied together.
  • TRUE ignores empty values, while FALSE keeps them.
  • The match_mode argument controls case-sensitive or case-insensitive delimiter matching.
  • The pad_with argument fills missing positions in a split result.
  • TEXTSPLIT is useful for separating employee, product, email, and course information.

Frequently Asked Questions (FAQs)

1. What is the TEXTSPLIT function in Excel?

TEXTSPLIT is a text function that divides text into multiple columns or rows based on specified delimiters.

2. What is the syntax of TEXTSPLIT?

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

3. Can TEXTSPLIT split text into multiple columns?

Yes. Use the second argument to specify the delimiter that separates values into columns.

4. Can TEXTSPLIT split text into multiple rows?

Yes. Use the third argument to specify a row delimiter.

5. How can I split text using multiple delimiters?

You can provide multiple delimiters inside an array constant, such as:

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

6. What does ignore_empty do in TEXTSPLIT?

It controls whether empty values are ignored. TRUE ignores empty values, while FALSE keeps them.

7. Is TEXTSPLIT case-sensitive?

By default, delimiter matching is case-sensitive. Set match_mode to 1 for case-insensitive matching.

8. What is the use of pad_with in TEXTSPLIT in Excel?

It specifies what Excel should display when a split result has missing positions, such as Not Provided.

9. Is TEXTSPLIT available in all Excel versions?

No. TEXTSPLIT is available in Microsoft 365 and Excel 2024. It may not work in older Excel versions.

10. What is the difference between TEXTSPLIT and TEXTJOIN?

TEXTSPLIT separates combined text into multiple cells, while TEXTJOIN combines text from multiple cells into one text string.