Sometimes we may need to separate or get only numbers from any text and number combined input. Without doing this manually Excel provides various ways to separate numbers from any dataset. In this article, I will show several methods to Separate Numbers in Excel by Using formulas in different cases.
Download the Practice Workbook
5 Ways to Separate Numbers in Excel Using Formula
1. Separate Numbers from Text Using Functions
Letâ€™s think we have a dataset of MS office names. We know that for every MS office version there is a year or number to specify the office version like MS Office 2019. Now I will show how to get only the numbers from the given dataset using a formula. In our formula we will need RIGHT, LEN, MIN, FIND functions. Letâ€™s talk about those functions first.
FIND(find_text, within_text, [start_num])
This is one of the most used functions to search for something in Excel. In the first portion of the parameter of the function, we need to pass the value or text which we want to find out. Then secondly we need to pass the range where we will search the value. Lastly, we can specify the starting number or value which is optional.
MIN(number1, [number2], ...)
This function is used to find out the minimum value from any given numeric value. We can pass any number in this functionâ€™s parameter then it will return the minimum value from our given numbers.
LEN (text)
Another function is LEN which takes any text its a parameter and counts the characters in the given input text then returns the number of characters.
RIGHT(text,[num_chars])
This function returns the last character or characters in a text string, based on the number of characters from our specified input text. The text string contains the characters we want to extract which is optional.
LEFT(text, [num_chars])
This is the same as the RIGHT function. All the parameters required value is also the same but here instead of returning the last character, it returns the first character of the input text.
SUBSTITUTE (text, old_text, new_text, [instance])
When we need to replace any text with a new text this function helps with that. The first section takes the text value which is going to be placed. Then we need to pass the current text which is going to be replaced. The third portion takes the text to replace with. Lastly, the instance to replace. If not supplied, all instances are replaced which is optional.
So, our given dataset will be like this:
Step 1: Enter the below formula in cell C4.
=RIGHT(B4,LEN(B4)MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))+1)
Formula Breakdown
As this is a little bit complex formula for better understanding letâ€™s talk about the inner function first
MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))
Using this part, we are searching the first index number of the numbers. Before extracting the number first we need to find out the starting location of the numbers.
After finding the starting number position using RIGHT and LEN functions we are extracting the numbers from the given text.
Step 2: Copy down the formula up to C13.
In this example, the numbers were on the right side of the input text. Now letâ€™s see if the input text is on the left side. For this letâ€™s consider the dataset like this:
Step 1: Enter the formula in the first cell and copy down the formula.
=LEFT(B18,SUM(LEN(B18)LEN(SUBSTITUTE(B18,{"0","1","2","3","4","5","6","7","8","9"},""))))
Formula Breakdown
For getting the numbers of the right side of the given text we need four functions which are LEFT, SUM, LEN, SUBSTITUTE.
SUM(LEN(B18)LEN(SUBSTITUTE(B18,{â€ś0â€ł,â€ť1â€ł,â€ť2â€ł,â€ť3â€ł,â€ť4â€ł,â€ť5â€ł,â€ť6â€ł,â€ť7â€ł,â€ť8â€ł,â€ť9â€ł},â€ťâ€ť)))
Using this part we will just find out the number from the given text.
Then by using the LEFT function we extract the total number length and the total number from the left side of our text.
Read More: How to Separate Numbers in One Cell in Excel (5 Methods)
2. Separate Numbers from Text Using Flash Fill
Assuming that we have a dataset of some person dataset where their ID is attached with their names in a single column. Now our task is to separate the ID numbers from there that column and show them in another single column named ID.
[ Note: This auto Flash Fill is available only on newer versions of Excellike 2016 or 2019 versions. ]
Step 1: Firstly, enter the numbers of cell B4 in cell C4.
Step 2: Go to the cell below and try to type the number of cells B5 then automatically all the numbers will be shown up. Just press Enter.
Step 3: All the numbers will be filled up automatically.
StepÂ 4: This process is also applicable if the numbers on the right side.
Read More: Extract Multiple Numbers from String in Excel (6 Methods)
3. Separate Decimal Numbers from Text in Excel
We can use the same formula for decimal numbers too which was used in the first method. Letâ€™s try this using the same example and formula but here instead of using integers we will use decimal numbers as well.
Step 1: Change the numbers which were used in the previous method and see the changes.
From the above picture it is visible that using this formula:
=RIGHT(B4,LEN(B4)MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))+1)
We can extract not only integer values but also decimal values from any given input string.
4. Separate Number Using Delimiters
Letâ€™s think we have a dataset of some personâ€™s name and ids in a single column. The formation of that column is Name ID. Now I will show how to extract an ID from that column and post it to another column.
Step 1: Select the column and go to the Data tab. Then select the Text to Columns section.
Step 2: Then it will open a window. Select the Delimited option then click on Next.
Step 3: Make sure Space is clicked then press the Next button. You can also see Data Review know how your data will be after pressing on Next button. Â
Step 4: In this section, you can change the Data Format of the new column. After that press Finish.
Step 5: Then all the data will show as per our requirement.
Read More: How to Separate Text and Numbers in Excel (4 Easy Ways)
5. Separate Numbers from Text Using User Defined Function on VBA ( Most Versatile Method)Â
In Excel, we can easily modify functions or make our own functions as per our needs easily using VBA code. In this method, I will show to separate any numbers from given input text with our own userdefined function using VBA code. For this method letâ€™s assume we have a dataset of some input string.
Step 1: First go to the Developer option then click on Visual Basic or use Keyboard shortcut Alt + F11.
Step 2: On Microsoft Visual Basic for Applications window first click on the Insert option then select Module.
Step 3: Enter the following code on the Module.
Code
Function SplitNumAndText(str As String, op As Boolean)
num = ""
txt = ""
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
num = num & Mid(str, i, 1)
Else
txt = txt & Mid(str, i, 1)
End If
Next i
If op = True Then
SplitNumAndText = num
Else
SplitNumAndText = txt
End If
End Function
Code Explanation:
It will be too long if we want to explain every line properly. But here I will explain the main point in the codes.

 1. In the first line we are declaring the function which is SplitNumAndText and it will have two arguments in the parameter where one is a string type and another one is a boolean type.
 Here we will iterate the whole string using For loop.
 Using the IF condition we are checking if the value is a number or not.
 Again, using conditions, it will give output based on number or text. If we pass 0 as the second argument it will extract the Text and if 1 it will extract the Number from the given string.
Step 4: Go to the worksheet and enter the below formula in cell C4.
=SplitNumAndText(B4,1)
[ Note: As we want to print all the numbers thatâ€™s why the 2nd argument is 1. If you want to extract just put 0 in the 2nd argument. ]
Step 5: Copy down the formula up to C10.
Read More: How to Separate Numbers From Text in Excel VBA (3 Methods)
Conclusion
These are the ways to separate numbers from any string in Excel. I have shown all the methods with their respective examples. Also, I have discussed the fundamentals of this function and the most commonly used format codes of this function. If you have any other method of achieving this then please feel free to share it with us.