Separate Decimal Numbers from Text in Excel (6 Quick Ways)

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

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