How do I extract the decimal places from a number in excel?

[Marli]

I need to produce a spreadsheet that rights the example: 1,245.89 as one two
four five 89. I've tried the left and right formulaes and the len one but the
numbers will change in length and may have zero decimals at the end too. Not
sure how to set this up now. Need the numbers in seperate cells, currently
have the following formula for the second number and so forth:
RIGHT(LEFT(B3,2),1)

Any help would be much appreciated.

Many thanks.

 

[Mike H]

Hi,

Do the two formula do what you want

=INT(B3)
=MOD(B3,1)

Mike

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Function NumToText(strData As Variant) As String
Dim arrText As Variant
arrText = Array("Zero", "One", "Two", "Three", _
"Four", "Five", "Six", "Seven", "Eight", "Nine")
For intPos = 1 To InStr(strData & ".", ".")
If IsNumeric(Mid(strData, intPos, 1)) Then
NumToText = NumToText & " " & arrText(Mid(strData, intPos, 1))
End If
Next
NumToText = Mid(NumToText, 2) & " " & _
Mid(strData, InStr(strData & ".", ".") + 1)
End Function

 

[Ashish Mathur]

Hi,

you may try this in cell F24. C24 has 1245.89. Copy this formula to the
right.

=MID($C$24,COLUMNS($F23:F23),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com