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

- Press
**Ctrl**+**S**to save the code and close the**Visual Basic**window. - Select
**Cell C5**and type the formula:

`=SeparateNumbers(B5,TRUE)`

- Hit
**Enter**and drag the**Fill Handle**down to see the results.

To separate text, use the formula below:

`=SeparateNumbers(B5,FALSE)`

**Download Practice Book**

Download the practice book here.

**<< Go Back to Separate Numbers Text | Split | Learn Excel**