Split text and numbers in a cell in Excel
Supposing a cell data is mixed with text and numbers, how can you split them into separate column cells? This tutorial is going to show you the detailed steps to get it down with formulas.
How to split text and numbers in a cell in Excel?
In this circumstance, you need to apply three formulas to split text and numbers in a cell into separate column cells.
Firstly, you need to locate the position of the first number;
Secondly, extract text from cell;
Finally, get the numbers.
Generic formulas
Generic formula for locating the position of the first number
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
Generic formula for getting text
=LEFT(A1,position-1)
Generic formula for getting numbers
=RIGHT(A1,LEN(A1)-position+1)
Arguments of above formulas
A1: It represents the cell which contains the text and numbers you want to split;
Position: The position of the first number in A1.
How to use these formulas?
Supposing you have a text string list as the below screenshot shown, you can do as follows to split the text and numbers into separate cells.
Firstly, return the starting position of the numbers in the text string
1. Select a blank cell to output the position. In this case, I select C3.
2. Enter the below formula into it and press the Enter key. Select the result cell, drag the Fill Handle down to apply the formula to other cell until all positions you need are shown.
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789"))
Secondly, get text
1. Select a blank cell to locate the text. Here I select D3.
2. Enter the below formula into it and press the Enter key. Select this result cell, drag the Fill Handle down to get all texts of the below cells.
=LEFT(B3,C3-1)
Finally, get numbers
1. Select a blank cell such as E3 to locate the numbers.
2. Enter the below formula into it and press the Enter key. Select this result cell, drag the Fill Handle down to get all numbers of the below cells.
=RIGHT(B3,LEN(B3)-C3+1)
How these formulas work?
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789"))
1. FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789")
- B3&"0123456789" returns the result as: ana varela 240.150123456789.
- FIND({0,1,2,3,4,5,6,7,8,9}, "ana varela 240.150123456789"): The Find function finds the position of "0-9" digits in ana varela 240.150123456789 and returns the result as {14,16,12,21,13,17,24,25,26,27}.
The result {14,16,12,21,13,17,24,25,26,27} means that the number 0 is in the 14th position in ana varela 240.150123456789, number 1 is in the 16 position in ana varela 240.150123456789…
2. =MIN({14,16,12,21,13,17,24,25,26,27}): The MIN function gets the minimum value of the array. Here the result is 12.
=LEFT(B3,C3-1)
LEFT(ana varela 240.15,12-1) which means that the LEFT function extracts the first 11 characters from the left side of the text string "ana varela 240.15". The final result is ana varela.
=RIGHT(B3,LEN(B3)-C3+1)
1. LEN(B3)-C3+1:
- The LEN function calculates the total length of the text string (ana varela 240.150123456789) in B3 and returns the result as 17.
- 17-12+1: Here gets the total length of numbers in ana varela 240.15. The result is 6.
2. =RIGHT(B3,6): The RIGHT function extracts 6 number of characters from the right side of ana varela 240.15. And the final result is 240.15.
Related functions
Excel MID function
The Excel MID function is used to find and return a specific number of characters from the middle of given text string.
Excel FIND function
The Excel FIND function is used to find a string within another string, and returns the starting position of the string inside another one.
Excel LEFT function
The Excel LEFT function extracts the given number of characters from the left side of a supplied string.
Excel RIGHT function
The Excel RIGHT function extracts a specific number of characters from the right side of the text string.
Excel LEN function
The Excel LEN function returns the number of characters in a text string.
Related formulas
Split dimensions into two parts in Excel
This article explains how to use formula to split dimensions in a cell into two parts without units (individual length and width).
Separate numbers from units of measurement
This article explains how to use formula to separate numbers from unites of measurement.
Split Text String At Specific Character In A Cell In Excel
This tutorial explains how to split texts string at a specific character in a cell with formulas in detailed steps.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
