I want a formula on how to change/translate an amount in figure in a cell to
amount in words on the other cell within the excel sheet. How do I do this?

Like for example: In cell D3 I typed 6000, on cell D4 it should be " Six
thousand"

So I would have to type the words, instead it will automatically translate
the amount in figure to amount in words.

Thanks

>I want a formula on how to change/translate an amount in figure in a cell
>to
> amount in words on the other cell within the excel sheet. How do I do
> this?
>
> Like for example: In cell D3 I typed 6000, on cell D4 it should be " Six
> thousand"
>
> So I would have to type the words, instead it will automatically translate
> the amount in figure to amount in words.

Go into the VB Editor (Alt+F11) and add a Module to the Workbook
(Insert/Module from the VBA menu) and then paste in all of the code
appearing after my signature into the Module's code window. You can call the
function from your work sheet like this =NumberAsText(A1) where A1 is assume
to hold the number you want to convert. There is an Optional argument you
can use to format the results to your liking. Here are some examples to show
the options available...

A1: 123.45

In B1:
=NumberAsText(A1) ==> One Hundred Twenty Three Point Four Five
=NumberAsText(A1"And") ==> One Hundred and Twenty Three Point Four Five
=NumberAsText(A1,"Check") ==> One Hundred Twenty Three and 45/100
=NumberAsText(A1,"Dollar") ==> One Hundred Twenty Three Dollars and Forty
Five Cents

To summarize, using "And" adds the word "and" in front of the tens/units
text; using "Check" formats any decimal values as would be written on a
check; and using "Dollar" adds the words "Dollars" and "Cents" in their
appropriate positions. The code is laid out in a reasonably straight forward
manner, so if Dollars/Cents is not you native currency designation, you
should be able to modify the program accordingly. In addition to the above,
for all modes, the Plus and Minus sign can be used and will be reported back
as a word; commas may be used to separate the numbers to the left of the
decimal point but they will not be reported back by the routine and are
permitted for the users convenience (however, if commas are used, they must
be placed in their correct positions). And, finally,if I remember correctly,
this function will work with a whole number part up to one less than a
quintillion (you can have as many decimal points as desired), but remember
to format large numbers as Text values... VB will convert large non-Text
values to Doubles (which will destroy the conversion).

