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

We will use a dataset that contains the Department and the ID Number of some employees, with the Name and ID Numbers together in one cell.

Method 1 – Applying Formulas to Separate Numbers in One Cell in Excel

1.1 Combining Excel RIGHT, LEN, MIN & FIND Functions

The numbers in the example are positioned at the end of the cell. To separate them, 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.

Steps:

Create an extra column named ID Number for column C like the picture below.
Select Cell C5 and copy this formula:

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

Press Enter to see the result.

Drag the Fill Handle down to see results in the rest of the column.

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 characters from the end of the text.

1.2 Joining LEFT, SUM, LEN & SUBSTITUTE Functions

Use this method if the numbers are at the start of the text.

The LEFT function returns specific numbers of characters from the start 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.

Steps:

Create a helper column in C 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"},""))))

Hit Enter to see the result.

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 an empty character and finds the resulting string’s 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.

Method 2 – Splitting Numbers in One Cell Using Text to Columns Feature

Steps:

Select the cells you want to manipulate.

Go to the Data tab and select Text to Columns.

Select Delimited from the Text to Columns Wizard Step 1.
Go to Next.

Select Space and click Next in the Text to Columns Wizard Step 2.

Select General in the Text to Columns Wizard Step 3 and click Finish.

Finally, you will see the numbers in one cell.

If the text and numbers are separate by a comma, period, or semicolon instead of a space, choose that option in Text to Columns Wizard Step 2, or input another delimiter in the Other box.

Method 3 – Using Excel Flash Fill to Separate Numbers in One Cell

Steps:

Type the number part in Cell C5 like the picture below.

Select the cells that you want to fill with numbers.

Go to the Home tab, select Fill, then choose Flash Fill from the drop-down menu.

The numbers will be separated into one cell.

Method 4 – Applying Excel Formulas with Decimal Numbers Separation in One Cell

Steps:

Create a helper column C.
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)

Hit Enter to see the result.

Use the Fill Handle to see results like the picture below.

If you have numbers at the start of the cell instead, 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.

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

Consider the mixed dataset below. Methods 1 and 2 wouldn’t work here, and Method 3 might fail.

Steps:

Select Visual Basic from the Developer tab in the ribbon. It will open the Visual Basic window.

Select Insert followed by Module.

Paste 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