This article will show how to separate decimal numbers from text in Excel. The following image highlights the purpose of this article.

**Table of Contents**Expand

**How to Separate Decimal Numbers from Text in Excel: 6 Quick Ways**

Look at the dataset below. Here, given some names with their weight. We will now see **6** ways to separate the ages from this dataset.

**1. Use Text to Column Wizard to Separate Decimal Numbers**

The **Text to Column** feature is used to separate a specific section from data in Excel. In this section, we will use this feature to separate the decimal part of the data.

**Steps:**

- Add a column named
**Decimal Number**to show the decimal numbers only.

- Select all the cells of the
**Data**column. - Now, go to the
**Data**tab. - Choose the
**Text to Columns**button from the**Data Tools**group.

- Choose the
**Delimited**option and then press**Next**.

We are forwarded to the **2nd** step of the **Text to Columns Wizard**.

- Choose the
**Comma**option. - Again, press
**Next**.

- Choose the
**General**option. - Now, choose the desired data from the Data preview section.
- Finally, press the
**Finish**button.

- Look at the dataset. 👇

We can see the decimal numbers are separated.

**2. Separate Decimal Numbers Using Flash Fill**

The Flash Fill is an interesting feature of MS Excel. This feature can sense patterns and automatically fills data according to a pattern given by the users. Execute the following steps to separate decimal numbers using this amazing tool.

**Note:**

**Flash Fill** is only available in **Excel 2013** and later versions.

**Steps:**

- Fill the first two cells of the
**Decimal Number**column by putting the decimal numbers from the corresponding**Data**column.

- Now, choose all the cells of the
**Decimal Number**column. - Now, go to the
**Data**tab. - Choose the
**Flash Fill**option from the**Data Tools**group.

You can also call **Flash Fill** by pressing **Ctrl+E**.

- Look at the dataset now.

All the decimal numbers are presented here.

**3. Use a Formula Combining RIGHT, LEN, MIN, and FIND Functions**

In this method, we will use a combination of the **RIGHT, LEN, MIN, and FIND** functions to extract the decimal numbers. First, have a brief intro to these functions.

**The FIND function** returns the starting position of one text string within another text string. **FIND** is a case-sensitive function.

**The MIN function** returns the smallest number in a set of values and ignores logical values and text.

**The LEN function** returns the number of characters of a text string.

**The RIGHT function** returns the specified number of characters from the end of the text string.

Now, follow the steps below.

**Steps:**

- Go to
**Cell C4**and put the following formula on that cell.

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

- Now, press the
**Enter**button.

We get the decimal number of **Cell B4 **here.

- Now, pull the
**Fill Handle**icon.

**Formula Breakdown:**

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

This will search the 1st argument within the second argument.

**Result: {14, 17, 18, 19, 20, 10, 22, 23, 11, 25}**

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

This will return the minimum value from the given argument.

**Result: 10**

**LEN(B4)**

This will return the length of **Cell B4**.

**Result: 15**

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

A subtraction operation is performed here.

**Result: 5**

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

This will return the last **6** strings from **Cell B4**.

**Result: 58.500**

**4. Consolidate LEFT, LEN, SUM, and SUBSTITUTE Functions**

In this section, we will create a formula using the **LEFT**,** LEN, SUM, **and **SUBSTITUTE **functions and learn how to separate decimal numbers from the left.

**The LEFT function **returns the specified number of characters from the start of the text string.

**The SUM function** adds all the numbers from a range of cells.

**The SUBSTITUTE function **replaces existing text with new text in a text string.

In the previous method, we apply a formula to separate the decimal numbers from the right side. In this section, we will extract decimal numbers from the right side. We will apply a formula based on the **LEFT**, **SUM**, **LEN**, and **SUBSTITUTE** functions.

**Steps:**

- Go to
**Cell C4**and write the following formula.

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

- Press the
**Enter**button and pull the**Fill Handle**icon.

Decimal numbers from the left side are extracted here.

**Formula Breakdown:**

**SUBSTITUTE(B4,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)**

This will replace the numerical values from **Cell B4**.

**Result: {58.5, Jessica; 58.500, Jessica; 58.500, Jessica; 58.500, Jessica; 58.500, Jessica; 8.00, Jessica; 58.500, Jessica; 58.500, Jessica; 5.500, Jessica; 58.500, Jessica}**

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

This will find the length of the given reference.

**Result: {13, 15, 15, 15, 15, 13, 15, 15, 14, 15}**

**LEN(B4)**

This will return the length of **Cell B4**.

**Result: 15**

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

A subtraction operation is performed here.

**Result: {2, 0, 0, 0, 0, 2, 0, 0, 1, 0}**

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

A sum operation is performed using the result of subtraction.

**Result: 5**

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

This will return the first 6 strings from **Cell B4**.

**Result: 58.500**

**5. Merge ROW, MID, INDIRECT, and LOOKUP Functions**

If you want to extract decimal numbers from any location, you can use another formula with **ROW**, **MID**, **INDIRECT, **and **LOOKUP **functions.

**The ROW function** returns the row number of a reference.

**The MID function** returns the character from the middle of a text string, given a starting position and length.

**The INDIRECT function** returns the reference specified by a text string.

**The LOOKUP function** looks up a value either from a one-row or one column range or from an array, provided for backward compatibility.

In this section, we will separate the decimal values from any location of the data using a formula. We will consider the below dataset for this purpose.

**Steps:**

- Go to
**Cell C4**and write down the formula below.

`=LOOKUP(9.9E+307,--LEFT(MID(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $B4&"1023456789")),999),ROW(INDIRECT("1:999"))))`

- Press the
**Enter**button and drag the**Fill Handle**icon downwards.

We get the decimal number from any location of the data.

**Formula Breakdown:**

**ROW(INDIRECT(“1:999”))**

This provides numbers from **1 **to **999**.

**MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $B4&”1023456789″))**

This will return the minimum value from the value of the **FIND **function.

**Result: 5**

**LEFT(MID(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},$B4&”1023456789″)),999),ROW(INDIRECT(“1:999”)))**

This will return values from the left side.

**LOOKUP(9.9E+307,–LEFT(MID(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $B4&”1023456789″)),999),ROW(INDIRECT(“1:999”))))**

This will look up the decimal values from **Cell B4 **and return that value.

**Result: 58.500**

**6. Use an Excel VBA Code to Separate Decimal Numbers**

**VBA **is another option to separate decimal numbers from text in Excel. We will **VBA **formula for that purpose.

**Steps:**

- Go to the
**Sheet name**section at the bottom of each sheet. Press the right button of the mouse. - Choose the
**View Code**option from the**Context menu**.

- Choose the
**Module**option from the**Insert**tab of the**VBA**window.

- Now, the
**VBA module**appears. We will write the VBA code in this window.

- Copy and paste the following
**VBA**code on the module and save the code.

```
Function DecimalNumber(str As String, op As Boolean)
Number = ""
Text = ""
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
Number = Number & Mid(str, i, 1)
Else
Text = Text & Mid(str, i, 1)
End If
Next i
If op = True Then
DecimalNumber = Number
Else
DecimalNumber = Text
End If
End Function
```

- Now, go to the dataset and put the following formula on
**Cell C4**.

`=DecimalNumber(B4,1)`

- Press the
**Enter**button and pull the**Fill Handle**icon.

There is a difference while using the **VBA **code. In the result, we get only the numbers but no decimal symbol is shown.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we described how to separate decimal numbers from the text in Excel. I hope this will satisfy your needs.

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