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.


Download Practice Workbook

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


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.

Use Text to Column Wizard to Separate Decimal Numbers

  • Choose the Delimited option and then press Next.

Use Text to Column Wizard to Separate Decimal Numbers

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

  • Choose the Comma option.
  • Again, press Next.

Use Text to Column Wizard to Separate Decimal Numbers

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

Use Text to Column Wizard to Separate Decimal Numbers

  • Look at the dataset. 👇

We can see the decimal numbers are separated.

Read More: How to Extract Numbers after a Specific Text in Excel (2 Suitable Ways)


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.

Separate Decimal Numbers Using Flash Fill

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

Separate Decimal Numbers Using Flash Fill

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.

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)

Combining RIGHT, LEN, MIN, and FIND Functions to separate decimal numbers

  • Now, press the Enter button.

We get the decimal number of Cell B4 here.

  • Now, pull the Fill Handle icon.

Combining RIGHT, LEN, MIN, and FIND Functions to separate decimal numbers

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

Read More: How to Extract Multiple Numbers from String in Excel (6 Methods)


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)

Consolidate LEFT, LEN, SUM, and SUBSTITUTE Functions to separate decimal numbers

  • 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

Read More: How to Extract Specific Numbers from a Cell in Excel (11 Ways)


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"))))

Merge ROW, MID, INDIRECT, and LOOKUP Functions to separate decimal numbers

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

Excel VBA Code to Separate Decimal Numbers

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

Excel VBA Code to Separate Decimal Numbers

  • 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

 

Excel VBA Code to Separate Decimal Numbers

  • Now, go to the dataset and put the following formula on Cell C4.
=DecimalNumber(B4,1)

Excel VBA Code to Separate Decimal Numbers

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


Further Readings

Alok

Alok

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

Leave a reply

ExcelDemy
Logo