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 Name | Last Name |
|---|---|
| Rahul | Sharma |
| Priya | Verma |
| Amit | Kumar |
| Neha | Singh |
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 Name | Last Name |
|---|---|
| Karan | Mehta |
| Simran | Kapoor |
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 Code | Employee Number |
|---|---|
| HR | 101 |
| IT | 205 |
| FIN | 310 |
| MKT | 415 |
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 Code | Employee Number |
|---|---|
| ACC | 520 |
| ADM | 625 |
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
| Username | Domain |
|---|---|
| rahul | gmail.com |
| priya | yahoo.com |
| amit | company.in |
| neha | outlook.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
| Username | Domain |
|---|---|
| karan | vsit.in |
| simran | codermantra.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
| Product | Category | Brand |
|---|---|---|
| Laptop | Electronics | Dell |
| Keyboard | Accessories | Logitech |
| Monitor | Electronics | HP |
| Mouse | Accessories | Lenovo |
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
| Product | Category | Brand |
|---|---|---|
| Printer | Electronics | Canon |
| Chair | Furniture | Godrej |
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
| Product | Category | Code |
|---|---|---|
| Laptop | Electronics | 101 |
| Chair | Furniture | 102 |
| Mouse | Accessories | 103 |
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
| Product | Category | Code |
|---|---|---|
| Keyboard | Accessories | 104 |
| Monitor | Electronics | 105 |
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 1 | Name 2 | Name 3 |
|---|---|---|
| Rahul | Priya | Amit |
For the second record:
| Name 1 | Name 2 |
|---|---|
| Neha | Karan |
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 1 | Name 2 | Name 3 |
|---|---|---|
| Amit | Neha | Simran |
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
| Employee | Department | City |
|---|---|---|
| Rahul | Sales | Delhi |
| Priya | (Blank) | Mumbai |
| Amit | IT | Noida |
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
| Employee | Department | City |
|---|---|---|
| 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
| Product | Brand |
|---|---|
| Laptop | DELL |
| Mouse | dell |
| Keyboard | LOGITECH |
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
| Product | Brand |
|---|---|
| Monitor | HP |
| Printer | Canon |
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 Name | Duration |
|---|---|
| Python | 3 Months |
| Java | 4 Months |
| PHP | Not 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 Name | Duration |
|---|---|
| Excel | 2 Months |
| Power BI | 3 Months |
| SQL | Not Available |
Student Practice Tasks
Try these exercises independently using only the TEXTSPLIT function.
- Split
Aarav Sharmainto two columns using a space. - Split
IT-205into department code and employee number. - Split
support@company.cominto username and domain. - Split
Laptop,Electronics,Dellinto three columns. - Split
Python,Java,PHP,SQLinto separate rows. - Split
Delhi,,Mumbaiwhile ignoring empty values. - Split
Rahul,,Saleswhile keeping empty values. - Split
MonitorXHPusing a case-insensitive delimiter. - Split
Excel,2 Months;Python,3 Monthsinto rows and columns. - Split
HR-101,Sales;IT-205,Technologyinto 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 Name | Department | City |
|---|---|---|
| Rahul | HR | Delhi |
| Priya | IT | Mumbai |
| Amit | Sales | Noida |
| Neha | Finance | Gurugram |
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.

