How to separate text and numbers in Excel. When working on spreadsheets, you may need to split the text string in a cell into several smaller text strings. The word separation in Excel largely depends on the delimiter in the text string. In this article, Ngolongtech will show you two ways to separate text in Excel.
Table of Contents
Why do we need to separate text and numbers in Excel
Reports and spreadsheets may include numbers and text for many reasons. Separating this content serves many purposes and can increase your ability to organize information. Here are some reasons you may separate numbers from text in an Excel document:
-
Easier to filter: Placing text and numbers in separate columns or rows in Excel allows for easier filtering, sorting and searching. You can use functions and tools to sort columns that contain only numerical data and to filter columns with text.
-
Increased readability: Having separate columns, rows or cells for text and numbers can make a spreadsheet easier to read and scan. Creating clear, labeled and separated areas for each type of data allows the viewer to identify where to find and place each piece of information efficiently.
How to separate text and numbers in Excel (2 ways)
Use the function to separate text in Excel
There are several Excel functions that can be used to split and analyze the text in a data cell as follows:
LEFT function to extract characters from the left side of the text. The syntax of this function is:
= LEFT ( Text, [Number] )
In there:
- Text is the text string that you want to extract. Alternatively, it can also be a valid cell reference.
- Number is an optional argument that tells the LEFT function how many characters you want to extract from the text string. So this argument must be greater than or equal to 0.
The RIGHT function extracts characters from the right side of the text. This function has the following syntax:
= RIGHT ( Text, [Number] )
In that, the parameters have the same meaning as for the LEFT function described above.
The Find function has the function of finding characters in a text string and the return value is the first position of the first character in the search string. So this function can be used to determine the position of a separator character. The syntax of the function is:
= FIND(SubText, Text, [Start])
In there:
- SubText is a string of text that you want to search.
- Text is the text string to be searched.
- Start is an optional argument that tells the function where to start searching.
The LEN function returns the length of the text string, including the space character. The syntax of the function is:
= LEN ( Text )
Where, Text is the text string that you want to specify the number of characters.
After you understand the function of the functions, let’s combine them together to separate text in Excel. The steps are as follows:
Step 1: We will use the first row (B3) of the sample spreadsheet as an example. First, you combine the LEFT and FIND functions with the following formula:
= LEFT ( B3, FIND( “,”, B3 ) – 1 )
Formula analysis: In this function, we use the FIND function to get the position of the first delimiter character. The function will return the value 18. Then we continue to use the LEFT function to extract the first element of the text string. Using the ” -1 ” argument makes the LEFT function ignore the delimiter when extracting data. You will then see that the ” Tabbie O’Hallagan ” data has been extracted to cell C3 as a result.
Step 2: In cell D3, you can use the RIGHT function in combination with the LEN and FIND functions with the following formula:
= RIGHT ( B3, LEN ( B3 ) – FIND ( “,”, B3 ) )
Formula parsing: Getting the next elements of a text string is more complicated. You need to remove the first element from the text using the above formula. This formula takes the length of the original text, finds the position of the first delimiter, and then calculates the number of characters remaining in the text string after that delimiter. The RIGHT function then truncates all characters from the right of the text string after the first delimiter. So the result you get will be 056 Dennis Park, Greda, Croatia, 44273 .
Now you can use FIND to locate the next delimiter and the LEFT function to extract the next element with the same steps as above.
Use the Flash Fill tool to separate text in Excel
Flash Fill allows you to include an example of how you want to split your data. Then it will learn and automatically divide the data in the same way as you in other data cells. Therefore, this is considered as a smart tool integrated in Excel.
To split text with the Flash Fill tool, select the first cell that you want to split the data inside and activate the Flash Fill tool (Ctrl + E). Excel will populate the remaining rows based on your example.
In the sample spreadsheet in the article, you will need to enter “Name” in cell C2 and “Tabbie O’Hallagan” in cell C3 . Press Ctrl + E , the data will be split similarly to automatically fill the range from C4:C12 . The rest of the text can do the same.
How to separate numbers from text in excel YouTube
Ngolongtech hopes that through this article, you will successfully perform word separation in Excel.
