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

Get FREE Advanced Excel Exercises with Solutions!

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.

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.


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.


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


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


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


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

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo