Converting digits into words.

s_chandru

New Member
Joined
Feb 15, 2004
Messages
1
Hi

We usually use various statments in XL and arrive at a net figure. I want the result to be represented in words.

For ex : if I have 10,999.99

The word column should show

ten thousand nine hundred and ninety nine and paise ninety nine only.


I am sure one of this team would have done a macro for this... Pls. share...


Chandru
Code:
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
download the morefunc add-in and use the =nbtext() function.
 
Upvote 0

kieran

Active Member
Joined
Oct 27, 2002
Messages
429
I use a macro by Legare Coleman


Here is what I use, it works for up to Sextllions. It will also give you something like
'Three Hundred Thirty Dollars and Sixty Seven Cents if you pass it the optional second and third parameters. like this:

'=NumberToText(330.67,"Dollars","Cents")

' (c) Legare Coleman


Public Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant
Dim TMBT As Variant
Dim sNum As String, sDec As String, sHun As String, IC As Integer
Dim Result As String, sCurName As String, sCent As String

If Application.IsNumber(Num) = False Then
NumberToText = CVErr(xlValue)
Exit Function
End If

If IsMissing(vCurName) Then
sCurName = ""
Else
sCurName = Trim(CStr(vCurName))
End If
If IsMissing(vCent) Then
sCent = ""
Else
sCent = Trim(CStr(vCent))
End If

TMBT = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion")

If IsMissing(sCent) Or IsNull(sCent) Then
sNum = Format(Application.Round(Num, 0), "0")
Else
sNum = Format(Application.Round(Num, 2), "0.00")
sDec = Right(sNum, 2)
sNum = Left(sNum, Len(sNum) - 3)
If CInt(sDec) <> 0 Then
sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
Else
sDec = ""
End If
End If

IC = 0
While Len(sNum) > 0
sHun = Right(sNum, 3)
sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0))
If CInt(sHun) <> 0 Then
Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result)
End If
IC = IC + 1
Wend
Result = Trim(Result & " " & sCurName)
Result = Trim(Result & " " & sDec)

NumberToText = Result

End Function

Private Function HundredsToText(Num As Integer) As String
Dim Units As Variant, Teens As Variant, Tens As Variant
Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer
Dim Result As String

Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

Result = ""
IUnit = Num Mod 10
I = Int(Num / 10)
ITen = I Mod 10
IHundred = Int(I / 10)
If IHundred > 0 Then
Result = Units(IHundred) & " Hundred"
End If
If ITen = 1 Then
Result = Result & " " & Teens(IUnit)
Else
If ITen > 1 Then
Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit))
Else
Result = Trim(Result & " " & Units(IUnit))
End If
End If

HundredsToText = Result

End Function
 
Upvote 0

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi S_Chandru:

Welcome to MrExcel Board!

Here is a UDF that converts digits into words for Indian Curency ...
Rich (BB code):
Function ySpellRupees1(ByVal MyNumber) 

'**** Yogi Anand -- ANAND Enterprises -- Rochester Hills MI 48309 -- 248-375-5710 www.anandent.com 
'**** Last updated 03-Oct-2003 
'**** ySpellRupees1 (modified on 20-Sep-2003 to 1) show Rupees to precede, and to show "" for 0 paise) 
'**** ySpellRupees (on 20-Nov-2002) 
'**** Excel UDF to spell Indian Currency -- Rupees and Paise into text 
'**** Indian currency starts off with 1000s, and after that only with 100s 
'**** 1000 (Thousand) -- 1,00,000 (Lac or Lakh) -- 1,00,00,000 (Crore) -- 1,00,00,00,000 (Arab) 
'**** (this UDF is based on SpellNumber by Microsoft) 
'****************' Main Function *'**************** 

    Dim Rupees, Paise, Temp 
    Dim DecimalPlace, Count 
    ReDim Place(9) As String 
        Place(2) = " Thousand " 
        Place(3) = " Lac " 
        Place(4) = " Crore " 
        Place(5) = " Arab " ' String representation of amount 
        MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none 
        DecimalPlace = InStr(MyNumber, ".") 
        'Convert Paise and set MyNumber to Rupee amount 
        If DecimalPlace > 0 Then 
            Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) 
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) 
        End If 
        Count = 1 
        Do While MyNumber <> "" 
            If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3)) 
            If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2)) 
            If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees 
            If Count = 1 And Len(MyNumber) > 3 Then 
            MyNumber = Left(MyNumber, Len(MyNumber) - 3) 
            Else 
                If Count > 1 And Len(MyNumber) > 2 Then 
                MyNumber = Left(MyNumber, Len(MyNumber) - 2) 
                Else 
                    MyNumber = "" 
                End If 
            End If 
            Count = Count + 1 
        Loop 
        Select Case Rupees 
            Case "" 
                Rupees = "No Rupees" 
            Case "One" 
                Rupees = "One Rupee" 
            Case Else 
