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

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.

Read More: How to Separate Numbers in Excel Using Formula (5 Ways)

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

Read More: How to Separate Text and Numbers in Excel (4 Easy Ways)

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

Read More: How to Separate Numbers from Text in Excel VBA (3 Methods)

## Conclusion

In this article, we described how to separate decimal numbers from the text in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box. #### Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts  5 Excel Hacks You Never Knew  