Convert Number To Decimal

jgthtrotih · Sep 3, 2009

<o

></o

>Hi All!
<o

> </o

>
I need a formula to take the numbers listed in cells D4:D50
<o

> </o

>
If below 1,000,000 than just keep that number value
<o

> </o

>
EXAMPLE DATA
<o

> </o

>
If the Ten Thousands place is 5 or below leave the same if it is higher round up. (use this concept for ALL PLACE VALUES

<o> </o>
1,500,000 to 1.5 million (if the rest are zeros)
<o

> </o

>
4,000,000 to 4 million
<o

> </o

>
1,564,535 than to 1.57 million
<o

> </o

>
1,545,535 than to 1.54 million
<o

> </o

>
1,555,000 than to 1.55 million
<o

> </o

>
Same concept for the rest as the Millions (move the rounded place up as needed)
<o

> </o

>
So for Billions it would be 10 Millions place (to round form) I think lol
<o

> </o

>
1,000,000,000 to 1 Billion
<o

> </o

>
1,500,000,000 to 1.5 Billion
<o

> </o

>
1,560,000,000 to 1.57 Billion
<o

> </o

>
1,578,000,000 to 1.58 Billion
<o

> </o

>
1,700,000,000 to 1.7 Billion
<o

> </o

>
I would like this to continue all the way up to place value 999 QUADILLION! (if possible) if the value can go bigger that would be great!

<o

> </o

>
<o

></o

>Return the answers in E4:E50

i think i explained my problem well but if not let me know!
<o

></o

>

sanrv1f · Sep 4, 2009

ooops, didn't tested that,

this one works

Excel Workbook

C

D

1

999999

2

15000000

15 Mio

3

1500000

1.5 Mio

4

15000000000

15 Bio

5

1000000000000

1 Tri

6

1500000000000000

1.5 Qua

7

1200000000000000000

1.2 Qui

8

1200000000000000000000

1.2E+21

Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

jgthtrotih · Sep 4, 2009

does work thanks.

jgthtrotih · Sep 4, 2009

Can VBA/UDF do what i want?

jgthtrotih · Sep 5, 2009

can i please get some help on this?

jgthtrotih · Sep 6, 2009

anyone?

phxsportz · Sep 6, 2009

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:220px;" /><col style="width:193px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; text-align:right; ">1,000,000.00</td><td style="background-color:#ccffcc; font-weight:bold; font-family:Verdana; text-align:center; ">1.00 Million</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Verdana; text-align:right; ">1,000,000,000.00</td><td style="background-color:#ccffcc; font-weight:bold; font-family:Verdana; text-align:center; ">1.00 Billion</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Verdana; text-align:right; ">1,000,000,000,000.00</td><td style="background-color:#ccffcc; font-weight:bold; font-family:Verdana; text-align:center; ">1.00 Trillion</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Verdana; text-align:right; ">1,000,000,000,000,000.00</td><td style="background-color:#ccffcc; font-weight:bold; font-family:Verdana; text-align:center; ">1.00 Quadrillion</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=bucks(A1)</td></tr><tr><td >B2</td></tr></table>

Placed in Module 1

Code:

Function bucks(arg1 As Variant)
    Select Case True
    Case arg1 < 1000000
        bucks = arg1
    Case arg1 < 1000000000
        bucks = Format(arg1 / 1000000, "###.00 ") & "Million"
    Case arg1 < 1000000000000#
        bucks = Format(arg1 / 1000000000, "###.00 ") & "Billion"
    Case arg1 < 1E+15
        bucks = Format(arg1 / 1000000000000#, "###.00 ") & "Trillion"
    Case arg1 >= 1E+15
        bucks = Format(arg1 / 1E+15, "###.00 ") & "Quadrillion"
    End Select
End Function

jgthtrotih · Sep 6, 2009

instead of saying 1000 it would say 1 Thousand

2000- 2 Thousand

1100- would say 11 Hundred

2500- 25 Hundred

4500- 45 Hundred

10000- would say 10 Thousand
20000- 20 Thousand

100000- would say 100 Thounsand
200000- 200 Thousand

can Code be changed to do this. added with what it already does?

Thanks

GTO · Sep 7, 2009

Greetings,

Hopefully it should look something like this:

Excel Workbook

A

B

1

Cells formatted as number

=IsNumericToText(TEXT(A2,"#.00"))

2

999,499.00

999 Thousand

3

999,500.00

1 Million

4

1,564,000.00

1.56 Million

5

534,634,134,202,710,000.00

534.63 Quadrillion

6

4,949.00

49 Hundred

7

5,025.00

5 Thousand

8

1,267,049,123,030,980,000.00

1.27 Quintillion

9

432,123,600,132,090,000,000,000,000.00

432.12 Septillion

Num2VariableText

Excel 2000

Cell Formulas
Range	Formula
B2	=IsNumericToText(TEXT(A2,"#.00"))

In a Standard Module:

Code:

Option Explicit
    
Const Septillion As Double = 1E+24
Const Sextillion As Double = 1E+21
Const Quintillion As Double = 1E+18
Const Quadrillion As Double = 1E+15
Const Trillion As Double = 1000000000000#
Const Billion As Double = 1000000000
Const Million As Double = 1000000
Const Thousand As Double = 1000
Const Hundred As Double = 100
    
Function IsNumericToText(ByVal VarInput As Variant) As String
Dim _
dblTemp         As Double, _
dblRound        As Double, _
dblThousands    As Double
    
    If Not IsNumeric(VarInput) Then
        IsNumericToText = "Bad Val"
        Exit Function
    End If
    VarInput = CleanString(VarInput)
    dblTemp = CDbl(VarInput)
RoundThousandSpot:
    Select Case dblTemp
    Case Is >= Septillion
        dblRound = dblTemp / 10 ^ 24
        IsNumericToText = CStr(Round(dblRound, 2)) & " Septillion"
    Case Is >= Sextillion
        dblRound = dblTemp / 10 ^ 21
        IsNumericToText = CStr(Round(dblRound, 2)) & " Sextillion"
    Case Is >= Quintillion
        dblRound = dblTemp / 10 ^ 18
        IsNumericToText = CStr(Round(dblRound, 2)) & " Quintillion"
    Case Is >= Quadrillion
        dblRound = dblTemp / 10 ^ 15
        IsNumericToText = CStr(Round(dblRound, 2)) & " Quadrillion"
    Case Is >= Trillion
        dblRound = dblTemp / 10 ^ 12
        IsNumericToText = CStr(Round(dblRound, 2)) & " Trillion"
    Case Is >= Billion
        dblRound = dblTemp / 10 ^ 9
        IsNumericToText = CStr(Round(dblRound, 2)) & " Billion"
    Case Is >= Million
        dblRound = dblTemp / 10 ^ 6
        IsNumericToText = CStr(Round(dblRound, 2)) & " Million"
    Case Is >= Thousand
        dblRound = dblTemp / 10 ^ 3
        If Round(dblRound, 0) = 1000 Then
            dblTemp = Million
            GoTo RoundThousandSpot
        End If
        
        If dblRound >= 10 Then
            IsNumericToText = CStr(Round(dblRound, 0)) & " Thousand"
        Else
            If Round(dblRound, 1) * 10 Mod 10 = 0 Then
                IsNumericToText = CStr(Round(dblRound, 0)) & " Thousand"
            Else
                IsNumericToText = CStr(Round(dblRound, 1) * 10) & " Hundred"
            End If
        End If
    Case Else
        IsNumericToText = "Val too low"
        Exit Function
    End Select
End Function
    
Function CleanString(NumStr As Variant) As String
Dim i As Long, tmp As Variant
    For i = Len(NumStr) To 1 Step -1
        If Mid(NumStr, i, 1) Like "[0-9.]" Then
            tmp = Mid(NumStr, i, 1) & tmp
        End If
    Next
    CleanString = tmp
End Function
Sub Test()
MsgBox _
"Input:" & String(4, vbTab) & "Results:" & String(3, vbTab) & String(2, vbCrLf) & _
"900" & String(4, vbTab) & IsNumericToText(900) & vbCrLf & _
"1000" & String(4, vbTab) & IsNumericToText(1000) & vbCrLf & _
"1100" & String(4, vbTab) & IsNumericToText(1100) & vbCrLf & _
"2000" & String(4, vbTab) & IsNumericToText(2000) & vbCrLf & _
"2300" & String(4, vbTab) & IsNumericToText(2300) & vbCrLf & _
"9900" & String(4, vbTab) & IsNumericToText(9900) & vbCrLf & _
"10000" & String(4, vbTab) & IsNumericToText(10000) & vbCrLf & _
"99000" & String(4, vbTab) & IsNumericToText(99000) & vbCrLf & _
"100000" & String(4, vbTab) & IsNumericToText(100000) & vbCrLf & _
"1000000" & String(4, vbTab) & IsNumericToText(1000000) & vbCrLf & _
"11000000" & String(3, vbTab) & IsNumericToText(11000000) & vbCrLf & _
"111574000" & String(3, vbTab) & IsNumericToText(111574000) & vbCrLf & _
"2111000000" & String(3, vbTab) & IsNumericToText(2111000000) & vbCrLf & _
"22111000000" & String(3, vbTab) & IsNumericToText(22111000000#) & vbCrLf & _
"222111000000" & String(3, vbTab) & IsNumericToText(222111000000#) & vbCrLf & _
"6000000000000" & String(3, vbTab) & IsNumericToText(6000000000000#) & vbCrLf & _
"7000000000000000" & String(2, vbTab) & IsNumericToText("7000000000000000") & vbCrLf & _
"8000000000000000000" & String(2, vbTab) & IsNumericToText("8000000000000000000") & vbCrLf & _
"9000000000000000000000" & String(2, vbTab) & IsNumericToText("9000000000000000000000") & vbCrLf & _
"3214000000000000000000000" & String(1, vbTab) & IsNumericToText("3214000000000000000000000"), 0, ""
    '// If grabbing a value from a cell in vba, I think you'll want the cell formatted for  //
    '// a number, and use the .Text property rather than the .Value                         //
MsgBox IsNumericToText(ThisWorkbook.Worksheets("Num2VariableText").Range("A9").Text), 0, ""
' Other links on the subject:
'  Also check
'http://www.xldynamic.com/source/xld.xlFAQ0004.html
'http://www.thecodecage.com/forumz/excel-miscellaneous/
'   106683-convert-number-value-words-numerical-value.html
'http://www.ozgrid.com/VBA/ValueToWords.htm
'http://support.microsoft.com/kb/213360
'http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q213360
'http://www.sulprobil.com/html/spellnumber.html
'http://www.contextures.com/excelfilesRon.html
'http://www.ozgrid.com/VBA/CurrencyToWords.htm
End Sub

Hope that helps,

Mark

jgthtrotih · Sep 7, 2009

Works Excellent just what i was looking for Thank You Oh So Very Much!

when 20 sextillion is entered..when i click the cell normally it shows (20 sextillion)
but when i click on it shows (2E+22) what does that mean? how to fix this?

Thank You Again For Your Time And Effort!

phxsportz · Sep 7, 2009

Code:

Function bucks(arg1 As Variant)
    Select Case True
    Case arg1 < 100
        bucks = arg1
    Case arg1 < 1000
        bucks = Replace(Format(arg1 / 100, "###.00 ") & "Hundred", ".00", "")
    Case arg1 < 1000000
        bucks = Replace(Format(arg1 / 1000, "###.00 ") & "Thousand", ".00", "")
    Case arg1 < 1000000000
        bucks = Replace(Format(arg1 / 1000000, "###.00 ") & "Million", ".00", "")
    Case arg1 < 1000000000000#
        bucks = Replace(Format(arg1 / 1000000000, "###.00 ") & "Billion", ".00", "")
    Case arg1 < 1E+15
        bucks = Replace(Format(arg1 / 1000000000000#, "###.00 ") & "Trillion", ".00", "")
    Case arg1 < 1E+18
        bucks = Replace(Format(arg1 / 1E+15, "###.00 ") & "Quadrillion", ".00", "")
    Case arg1 < 1E+21
        bucks = Replace(Format(arg1 / 1E+18, "###.00 ") & "Quintillion", ".00", "")
    Case arg1 >= 1E+21
        bucks = Replace(Format(arg1 / 1E+21, "###.00 ") & "Sextillion", ".00", "")
    End Select
End Function

Convert Number To Decimal

jgthtrotih

Well-known Member

sanrv1f

MrExcel MVP

Excel Facts

jgthtrotih

Well-known Member

jgthtrotih

Well-known Member

jgthtrotih

Well-known Member

jgthtrotih

Well-known Member

phxsportz

Well-known Member

jgthtrotih

Well-known Member

GTO

MrExcel MVP

jgthtrotih

Well-known Member

phxsportz

Well-known Member

Similar threads

Forum statistics

Share this page

Convert Number To Decimal

Well-known Member

MrExcel MVP

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock