convert a number to words in excel 2007

excell2007

New Member
Joined
Sep 23, 2013
Messages
2
Required <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> 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 <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> code provided.

Thanks & regards,
shafique ahmed
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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"
[COLOR=#ff0000]             Cents = " Only"[/COLOR]
        Case "One"
            Cents = " and One Cent"
              Case Else
[COLOR=#ff0000]            Cents = " and " & Cents & " Cents Only"[/COLOR]
    End Select

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:125px;" /><col style="width:666px;" /></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="text-align:right; ">$1,366,500.00</td><td >US Dollars One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars Only</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">$1,366,500.00</td><td >Saudi Ryals One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars Only</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">$1,366,500.55</td><td >US Dollars One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars and Fifty Five Cents Only</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">$1,366,500.55</td><td >Saudi Ryals One Million Three Hundred Sixty Six Thousand Five Hundred  Dollars and Fifty Five Cents Only</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 >="US Dollars " & SpellNumber(A1)</td></tr><tr><td >B2</td><td >="Saudi Ryals " & SpellNumber(A2)</td></tr><tr><td >B3</td><td >="US Dollars " & SpellNumber(A3)</td></tr><tr><td >B4</td><td >="Saudi Ryals " & SpellNumber(A4)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
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?


 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top