How to Separate Numbers in One Cell in Excel (5 Methods)

Sometimes, our dataset contains numbers, texts, and different delimiters in one cell. In that case, we need to separate numbers in one cell in Excel. We have several methods to separate numbers. We can use different formulas, VBA, or some Excel options to split numbers in one cell. In this article, we will demonstrate 5 methods. So, without further ado, let’s start the discussion.

To explain these methods, we will use a dataset that contains the Department and the ID Number of some employees. But, the problem is, The Name and ID Numbers are together in one cell. Here, we will separate the ID Numbers.

1. Applying Formulas to Separate Numbers in One Cell in Excel

In the first method, we will apply a formula to separate numbers in one cell in Excel. In Excel, numbers may be positioned on the right side or left side in one cell. We will show both cases in the following sub-methods.

1.1 Combining Excel RIGHT, LEN, MIN & FIND Functions

Here, the numbers are positioned at the end of the cell. To separate the numbers from the right side, we need to use the RIGHT function. Inside the RIGHT function, we will include the LEN, MIN, and FIND functions. The LEN function counts the number of characters in a given text string. To return the smallest number in a set of values ignoring logical values and texts, we need to use the MIN function. And the FIND function returns the starting position of a text string.

Let’s follow the steps below to learn the method.

Steps:

• In the first place, create an extra column named ID Number like the picture below.
• Secondly, select Cell C5 and type the formula:

• After that, press Enter to see the result.

• Finally, drag the Fill Handle down to see results in the rest of the cells.

🔎 How Does the Formula Work?

• MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″)

This part of the formula finds the starting location of the numbers. Here, the starting location is 6 from the left.

• LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″))+1

The above formula subtracts the length of Cell B5 from the starting location of the numbers and then adds 1. Here, the length of Cell B5 is 8 and the starting location of the numbers is 6. So, the output of this formula is 3.

• RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″))+1)

Here, this formula will act like RIGHT(B5,3) after the execution of the above two commands. This means, the RIGHT function will extract the last 3 numbers from the end of the text.

Read More: Extract Only Numbers from Excel Cell (6 Useful Methods)

1.2 Joining LEFT, SUM, LEN & SUBSTITUTE Functions

In this case, we will separate the numbers from the starting point of a cell. To do so, we need to use the LEFT, SUM, LEN, and SUBSTITUTE functions together. The LEFT function returns specific numbers of characters from the starting of a text string and the SUBSTITUTE function is used to replace an existing text with a new text. Here, the SUM function is used to calculate the number of characters from the start. Let’s pay attention to the steps below to know more.

Steps:

• First of all, create a helper column and type the formula in Cell C5:

• After that, hit Enter to see the result.

• In the end, drag the Fill Handle down to see results in all cells.

🔎 How Does the Formula Work?

• LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)

This part of the formula substitutes the numbers with empty and finds the lengths.

• SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)))

The above formula sums up the subtraction of the length of Cell B5 and the numbers.

• LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””))))

Here, this formula will act like LEFT(B5,3) after the execution of the above two commands. This means the LEFT function will extract the first 3 numbers from the start of the text.

2. Splitting Numbers in One Cell Using Text to Columns Feature

Another way to separate numbers in one cell is to use the ‘Text to Columns’ feature in excel. You can use this method for any position of the numbers in a cell but if there are multiple delimiters then the delimiters must have to be the same. Because this tool can’t handle multiple types of delimiters at a time. Here, we will use the same dataset again. So, without any delay, let’s jump to the steps of the method.

Steps:

• Firstly, select the cells that contain numbers and texts together.

• In the second step, go to the Data tab and select Text to Columns.

• After that, select Delimited from the Text to Columns Wizard Step 1.
• Then, select Next.

• In the following, select Space and click Next in the Text to Columns Wizard Step 2.

• After clicking Next, select General in the Text to Columns Wizard Step 3 and then, click Finish.

• Finally, you will see the numbers in one cell.

Read More: How to Separate Text and Numbers in Excel (4 Easy Ways)

3. Using Excel Flash Fill to Separate Numbers in One Cell

The use of Excel Flash Fill is another way to autofill a pattern very easily. Again, we can use this method for any position of the numbers in a cell, because Flash Fill can detect the pattern for any position. Follow the steps below to know the process.

Steps:

• In the beginning, type the number part in Cell C5 like the picture below.

• Secondly, select the cells that you want to fill with numbers.

• After that, go to the Home tab, select Fill, and then, Flash Fill from the drop-down menu.

• As a result, you will see, that the numbers are separated into one cell.

Read More: How to Extract Multiple Numbers from String in Excel (6 Methods)

4. Applying Excel Formulas with Decimal Numbers Separation in One Cell

In the above methods, we have only discussed the integer numbers. But we can also separate decimal numbers in one cell in Excel. Here, we will apply the formulas of Method-1. So, let’s follow the steps below to separate decimal numbers.

Steps:

• Firstly, select Cell C5 and type the formula:

• After that, hit Enter to see the result.

• In the end, use the Fill Handle to see results like the picture below.

Note: If you have numbers at the starting of the cell, then, use the formula below:

The breakdown of both formulas is discussed in Method-1.

5. Creating User-Defined Function with VBA to Split Numbers in One Cell

If you have a mixture of texts, numbers, and delimiters, you can create a user-defined function with VBA to separate numbers in one cell. This method works on every type of mixture of numbers and texts. Look at the dataset to know about the mixture. We will separate the numbers in every case.

Let’s follow the steps to know the technique.

Steps:

• In the first place, select Visual Basic from the Developer tab in the ribbon. It will open the Visual Basic window.

• Secondly, select Insert and then Module in the Visual Basic window.

• After that, type the code in the Module window:

Function SeparateNumbers(xWrkRg As Range, xIsNum As Boolean) As String
Dim ilen As Long
Dim istr As String
ilen = VBA.Len(xWrkRg.Value)
For i = 1 To ilen
    istr = VBA.Mid(xWrkRg.Value, i, 1)
    If ((VBA.IsNumeric(istr) And xIsNum) _
    Or (Not (VBA.IsNumeric(istr)) And Not (xIsNum))) Then
        SeparateNumbers = SeparateNumbers + istr
    End If
Next
End Function

Here, we have created a user-defined function. The name of the function is SeparatedNumbers and it has two compulsory arguments.

• Press Ctrl + S to save the code and then, close the Visual Basic window.
• Now, select Cell C5 and type the formula:

• Finally, hit Enter and drag the Fill Handle down to see the results.

Note: To separate texts, use the formula below:

Read More: How to Separate Numbers From Text in Excel VBA (3 Methods)

Things to Remember

We need to remember certain things when we try to separate numbers in one cell in Excel.

• Method-1 and 4 can be used if the numbers are at the start or at the end of a given text string.
• To separate numbers from a mixture, use Method-5.

Conclusion

We have demonstrated 5 easy methods to Separate Numbers in Excel in One Cell. We have used different methods and also discussed a method with VBA. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Related Articles

• How to Extract Numbers after a Specific Text in Excel (2 Suitable Ways)
• Extract Specific Numbers from a Cell in Excel (11 Ways)
• VBA to Remove Numbers from String in Excel (4 Methods)