convert #'s to text 3

[YNOTU]

hi all, I was wondering what was the formula for converting $1,250 on cell c3 to One Thousand two hundres and fifty on cell c8

thanks

 

[SkipVought]

Hi,

Use the Advanced Find. There have been several posts recently regarding this.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[jfhewitt]

Go to

http://www.asap-utilities.com/

Great Add-On

 

[YNOTU]

If I only knew what to search for.... I seached for "convert numbers to text" and get way too many results.... I gonna have browse all of them I guess....

thanks for your help.

 

[YNOTU]

thank you jfhewitt but I'm stil looking for the formula tho...

 

[AnotherHiggins]

There is no formula in Excel to do this. The only way to do it is with code - or possibly ASAP utilities free addin - I haven't ever seen that functionality on ASAP, but it very well might be there.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AnotherHiggins]

Ah! I just found it in ASAP.

ASAP Utilities > Numbers > Spell Numbers or Amounts

Thanks, jfhewitt. I've had ASAP for ages but don't use it that often. (Not often enough, it would appear). * for you!

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[YNOTU]

yes, THANK YOU jfhewitt

 

[YNOTU]

anotherhiggins, would it be too much to ask you for an example on how to accomplish this with code??


Thanks

 

[AnotherHiggins]

I've quite honestly never tried to do this before, but it seems like it would be very involved.

I found this thread: thread68-924806 that contains this User Defined Function (posted by PeterMoran):

Function DollarsToText(amount)
'   DollarsToText created 26/4/00
'   Converts Amounts to Text (ex Ins Ms Excel Oct 97)
'   with some additional mods to Australianise it
'
    If amount >= 1000000 Or amount < 0 Then
        DollarsToText = "***VOID***"
        Exit Function
    End If
    
    hth = Int(amount / 100000)
    tth = Int((Int(amount) Mod 100000) / 10000)
    th = Int((Int(amount) Mod 10000) / 1000)
    h = Int((Int(amount) Mod 1000) / 100)
    t = Int((Int(amount) Mod 100) / 10)
    o = Int((Int(amount) * 100 Mod 1000) / 100)
    C = Application.Round(amount - Int(amount), 2) * 100
    
    array1 = Array("One ", "Two ", "Three ", "Four ", "Five ", "Six ", _
        "Seven ", "Eight ", "Nine ")
    array2 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", _
        "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
    array3 = Array("", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", _
        "Seventy ", "Eighty ", "Ninety ")
    
    If hth = 0 Then
        part1 = ""
    Else
        part1 = array1(hth) & "Hundred "
    End If

    If tth = 0 And th < 1 Then
        part1and = ""
    Else: part1and = "and "
    End If
    
    If amount < 100000 Then
        part1and = ""
    End If
    
    If tth = 0 Then
        part2 = ""
    ElseIf tth = 1 Then
        part2 = array2(tth + th)
    Else
        part2 = array3(tth)
    End If
    
    If th = 0 Or tth = 1 Then
        part3 = ""
    Else
        part3 = array1(th)
    End If
    
    If hth = 0 And tth = 0 And th = 0 Then
        part4 = ""
    Else
        part4 = "Thousand "
    End If
    
    If h = 0 Then
        part5 = ""
    Else
        part5 = array1(h) & "Hundred "
    End If

    If t = 0 And o < 1 Then
        partand = ""
    Else: partand = "and "
    End If
    
    If amount < 100 Then
        partand = ""
    End If
        
    If t = 0 Then
        part6 = ""
    ElseIf t = 1 Then
        part6 = array2(t + o)
    Else
        part6 = array3(t)
    End If

    If amount < 1 Then
        part7 = "No "
    ElseIf o = 0 Or t = 1 Then
        part7 = ""
    Else
        part7 = array1(o)
    End If

    DollarsToText = part1 & part1and & part2 & part3 & part4 & part5 & partand & _
        part6 & part7 & "Dollar(s) and " & C & " cents"

End Function

This would be inserted into a module (not the sheet or ThisWorkbook). Then you could use it like [COLOR=blue white]=DollarsToText(A1)[/color]

Hope that helps!

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[YNOTU]

thank you very much anotherhiggins, I can at least get an idea and work with it. Thanks again * 4 u