Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > number into text or in words
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 132
Default number into text or in words
I need to put Invoice value should also be shown in words. Anybody can help
how to show numeric value into words along with Currency in excel?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default number into text or in words

=TEXT(H25,"$*0.00")


"Narnimar" wrote:

I need to put Invoice value should also be shown in words. Anybody can help
how to show numeric value into words along with Currency in excel?
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 132
Default number into text or in words
Joel,
It returns like this $25000.00. But I want result in words "Dollar Twenty
Five Thousand and no Zero cent"

"Joel" wrote:


=TEXT(H25,"$*0.00")


"Narnimar" wrote:

I need to put Invoice value should also be shown in words. Anybody can help
how to show numeric value into words along with Currency in excel?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default number into text or in words
See Bob Phillips' site.

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


Gord Dibben MS Excel MVP


On Mon, 26 Feb 2007 07:33:05 -0800, Narnimar
wrote:

I need to put Invoice value should also be shown in words. Anybody can help
how to show numeric value into words along with Currency in excel?

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default number into text or in words
Here is a functtion that will do whatt you want. There is no simple way of
doing this.

Function GetCurrency(MyCurrency As Double) As String


Dim Ones(10) As String
Dim Tens(10) As String
Dim Teens(10) As String

Ones(0) = ""
Ones(1) = "One"
Ones(2) = "Two"
Ones(3) = "Three"
Ones(4) = "Four"
Ones(5) = "Five"
Ones(6) = "Six"
Ones(7) = "Seven"
Ones(8) = "Eight"
Ones(9) = "Nine"

Tens(0) = ""
Tens(1) = "Ten"
Tens(2) = "Twenty"
Tens(3) = "Thirty"
Tens(4) = "Fourty"
Tens(5) = "Fifty"
Tens(6) = "Sixty"
Tens(7) = "Seventy"
Tens(8) = "Eighty"
Tens(9) = "Ninety"

Teens(0) = "Ten"
Teens(1) = "Eleven"
Teens(2) = "Twelve"
Teens(3) = "Thirteen"
Teens(4) = "Fourteen"
Teens(5) = "Fiveteen"
Teens(6) = "Sixteen"
Teens(7) = "Seventeen"
Teens(8) = "Eighteen"
Teens(9) = "Nineteen"



StringNumber = CStr(MyCurrency)

If (InStr(StringNumber, ".") 0) Then
MyDollars = Left(StringNumber, InStr(StringNumber, ".") - 1)
MyCents = Mid(StringNumber, InStr(StringNumber, ".") + 1)
Else
MyDollars = StringNumber
MyCents = "00"
End If


GetCurrency = ""

DollarLength = Len(MyDollars)
If ((DollarLength Mod 3) = 1) Then
MyDollars = "00" & MyDollars
DollarLength = Len(MyDollars)
Else

If ((DollarLength Mod 3) = 2) Then
MyDollars = "0" & MyDollars
DollarLength = Len(MyDollars)
End If

End If


For i = DollarLength To 1 Step -3

Nibble = Mid(MyDollars, DollarLength - i + 1, 3)


