Yes, there are several ways to split texts in Excel. A couple of years ago we were used to using LEFT, MID, and RIGHT functions in order to split the texts. However, Excel launched two new ways to perform this task within seconds.
Thanks to the TEXTSPLIT function, we are now able to separate text in a shortcut way.
In this post, we will show you how to separate text and numbers in excel in the easiest way possible.
Understanding Excel TEXTSPLIT Function
TEXTSPLIT function works the best if your text string has multiple spaces, commas, or a mixture of delimiters.
The syntax of this function is:
Here you can see six arguments. But only the first two arguments are mandatory. The rest of the four arguments are optional and only needed based on a criterion.
Explanation of the arguments:
|
Arguments |
Description |
| text | It is the text string that you want to split into columns or rows. |
| col_delimiter | This is the delimiter that separates columns in the text string. |
| row_delimiter | This is the delimiter that separates rows in the text string. |
| ignore_empty | This argument determines whether empty values should be included in the output or not. |
| match_mode | Determines how the function matches delimiters in the text string. |
| pad_with | What value to use to fill in any empty cells that result from splitting the text string. |
Text Split Not Available in Excel?
The Text split function is available in Microsoft 365 and the Online web version of Microsoft (For both Windows, Mac).
If you have older versions of Excel installed, you have to use the LEFT, MID, RIGHT functions. Or, you can use the TEXT to COLUMNS Wizard!
Use the Text to Columns Wizard in Excel To Split Data
You can Split text into different columns using this built-in wizard. The best thing about this wizard is- you can split text without entering any single function. Just as the way we discussed on using formulas without using a function.
So, how to separate text and numbers in excel?
Select the data you want to separate into columns. Go to the Data Tab and select the Text to Columns option. Click on the Delimited option and Press the Next Button. Select the delimiters (it can be comma, semicolon, colon, space, or other custom delimiters).
See the Preview of the selected data and Press Next. Format the column and hit the Finish Button.
How To Split Text in Excel using the TEXTSPLIT Function
There are several functions to separate texts. Some of the most notable ones are FILTERXML, MID, LEFT, RIGHT, CHAR, and the new TEXTSPLIT function. Out of all these functions, TEXTSPLIT saves you time and is the easiest way to separate texts into columns.
In short:
TEXTSPLIT is the formula version of “Text to columns” Wizard in Excel.
Here are the steps to use TEXTSPLIT function for splitting texts:
- Select any blank cell where you want Excel to return the result.
- Type =TEXTSPLIT and select the cell which you want to split as the text. This will be your text argument.
- Specify the column delimiters. It can be comma, semicolon, colon, space, etc.
- Press Enter to return the final result if you don’t have any specific row delimiters.
Advanced Examples Of Using the TEXTSPLIT Function Properly
Now that you are familiar with the basics of the function. Let’s solve some problems using the latest function.
In this section, we will show you different criteria and how to solve the problem using Text Split. We will also be using some combination functions as well. Like the TRIM, SORT, and TEXTJOIN function.
So, let’s get to solving the first example.
Example 1: Split Single Cell Texts into Multiple Cells
Look at the dataset in the following image. We have the Employee Names on one column and the list of skills learned by them in another column. We want to separate the learnt skills into separate columns.
Point to note that, here, the delimiter is a comma and a space together. So, we will put these delimiters in the col_delimiter argument.
Select an empty cell. Type the TEXTSPLIT function. Select the text argument. In this case, B2 cell is our text to split. The column delimiters are a comma and a space together. There aren’t any row delimiters, or any values to ignore. So, we will skip the rest of the arguments.
You can also use a combination function as well. The combination of TRIM and TEXTSPLIT. In that case, you have to select a comma (,) as the only delimiter.
The TRIM Function will allow you to trim down the excess space present in the cell.
So, the formula will look like this:
Example 2: Split Names Into First and Last
Now, look at the example below. We have the first name, middle name, and the last name of the employees in column A. However, we want to separate the first and last name.
Clearly, the delimiter is a space only. But there’s a problem. When we execute the formula =TEXTSPLIT(A2,” “) in cell B2, a #SPILL! Error takes place which says the Spill range isn’t blank (More on that later).
For now, let’s ignore this error. We will omit the Ms. word from the employee’s name too. So, our delimiters are a space and the word Mr. and Ms.
So, our main formula will be:
Here, we placed all the delimiters inside a curly bracket and also used the fourth argument, i.e., ignoring empty cells. Otherwise, we would have gotten ourselves into the Spill Error.
How to Correct a #SPILL Error?
The SPILL error in excel occurs because of obstructing cells. We commonly see this type of error while using the TEXTSPLIT function. Such an error takes place when Excel wants to return a result in a specific cell, but that cell is already occupied by any other data.
Look at the dataset. Normally, Excel should return the result as Jaba Le Noor in three consecutive columns. But the D column is preoccupied with other data. That’s why we are receiving the Spill Error.
Solution: Leave an empty column for the returned result.
Example 3: Separate Text To Columns and Rows Simultaneously
Now, we want to make sure we use both the row and column delimiter. So, let’s look at our new dataset.
We want to ensure that the data is separated into parallel rows and columns.
No problem for Text split. We will use the equals sign as the column delimiter and the comma sign as the row delimiter.
So, the overall formula will be:
FAQ
Q: What does split mean in Excel formula?
A: Split means to separate the contents in a cell into one or more rows and columns. It can be done either with the TEXTSPLIT function or any other functions in Excel.
Q: What is a delimiter in Excel?
A: A delimiter refers to a character or a sequence of characters used to separate the contents of cells or values within a file. The delimited values can be a comma, space, any text, etc.
Conclusion
In this post, we learnt the basics and advanced use of the TEXTSPLIT function. So far, it’s concluded that the Text split function is the ideal split formula in Excel.
You can combine this function with the TRIM, SORT, and even the TEXTJOIN function to get your desired value.