'**************************************************************** 
'Yogi Anand on 20-Sep-2003 
'modified the following two lines to display "Rupees" to precede 
' rem'd the first line and added the second line 
'**************************************************************** 
                'Rupees = Rupees & " Rupees" 
                Rupees = "Rupees " & Rupees 
                
        End Select 
        Select Case Paise 
            Case "" 
'**************************************************************** 
'Yogi Anand on 20-Sep-2003 
'modified the following two lines to display nothing for no paise 
' rem'd the first line and added the second line 
'**************************************************************** 
                
                'Paise = " and No Paise" 
'**************************************************************** 
'Yogi Anand on 03-Oct-2003 
'modified the following line to display " Only" for no paise 
' rem'd the first line and added the second line 
'**************************************************************** 
                'Paise = "" 
                Paise = "Only" 
            Case "One" 
                Paise = " and One Paisa" 
            Case Else 
                Paise = " and " & Paise & " Paise" 

        End Select 
    ySpellRupees1 = Rupees & Paise 
End Function 
'******************************************* 
' Converts a number from 100-999 into text * 
'******************************************* 
Function GetHundreds(ByVal MyNumber) 
    Dim Result As String 
        If Val(MyNumber) = 0 Then Exit Function 
        MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place 
        If Mid(MyNumber, 1, 1) <> "0" Then 
            Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " 
        End If 
        'Convert the tens and ones place 
        If Mid(MyNumber, 2, 1) <> "0" Then 
            Result = Result & GetTens(Mid(MyNumber, 2)) 
            Else 
                Result = Result & GetDigit(Mid(MyNumber, 3)) 
        End If 
        GetHundreds = Result 
End Function 
'********************************************* 
' Converts a number from 10 to 99 into text. * 
'********************************************* 
Function GetTens(TensText) 
    Dim Result As String 
        Result = "" 'null out the temporary function value 
        If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19 
            Select Case Val(TensText) 
                Case 10: Result = "Ten" 
                Case 11: Result = "Eleven" 
                Case 12: Result = "Twelve" 
                Case 13: Result = "Thirteen" 
                Case 14: Result = "Fourteen" 
                Case 15: Result = "Fifteen" 
                Case 16: Result = "Sixteen" 
                Case 17: Result = "Seventeen" 
                Case 18: Result = "Eighteen" 
                Case 19: Result = "Nineteen" 
                Case Else 
            End Select 
            Else ' If value between 20-99 
            Select Case Val(Left(TensText, 1)) 
                Case 2: Result = "Twenty " 
                Case 3: Result = "Thirty " 
                Case 4: Result = "Forty " 
                Case 5: Result = "Fifty " 
                Case 6: Result = "Sixty " 
                Case 7: Result = "Seventy " 
                Case 8: Result = "Eighty " 
                Case 9: Result = "Ninety " 
                Case Else 
            End Select 
            Result = Result & GetDigit _ 
            (Right(TensText, 1)) 'Retrieve ones place 
        End If 
        GetTens = Result 
End Function 
'******************************************* 
' Converts a number from 1 to 9 into text. * 
'******************************************* 
Function GetDigit(Digit) 
    Select Case Val(Digit) 
        Case 1: GetDigit = "One" 
        Case 2: GetDigit = "Two" 
        Case 3: GetDigit = "Three" 
        Case 4: GetDigit = "Four" 
        Case 5: GetDigit = "Five" 
        Case 6: GetDigit = "Six" 
        Case 7: GetDigit = "Seven" 
        Case 8: GetDigit = "Eight" 
        Case 9: GetDigit = "Nine" 
        Case Else: GetDigit = "" 
    End Select 
End Function
I hope this helps!
 
Upvote 0

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
ADVERTISEMENT
where do i get the morefunc add-in and use the =nbtext() function
 
Upvote 0

Forum statistics

Threads
1,195,623
Messages
6,010,749
Members
441,567
Latest member
Flitbee

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
Top