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

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

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

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

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

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

`=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**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.