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

This article will show how to separate decimal numbers from text in Excel, like in the following image.

Method 1 – Using Text to Column Wizard

The Text to Column feature is used to separate a specific section from data in Excel.

Steps:

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

Select all the cells of the Data column.
Go to the Data tab.
Choose the Text to Columns button from the Data Tools group.

Choose the Delimited option and then press Next.

Choose the Comma option.
Press Next.

Choose the General option.
Choose the desired data from the Data preview section.
Click the Finish button.

The decimal numbers are separated.

Method 2 – Using Flash Fill

The Flash Fill feature can sense patterns and automatically fill data according to a given pattern.

Note:

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

Steps:

Fill the first two cells of the Decimal Number column by manually entering the decimal numbers from the corresponding Data column.

Choose all the cells of the Decimal Number column.
Go to the Data tab.
Choose the Flash Fill option from the Data Tools group, or use the keyboard shortcut Ctrl+E.

All the decimal numbers are separated.

Method 3 – Using a Formula Combining RIGHT, LEN, MIN, and FIND Functions

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

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.

Steps:

In cell C4 enter the following formula:

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

Press Enter.

The decimal number in cell B4 is returned.

Drag the Fill Handle icon down to copy the formula to the rest of the cells.

Formula Breakdown:

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

This will search for 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 characters from cell B4.
Result: 58.500

Method 4 – Combining LEFT, LEN, SUM, and SUBSTITUTE Functions

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 applied a formula to separate the decimal numbers from the right side. Now we will extract decimal numbers from the left side.

Steps:

In cell C4 enter the following formula:

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

Press Enter and drag the Fill Handle icon.

Decimal numbers are extracted from the left side.

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 characters from cell B4.
Result: 58.500

Method 5 – Combining ROW, MID, INDIRECT, and LOOKUP Functions

To extract decimal numbers from any location, use the following formula combining the 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.

To illustrate this method, we will use the modified dataset below.

Steps:

In cell C4 enter 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 Enter and drag the Fill Handle icon down.

The decimal numbers from any location are returned.

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 results 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 those values.
Result: 58.500

Method 6 – Using VBA Code

Steps:

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

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

The VBA module appears. We will write the VBA code in this window.

Copy and paste the following VBA code into 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