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.


Download Practice Book

Download the practice book here.


5 Methods to Separate Numbers in One Cell in Excel

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. Apply 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 Combine Excel RIGHT, LEN, MIN & FIND Functions

Here, the numbers are positioned at the end of the cell. To separate the numbers from the left 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:
=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)
  • After that, press Enter to see the result.

Apply Formulas to Separate Numbers in One Cell in Excel

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

Apply Formulas to Separate Numbers in One Cell in Excel

🔎 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 Insert Combination of 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:
=LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))
  • After that, hit Enter to see the result.

Apply Formulas to Separate Numbers in One Cell in Excel

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

Apply Formulas to Separate Numbers in One Cell in Excel

🔎 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 the same character and finds the length.

  • 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.

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


2. Split 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 if the texts are at the starting or at the end of a cell. 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.

Split Numbers in One Cell Using ‘Text to Columns’ Feature

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

Split Numbers in One Cell Using ‘Text to Columns’ Feature

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

Split Numbers in One Cell Using ‘Text to Columns’ Feature

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

Split Numbers in One Cell Using ‘Text to Columns’ Feature

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

Split Numbers in One Cell Using ‘Text to Columns’ Feature

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

Split Numbers in One Cell Using ‘Text to Columns’ Feature

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


3. Use 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, if the numbers are at the start or at the end of a text string. Follow the steps below to know the process.

STEPS:

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

Use Excel Flash Fill to Separate Numbers in One Cell

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

Use Excel Flash Fill to Separate Numbers in One Cell

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

Use Excel Flash Fill to Separate Numbers in One Cell

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

Use Excel Flash Fill to Separate Numbers in One Cell

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


4. Decimal Numbers Separation in One Cell with Excel Formulas

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:
=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)
  • After that, hit Enter to see the result.

Decimal Numbers Separation in One Cell with Excel Formulas

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

Decimal Numbers Separation in One Cell with Excel Formulas

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

=LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))

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


5. Create a User-Defined Function to Split Numbers in One Cell with Excel VBA

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:
=SeparateNumbers(B5,TRUE)

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

Note: To separate texts, use the formula below:

=SeparateNumbers(B5,TRUE)

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,2,3 and 4 can be used if the numbers are at the starting 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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo