Formula in Excel to turn numbers into text [Solved/Closed]

Posts
1
Registration date
Saturday March 23, 2013
Status
Member
Last seen
March 23, 2013
-
Hello,

I found formula but sir I want at last as per below.
As per formula
=words(1234) = one thousand two hundred thirty four
But sir I want
=words(1234) = one thousand two hundred thirty four only.
So please send me update detail on my mail.

System Configuration: Windows 7 / Chrome 25.0.1364.172
See more 

2 replies

Best answer
approved by Jean-François Pillou on Jan 25, 2019
Posts
54
Registration date
Tuesday April 2, 2013
Status
Member
Last seen
November 27, 2013
39
18
Thank you
HI

Please try the below given UDF code in your module

if any problem please inform us or give your code which was you using


'
Function SpellIndian(ByVal MyNumber)
    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 = "(Rupees"
    Case "One"
        Rupees = "(One Rupee)"
    Case Else

         'Rupees = Rupees & " Rupees"
        Rupees = "(Rupees " & Rupees

    End Select
    Select Case Paise
    Case ""

         'Paise = " and No Paise"

         'Paise = ""
        Paise = (")Only")
    Case "One"
        Paise = ("and One Paisa")
    Case Else
        Paise = " and " & Paise & " Paise)"

    End Select
    SpellIndian = (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

'

With above code you can use as say your amount in the A1

=SpellIndian(a1)


and it will appear the Words

Thanks

Patnaik

Say "Thank you" 18

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6069 users have said thank you to us this month

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
753
-1
Thank you
First, using all caps is considered as shouting and is not considered polite. Refrain from it when you type message in email, forums or sms etc

For your issue, whats the difference between the two, other than "ONLY"
just append "only" after formula or edit the the function "WORDS" and add "only" to it