Results 1 to 5 of 5

convert a number to words in excel 2007

This is a discussion on convert a number to words in excel 2007 within the Excel Questions forums, part of the Question Forums category; Required VBA code for converting a number to word format. Format 1 : US Dollars One million three hundred sixty ...

  1. #1
    New Member excell2007's Avatar
    Join Date
    Sep 2013
    Posts
    2

    Exclamation convert a number to words in excel 2007

    Required VBA code for converting a number to word format.
    Format 1 : US Dollars One million three hundred sixty six thousand & five hundred only.
    Format 2 : Saudi Riyals One million three hundred sixty six thousand & five hundred only.

    Kindly Above two format VBA code provided.

    Thanks & regards,
    shafique ahmed

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,330

    Default Re: convert a number to words in excel 2007

    Look here:

    How to convert a numeric value into English words in Excel

    Change the currency description to suit.
    Microsoft MVP - Excel

  3. #3
    apo
    apo is offline
    Board Regular
    Join Date
    Nov 2008
    Location
    Wirrimbi, NSW, Australia
    Posts
    550

    Default Re: convert a number to words in excel 2007

    Using the same code Andrew posted.. appending the currency description to the function... and just modifying the following lines..

    Code:
     Select Case Cents
            Case ""
                'Cents = " and No Cents Only"
                 Cents = " Only"
            Case "One"
                Cents = " and One Cent"
                  Case Else
                Cents = " and " & Cents & " Cents Only"
        End Select
    Sheet3

     AB
    1$1,366,500.00US Dollars One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars Only
    2$1,366,500.00Saudi Ryals One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars Only
    3$1,366,500.55US Dollars One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars and Fifty Five Cents Only
    4$1,366,500.55Saudi Ryals One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars and Fifty Five Cents Only

    Spreadsheet Formulas
    CellFormula
    B1="US Dollars " & SpellNumber(A1)
    B2="Saudi Ryals " & SpellNumber(A2)
    B3="US Dollars " & SpellNumber(A3)
    B4="Saudi Ryals " & SpellNumber(A4)

    Excel tables to the web - Excel Jeanie Html 4

  4. #4
    New Member
    Join Date
    Aug 2013
    Posts
    21

    Default Re: convert a number to words in excel 2007

    Hello,

    Using the above code, how to change the result from:

    One Million Three Hundred Sixty Six Thousand Five Hundred Dollars and Fifty Five Cents Only
    to
    One Million Three Hundred Sixty Six Thousand Five Hundred Dollars and 55/100 Cent(s) Only

    Any idea?



  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    1

    Default Re: convert a number to words in excel 2007

    You also do it with my function:

    Option Explicit
    Option Base 0
    'Author: thaipv@live.com
    'Date: 30/11/2014
    'Reference: ACC: How to Convert Currency or Numbers into English Words
    'More: MS Excel: Convert currency into words
    Function READNUMBER(ByVal CNUMBER, Optional UPPER As Boolean = True) As String
    Dim DIGIT, ATEEN, ATENS, PLACE(13), READ1__$, READ_23$, READ123$
    Dim NUMBER1 As Byte, NUMBER2 As Byte, NUMBER3 As Byte, V As Byte

    DIGIT = Array("", " one", " two", " three", " four", " five", " six", " seven", " eight", " nine")
    ATEEN = Array(" ten", " elevent", " twelve", " thirteen", " fourteen", " fifteen", " sixteen", " seventeen", " eighteen", " nineteen")
    ATENS = Array("", "", " twenty", " thirty", " forty", " fifty", " sixty", " seventy", " eighty", " ninety")
    PLACE(1) = " trillion,": PLACE(4) = " billion,"
    PLACE(7) = " million,": PLACE(10) = " thousand,"


    If Trim$(CNUMBER) = "" Then
    READNUMBER = ""
    ElseIf Not IsNumeric(CNUMBER) Then
    READNUMBER = "(_) Hmm, It is too embarrassing! XA can not read this number: " & "'" & CNUMBER & "'"
    ElseIf CNUMBER < -10 ^ 15 Then
    READNUMBER = "(_) Hmm, It is too embarrassing! This number is too small to read."
    ElseIf CNUMBER > 10 ^ 15 Then
    READNUMBER = "(_) Hmm, It is too embarrassing! This number is too large to read."
    Else
    '// Doc dau am/duong cua so tien va xy ly so tien truoc khi doc//
    READNUMBER = IIf(Round(CNUMBER, 0) < 0, "minus", "")
    CNUMBER = Round(Abs(CNUMBER), 0): CNUMBER = "000000000000000" & CNUMBER
    CNUMBER = Replace$(CNUMBER, ",", ""): CNUMBER = Right$(CNUMBER, 15)

    '// Bat dau doc so //
    For V = 1 To 13 Step 3
    NUMBER1 = Mid$(CNUMBER, V, 1)
    NUMBER2 = Mid$(CNUMBER, V + 1, 1)
    NUMBER3 = Mid$(CNUMBER, V + 2, 1)
    READ123 = ""
    READ1__ = IIf(NUMBER1 = 0, "", DIGIT(NUMBER1) & " hundread")
    Select Case NUMBER2
    Case 0: READ_23 = DIGIT(NUMBER3)
    Case 1: READ_23 = ATEEN(NUMBER3)
    Case Else: READ_23 = ATENS(NUMBER2) & DIGIT(NUMBER3)
    End Select
    READ123 = READ1__ & READ_23
    READNUMBER = Trim$(READNUMBER & IIf(Len(READ123) = 0, "", READ123 & PLACE(V)))
    Next V

    '// Xu ly (nhung) dau phay (",") du thua va VIET HOA chu dau tien //
    READNUMBER = IIf(Right$(READNUMBER, 1) = ",", Left$(READNUMBER, Len(READNUMBER) - 1), READNUMBER)
    READNUMBER = IIf(UPPER = True, UCase$(Left$(READNUMBER, 1)) & Mid$(READNUMBER, 2), READNUMBER)
    End If
    End Function

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com