Rick


Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, _
Optional AND_or_CHECK_or_DOLLAR As String) As String
Dim cnt As Long
Dim DecimalPoint As Long
Dim CardinalNumber As Long
Dim CommaAdjuster As Long
Dim TestValue As Long
Dim CurrValue As Currency
Dim CentsString As String
Dim NumberSign As String
Dim WholePart As String
Dim BigWholePart As String
Dim DecimalPart As String
Dim tmp As String
Dim sStyle As String
Dim bUseAnd As Boolean
Dim bUseCheck As Boolean
Dim bUseDollars As Boolean
'----------------------------------------
' Begin setting conditions for formatting
'----------------------------------------
' Determine whether to apply special formatting.
' If nothing passed, return routine result
' converted only into its numeric equivalents,
' with no additional format text.
sStyle = LCase(AND_or_CHECK_or_DOLLAR)
' User passed "AND": "and" will be added
' between hundredths and tens of dollars,
' ie "Three Hundred and Forty Two"
bUseAnd = sStyle = "and"
' User passed "DOLLAR": "dollar(s)" and "cents"
' appended to string,
' ie "Three Hundred and Forty Two Dollars"
bUseDollars = sStyle = "dollar"
' User passed "CHECK" *or* "DOLLAR"
' If "check", cent amount returned as a fraction /100
' i.e. "Three Hundred Forty Two and 00/100"
' If "dollar" was passed, "dollar(s)" and "cents"
' Appended instead.
bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
'----------------------------------------
' Check/create array. If this is the first
' time using this routine, create the text
' strings that will be used.
'----------------------------------------
If Not IsBounded(sNumberText) Then
Call BuildArray(sNumberText)
End If
'----------------------------------------
' Begin validating the number, and breaking
' into constituent parts
'----------------------------------------
' Prepare to check for valid value in
NumberIn = Trim$(NumberIn)
If Not IsNumeric(NumberIn) Then
' Invalid entry - abort
NumberAsText = "Error - Number improperly formed"
Exit Function
Else
' Decimal check
DecimalPoint = InStr(NumberIn, ".")
If DecimalPoint > 0 Then
' Split the fractional and primary numbers
DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
WholePart = Left$(NumberIn, DecimalPoint - 1)
Else
' Assume the decimal is the last char
DecimalPoint = Len(NumberIn) + 1
WholePart = NumberIn
End If
If InStr(NumberIn, ",,") Or _
InStr(NumberIn, ",.") Or _
InStr(NumberIn, ".,") Or _
InStr(DecimalPart, ",") Then
NumberAsText = "Error - Improper use of commas"
Exit Function
ElseIf InStr(NumberIn, ",") Then
CommaAdjuster = 0
WholePart = ""
For cnt = DecimalPoint - 1 To 1 Step -1
If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
WholePart = Mid$(NumberIn, cnt, 1) & WholePart
Else
CommaAdjuster = CommaAdjuster + 1
If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
NumberAsText = "Error - Improper use of commas"
Exit Function
End If
End If
Next
End If
End If
If Left$(WholePart, 1) Like "[+-]" Then
NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
WholePart = Mid$(WholePart, 2)
End If
'----------------------------------------
' Begin code to assure decimal portion of
' check value is not inadvertently rounded
'----------------------------------------
If bUseCheck = True Then
CurrValue = CCur(Val("." & DecimalPart))
DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
If CurrValue >= 0.995 Then
If WholePart = String$(Len(WholePart), "9") Then
WholePart = "1" & String$(Len(WholePart), "0")
Else
For cnt = Len(WholePart) To 1 Step -1
If Mid$(WholePart, cnt, 1) = "9" Then
Mid$(WholePart, cnt, 1) = "0"
Else
Mid$(WholePart, cnt, 1) = _
CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
Exit For
End If
Next
End If
End If
End If
'----------------------------------------
' Final prep step - this assures number
' within range of formatting code below
'----------------------------------------
If Len(WholePart) > 9 Then
BigWholePart = Left$(WholePart, Len(WholePart) - 9)
WholePart = Right$(WholePart, 9)
End If
If Len(BigWholePart) > 9 Then
NumberAsText = "Error - Number too large"
Exit Function
ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
(Not BigWholePart Like String$(Len(BigWholePart), "#") _
And Len(BigWholePart) > 0) Then
NumberAsText = "Error - Number improperly formed"
Exit Function
End If
'----------------------------------------
' Begin creating the output string
'----------------------------------------
' Very Large values
TestValue = Val(BigWholePart)
If TestValue > 999999 Then
CardinalNumber = TestValue \ 1000000
tmp = HundredsTensUnits(CardinalNumber) & "Quadrillion "
TestValue = TestValue - (CardinalNumber * 1000000)
End If
If TestValue > 999 Then
CardinalNumber = TestValue \ 1000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
TestValue = TestValue - (CardinalNumber * 1000)
End If
If TestValue > 0 Then
tmp = tmp & HundredsTensUnits(TestValue) & "Billion "
End If
' Lesser values
TestValue = Val(WholePart)
If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
If TestValue > 999999 Then
CardinalNumber = TestValue \ 1000000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Million "
TestValue = TestValue - (CardinalNumber * 1000000)
End If
If TestValue > 999 Then
CardinalNumber = TestValue \ 1000
tmp = tmp & HundredsTensUnits(CardinalNumber) & "Thousand "
TestValue = TestValue - (CardinalNumber * 1000)
End If
If TestValue > 0 Then
If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
tmp = tmp & HundredsTensUnits(TestValue, bUseAnd)
End If
' If in dollar mode, assure the text is the correct plurality
If bUseDollars = True Then
CentsString = HundredsTensUnits(DecimalPart)
If tmp = "One " Then
tmp = tmp & "Dollar"
Else
tmp = tmp & "Dollars"
End If
If Len(CentsString) > 0 Then
tmp = tmp & " and " & CentsString
If CentsString = "One " Then
tmp = tmp & "Cent"
Else
tmp = tmp & "Cents"
End If
End If
ElseIf bUseCheck = True Then
tmp = tmp & "and " & Left$(DecimalPart & "00", 2)
tmp = tmp & "/100"
Else
If Len(DecimalPart) > 0 Then
tmp = tmp & "Point"
For cnt = 1 To Len(DecimalPart)
tmp = tmp & " " & sNumberText(Mid$(DecimalPart, cnt, 1))
Next
End If
End If
' Done!
NumberAsText = NumberSign & tmp
End Function

Private Sub BuildArray(sNumberText() As String)
ReDim sNumberText(0 To 27) As String
sNumberText(0) = "Zero"
sNumberText(1) = "One"
sNumberText(2) = "Two"
sNumberText(3) = "Three"
sNumberText(4) = "Four"
sNumberText(5) = "Five"
sNumberText(6) = "Six"
sNumberText(7) = "Seven"
sNumberText(8) = "Eight"
sNumberText(9) = "Nine"
sNumberText(10) = "Ten"
sNumberText(11) = "Eleven"
sNumberText(12) = "Twelve"
sNumberText(13) = "Thirteen"
sNumberText(14) = "Fourteen"
sNumberText(15) = "Fifteen"
sNumberText(16) = "Sixteen"
sNumberText(17) = "Seventeen"
sNumberText(18) = "Eighteen"
sNumberText(19) = "Nineteen"
sNumberText(20) = "Twenty"
sNumberText(21) = "Thirty"
sNumberText(22) = "Forty"
sNumberText(23) = "Fifty"
sNumberText(24) = "Sixty"
sNumberText(25) = "Seventy"
sNumberText(26) = "Eighty"
sNumberText(27) = "Ninety"
End Sub

Private Function IsBounded(vntArray As Variant) As Boolean
' Note: the application in the IDE will stop
' at this line when first run if the IDE error
' mode is not set to "Break on Unhandled Errors"
' (Tools/Options/General/Error Trapping)
On Error Resume Next
IsBounded = IsNumeric(UBound(vntArray))
End Function

Private Function HundredsTensUnits(ByVal TestValue As Integer, _
Optional bUseAnd As Boolean) As String
Dim CardinalNumber As Integer
If TestValue > 99 Then
CardinalNumber = TestValue \ 100
HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
TestValue = TestValue - (CardinalNumber * 100)
End If
If bUseAnd = True Then
HundredsTensUnits = HundredsTensUnits & "and "
End If
If TestValue > 20 Then
CardinalNumber = TestValue \ 10
HundredsTensUnits = HundredsTensUnits & _
sNumberText(CardinalNumber + 18) & " "
TestValue = TestValue - (CardinalNumber * 10)
End If
If TestValue > 0 Then
HundredsTensUnits = HundredsTensUnits & _
sNumberText(TestValue) & " "
End If
End Function

> I have also tried to do the same but not worked to other excel sheet
> where the module not saved.*What to do then*?
>
> Not working to other excel sheet.Then what to do????

After you get the Module set up the way you want, give the Module a
meaningful name, then right-click on it in the Project window and Export it
to a location of your choice (perhaps a MyModules directory). Then, whenever
you have need of that particular functionality, just do a File/ImportFile
from the VBA menu and the Module will be added to your project.

Rick

function showing amount in wds in excel wksheet



Loti wrote:

How to change amount in figure to amount in words?
22-Jun-07

I want a formula on how to change/translate an amount in figure in a cell to
amount in words on the other cell within the excel sheet. How do I do this

Like for example: In cell D3 I typed 6000, on cell D4 it should be " Six
thousand

So I would have to type the words, instead it will automatically translate
the amount in figure to amount in words

Thanks

Previous Posts In This Thread:

On Friday, June 22, 2007 11:12 PM
Loti wrote:

How to change amount in figure to amount in words?
I want a formula on how to change/translate an amount in figure in a cell to
amount in words on the other cell within the excel sheet. How do I do this

Like for example: In cell D3 I typed 6000, on cell D4 it should be " Six
thousand

So I would have to type the words, instead it will automatically translate
the amount in figure to amount in words

Thanks

On Tuesday, June 26, 2007 9:35 PM
rony4icab wrote:

I have also tried to do the same but not worked to other excel sheetwhere the
I have also tried to do the same but not worked to other excel shee
where the module not saved.*What to do then*

Not working to other excel sheet.Then what to do???

--
rony4ica
-----------------------------------------------------------------------
rony4icab's Profile: http://www.officehelp.in/members/3579/rony4ica
View this thread: http://www.officehelp.in/1490075/cha...re-amount-word

http://www.officehelp.in

On Tuesday, June 26, 2007 11:34 PM
Rick Rothstein \(MVP - VB\) wrote:

After you get the Module set up the way you want, give the Module a meaningful
After you get the Module set up the way you want, give the Module a
meaningful name, then right-click on it in the Project window and Export it
to a location of your choice (perhaps a MyModules directory). Then, whenever
you have need of that particular functionality, just do a File/ImportFile
from the VBA menu and the Module will be added to your project

Rick

EggHeadCafe - Software Developer Portal of Choice
Extract Icons from the Registry by their Extensions
http://www.eggheadcafe.com/tutorials...om-the-re.aspx

Amount in figures change / translate in to amount in words



solaiman mazumder wrote:

function showing amount in wds in excel wksheet
20-Nov-09

function showing amount in wds in excel wksheet

Previous Posts In This Thread:

On Friday, June 22, 2007 11:12 PM
Loti wrote:

How to change amount in figure to amount in words?
I want a formula on how to change/translate an amount in figure in a cell to
amount in words on the other cell within the excel sheet. How do I do this

Like for example: In cell D3 I typed 6000, on cell D4 it should be " Six
thousand

So I would have to type the words, instead it will automatically translate
the amount in figure to amount in words

Thanks

On Tuesday, June 26, 2007 9:35 PM
rony4icab wrote:

I have also tried to do the same but not worked to other excel sheetwhere the
I have also tried to do the same but not worked to other excel shee
where the module not saved.*What to do then*

Not working to other excel sheet.Then what to do???

--
rony4ica
-----------------------------------------------------------------------
rony4icab's Profile: http://www.officehelp.in/members/3579/rony4ica
View this thread: http://www.officehelp.in/1490075/cha...re-amount-word

http://www.officehelp.in

On Tuesday, June 26, 2007 11:34 PM
Rick Rothstein \(MVP - VB\) wrote:

After you get the Module set up the way you want, give the Module a meaningful
After you get the Module set up the way you want, give the Module a
meaningful name, then right-click on it in the Project window and Export it
to a location of your choice (perhaps a MyModules directory). Then, whenever
you have need of that particular functionality, just do a File/ImportFile
from the VBA menu and the Module will be added to your project

Rick

On Friday, November 20, 2009 9:26 AM
solaiman mazumder wrote:

function showing amount in wds in excel wksheet
function showing amount in wds in excel wksheet


Submitted via EggHeadCafe - Software Developer Portal of Choice
DataContractSerializer Basics
http://www.eggheadcafe.com/tutorials...alizer-ba.aspx

See http://www.xldynamic.com/source/xld.xlFAQ0004.html

--

HTH

Bob

<Mohammad Ali Ansari> wrote in message
news:(E-Mail Removed)...
> Amount in figures change / translate in to amount in words
>
>
>
> solaiman mazumder wrote:
>
> function showing amount in wds in excel wksheet
> 20-Nov-09
>
> function showing amount in wds in excel wksheet
>
> Previous Posts In This Thread:
>
> On Friday, June 22, 2007 11:12 PM
> Loti wrote:
>
> How to change amount in figure to amount in words?
> I want a formula on how to change/translate an amount in figure in a cell
> to
> amount in words on the other cell within the excel sheet. How do I do
> this?
>
> Like for example: In cell D3 I typed 6000, on cell D4 it should be " Six
> thousand"
>
> So I would have to type the words, instead it will automatically translate
> the amount in figure to amount in words.
>
> Thanks
>
> On Tuesday, June 26, 2007 9:35 PM
> rony4icab wrote:
>
> I have also tried to do the same but not worked to other excel sheetwhere
> the
> I have also tried to do the same but not worked to other excel sheet
> where the module not saved.*What to do then*?
>
> Not working to other excel sheet.Then what to do????
>
>
> --
> rony4icab
> ------------------------------------------------------------------------
> rony4icab's Profile: http://www.officehelp.in/members/3579/rony4icab
> View this thread:
> http://www.officehelp.in/1490075/cha...e-amount-words
>
> http://www.officehelp.in
>
> On Tuesday, June 26, 2007 11:34 PM
> Rick Rothstein \(MVP - VB\) wrote:
>
> After you get the Module set up the way you want, give the Module a
> meaningful
> After you get the Module set up the way you want, give the Module a
> meaningful name, then right-click on it in the Project window and Export
> it
> to a location of your choice (perhaps a MyModules directory). Then,
> whenever
> you have need of that particular functionality, just do a File/ImportFile
> from the VBA menu and the Module will be added to your project.
>
> Rick
>
> On Friday, November 20, 2009 9:26 AM
> solaiman mazumder wrote:
>
> function showing amount in wds in excel wksheet
> function showing amount in wds in excel wksheet
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> DataContractSerializer Basics
> http://www.eggheadcafe.com/tutorials...alizer-ba.aspx