If (StrComp(Left(Nibble, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Left(Nibble, 1))) & " Hundred"
End If

If (StrComp(Mid(Nibble, 2, 1), "0") < 0) Then

If (CInt(Mid(Nibble, 2, 2)) 10) And _
(CInt(Mid(Nibble, 2, 2)) <= 19) Then

GetCurrency = GetCurrency & " " & _
Teens(CInt(Mid(Nibble, 3, 1)))

Else

If (StrComp(Mid(Nibble, 2, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Tens(CInt(Mid(Nibble, 2, 1)))
End If

If (StrComp(Mid(Nibble, 3, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Mid(Nibble, 3, 1)))

End If
End If

Else

If (StrComp(Mid(Nibble, 3, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Mid(Nibble, 3, 1)))
End If
End If


Select Case i

Case 6
GetCurrency = GetCurrency & " Thousands"
Case 9
GetCurrency = GetCurrency & " Millions"
Case 12
GetCurrency = GetCurrency & " Billions"
Case 15
GetCurrency = GetCurrency & " Trillions"

End Select


Next i

GetCurrency = GetCurrency & " Dollars"

If (StrComp(MyCents, "00") = 0) Then
GetCurrency = GetCurrency & " and Zero"
Else
GetCurrency = GetCurrency & " and"
End If


If (StrComp(Left(MyCents, 1), "0") < 0) Then

If (CInt(MyCents) 10) And _
(CInt(MyCents) <= 19) Then

GetCurrency = GetCurrency & " " & _
Teens(CInt(Right(MyCents, 1)))

Else

If (StrComp(Left(MyCents, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Tens(CInt(Left(MyCents, 1)))
End If

If (StrComp(Right(MyCents, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Right(MyCents, 1)))

End If
End If

Else

If (StrComp(Right(MyCents, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Right(MyCents, 1)))
End If
End If

GetCurrency = GetCurrency & " Cents"

End Function


"Narnimar" wrote:

Joel,
It returns like this $25000.00. But I want result in words "Dollar Twenty
Five Thousand and no Zero cent"

"Joel" wrote:


=TEXT(H25,"$*0.00")


"Narnimar" wrote:

I need to put Invoice value should also be shown in words. Anybody can help
how to show numeric value into words along with Currency in excel?
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 132
Default number into text or in words
Thank for your answer. Can you let me know next step to use it .

"Joel" wrote:

Here is a functtion that will do whatt you want. There is no simple way of
doing this.

Function GetCurrency(MyCurrency As Double) As String


Dim Ones(10) As String
Dim Tens(10) As String
Dim Teens(10) As String

Ones(0) = ""
Ones(1) = "One"
Ones(2) = "Two"
Ones(3) = "Three"
Ones(4) = "Four"
Ones(5) = "Five"
Ones(6) = "Six"
Ones(7) = "Seven"
Ones(8) = "Eight"
Ones(9) = "Nine"

Tens(0) = ""
Tens(1) = "Ten"
Tens(2) = "Twenty"
Tens(3) = "Thirty"
Tens(4) = "Fourty"
Tens(5) = "Fifty"
Tens(6) = "Sixty"
Tens(7) = "Seventy"
Tens(8) = "Eighty"
Tens(9) = "Ninety"

Teens(0) = "Ten"
Teens(1) = "Eleven"
Teens(2) = "Twelve"
Teens(3) = "Thirteen"
Teens(4) = "Fourteen"
Teens(5) = "Fiveteen"
Teens(6) = "Sixteen"
Teens(7) = "Seventeen"
Teens(8) = "Eighteen"
Teens(9) = "Nineteen"



StringNumber = CStr(MyCurrency)

If (InStr(StringNumber, ".") 0) Then
MyDollars = Left(StringNumber, InStr(StringNumber, ".") - 1)
MyCents = Mid(StringNumber, InStr(StringNumber, ".") + 1)
Else
MyDollars = StringNumber
MyCents = "00"
End If


GetCurrency = ""

DollarLength = Len(MyDollars)
If ((DollarLength Mod 3) = 1) Then
MyDollars = "00" & MyDollars
DollarLength = Len(MyDollars)
Else

If ((DollarLength Mod 3) = 2) Then
MyDollars = "0" & MyDollars
DollarLength = Len(MyDollars)
End If

End If


For i = DollarLength To 1 Step -3

Nibble = Mid(MyDollars, DollarLength - i + 1, 3)


If (StrComp(Left(Nibble, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Left(Nibble, 1))) & " Hundred"
End If

If (StrComp(Mid(Nibble, 2, 1), "0") < 0) Then

If (CInt(Mid(Nibble, 2, 2)) 10) And _
(CInt(Mid(Nibble, 2, 2)) <= 19) Then

GetCurrency = GetCurrency & " " & _
Teens(CInt(Mid(Nibble, 3, 1)))

Else

If (StrComp(Mid(Nibble, 2, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Tens(CInt(Mid(Nibble, 2, 1)))
End If

If (StrComp(Mid(Nibble, 3, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Mid(Nibble, 3, 1)))

End If
End If

Else

If (StrComp(Mid(Nibble, 3, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Mid(Nibble, 3, 1)))
End If
End If


Select Case i

Case 6
GetCurrency = GetCurrency & " Thousands"
Case 9
GetCurrency = GetCurrency & " Millions"
Case 12
GetCurrency = GetCurrency & " Billions"
Case 15
GetCurrency = GetCurrency & " Trillions"

End Select


Next i

GetCurrency = GetCurrency & " Dollars"

If (StrComp(MyCents, "00") = 0) Then
GetCurrency = GetCurrency & " and Zero"
Else
GetCurrency = GetCurrency & " and"
End If


If (StrComp(Left(MyCents, 1), "0") < 0) Then

If (CInt(MyCents) 10) And _
(CInt(MyCents) <= 19) Then

GetCurrency = GetCurrency & " " & _
Teens(CInt(Right(MyCents, 1)))

Else

If (StrComp(Left(MyCents, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Tens(CInt(Left(MyCents, 1)))
End If

If (StrComp(Right(MyCents, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Right(MyCents, 1)))

End If
End If

Else

If (StrComp(Right(MyCents, 1), "0") < 0) Then
GetCurrency = GetCurrency & " " & _
Ones(CInt(Right(MyCents, 1)))
End If
End If

GetCurrency = GetCurrency & " Cents"

End Function


"Narnimar" wrote:

Joel,
It returns like this $25000.00. But I want result in words "Dollar Twenty
Five Thousand and no Zero cent"

"Joel" wrote:


=TEXT(H25,"$*0.00")


"Narnimar" wrote:

I need to put Invoice value should also be shown in words. Anybody can help
how to show numeric value into words along with Currency in excel?
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Number to Words Siddhartha Neogi Excel Worksheet Functions 7 April 4th 09 03:56 PM
Is there a maximum number of words when using text wrapping? Marian Excel Discussion (Misc queries) 6 September 11th 08 02:12 PM
Convert number in to words harshaputhraya Excel Discussion (Misc queries) 2 May 8th 06 10:31 AM
How do I specifly text lenth if the text is words instead of #'s Kari Gray-Smith Excel Worksheet Functions 1 December 9th 05 09:41 PM
Number Field to Text or Words mr_bsh Excel Discussion (Misc queries) 5 May 10th 05 03:40 PM


All times are GMT +1. The time now is 12:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement