How to Separate Numbers in Excel Using Formula (5 Ways)

Separate Numbers from Text

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 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:

Given dataset for method 1

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.

Enter the formula in Cell C4

Step 2: Copy down the formula up to C13.

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:

Using Left function

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.

Copy down the formula using Left function


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 into another single column named ID.

[ Note: This auto Flash Fill is available only on newer versions of Excel-like 2016 or 2019 versions. ]

Separate Numbers from Text Using Flash Fill

Step 1: Firstly, enter the numbers of cell B4 in cell C4.

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.

Go to the cell below and try to type the number of cell B5 then automatically all the numbers will be shown up.Just press Enter.

Step 3: All the numbers will be filled up automatically.

All the numbers will be filled up automatically

Step 4: This process is also applicable if the numbers on the right side.

Flash fill from right sideFlash fill final output from right side


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.

 Separate Decimal Numbers from Text in Excel

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.

Separate Number Using Delimiters

Step 1: Select the column and go to the Data tab. Then select the Text to Columns section.

Text to columns selection

Step 2: Then it will open a window. Select the Delimited option then click on Next.

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.  

Make sure Space is clicked then press Next button.

Step 4: In this section, you can change the Data Format of the new column. After that press Finish.

Data format

Step 5: Then all the data will show as per our requirement.

Final output after using Delimited


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 user-defined function using VBA code. For this method let’s assume we have a dataset of some input string.

Separate numbers using VBA code

Step 1: First go to the Developer option then click on Visual Basic or use Keyboard shortcut Alt + F11.

How to open VBA window

Step 2: On Microsoft Visual Basic for Applications window first click on the Insert option then select Module.

Process of inserting 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

VBA Code of SplitTextAndNumber function

Code Explanation:

VBA 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. 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.
    2. Here we will iterate the whole string using For loop.
    3. Using the IF condition we are checking if the value is a number or not.
    4. Again, using condition, 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 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. ]

Enter the formula in cell C4

Step 5: Copy down the formula up to C10.

Copy down the formula up to C10


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.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo