Converting a number in words into a number

Solved/Closed
siddhu - Feb 22, 2010 at 11:00 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 25, 2010 at 08:33 AM
Hello,
i want to convert wrods into a number.Let me say more clearly...
if i take " one thousand five hundred six " it should be converted into -- 1506
" two hundred fifty six " it should be converted into -- 256

like this i want to convert for any number ( till billions value )

Can anybody help in this regard ?

If any one can answer my question pleadsse mail me

Thanks
Related:

4 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 23, 2010 at 01:21 AM
There is third party software callee "morefunc". There are a number of worksheets functions one of them is NBText which converts numbers into worlds.

the software url is perhaps
http://xcell05.free.fr/fclicksql/fclick.php?2

you can also make google search.
1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 23, 2010 at 02:00 AM
thanks Venkat for information about this addin. Quite a few useful functions it seem to have.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 23, 2010 at 01:38 AM
Sorry that was wrong solution. I misunderstood numbers into worlds not words into numbers
0
Hi rizvisa1
Thank you for your reply.i hope this will work.
Thank you very much


Regards
Siddhu
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 25, 2010 at 01:25 AM
you doubt it ? I am heart broken
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491 > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 25, 2010 at 07:14 AM
Hi,
<mode : Off subject> For you, emergency ! ;)) <mode : end Off subject>
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768 > aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022
Feb 25, 2010 at 08:30 AM
LOL
Thanks Man
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 25, 2010 at 08:33 AM
Siddu
Forget the above one. Use this one. This one is better (and tested too!!!)

Function wordToDigits(numInWords As String) As Variant

Dim objSmallNumber As Object     ' dictionay objects
Dim objMidNumber As Object       ' dictionary object
Dim objBigNumber As Object       ' dictionary objects
Dim tempNum As String            ' to lower case the variable passed

Dim numParts, numPart As Variant ' variables to hold fraction of numInWord
Dim bHasBill As Boolean          ' does have a full dollar
Dim bHasChange As Boolean        ' does have change

Dim bigBucket As Double          ' hold multiples of  numbers as we work thru
Dim smallBucket As Double        ' hold numbers as we work thru

Dim preNum As Double             ' hold full bill amount
Dim myNumber As Variant          ' number evaluated. If no prenum, then hold full amount

    bHasBill = False             ' does not have currency unit
    bHasChange = False           ' does not have fraction unit

' ########################################################
' #################### customize here ####################
' ########################################################

Dim CurrencyBill As String     ' currency unit. Leave blank if not needed
    CurrencyBill = "dollars"

Dim CurrencyChg As String      ' currency unit. Leave blank if not needed
    CurrencyChg = "cents"

Dim CurrencyDelim As String    ' decimal delimiter between full and fraction. Leave blank if not needed
    CurrencyDelim = "."

Dim numberSeparator As String  ' word that is valid in spelled number but is not a number example "One Hunred AND Eight"
    numberSeparator = "and"

    Set objSmallNumber = CreateObject("Scripting.Dictionary")
    objSmallNumber.Add "zero", 0
    objSmallNumber.Add "one", 1
    objSmallNumber.Add "two", 2
    objSmallNumber.Add "three", 3
    objSmallNumber.Add "four", 4
    objSmallNumber.Add "five", 5
    objSmallNumber.Add "six", 6
    objSmallNumber.Add "seven", 7
    objSmallNumber.Add "eight", 8
    objSmallNumber.Add "nine", 9
    objSmallNumber.Add "ten", 10
    objSmallNumber.Add "eleven", 11
    objSmallNumber.Add "twelve", 12
    objSmallNumber.Add "thirteen", 13
    objSmallNumber.Add "fourteen", 14
    objSmallNumber.Add "fifteen", 15
    objSmallNumber.Add "sixteen", 16
    objSmallNumber.Add "seventeen", 17
    objSmallNumber.Add "eighteen", 18
    objSmallNumber.Add "nineteen", 19
    
    Set objMidNumber = CreateObject("Scripting.Dictionary")
    objMidNumber.Add "twenty", 20
    objMidNumber.Add "thirty", 30
    objMidNumber.Add "forty", 40
    objMidNumber.Add "fifty", 50
    objMidNumber.Add "sixty", 60
    objMidNumber.Add "seventy", 70
    objMidNumber.Add "eighty", 80
    objMidNumber.Add "ninety", 90

    Set objBigNumber = CreateObject("Scripting.Dictionary")
    objBigNumber.Add "hundred", 100
    objBigNumber.Add "thousand", 1000
    objBigNumber.Add "million", 1000000
    objBigNumber.Add "billion", 1000000000

    
    tempNum = LCase(numInWords) 'lower case
    tempNum = Trim(tempNum) 'remove left right spaces
    tempNum = Replace(tempNum, "-", " ") 'replace any - (example fifty-one)
    
    For Each numPart In objMidNumber
        'add spaces after text like fifty to address situation like fiftyone to make it fifty one
        tempNum = Replace(tempNum, numPart, numPart & " ")
    Next
    
    'remove any double space, that may have been added in the step above or already present
    Do While (InStr(1, tempNum, "  ", vbTextCompare) > 1)
        tempNum = Replace(tempNum, "  ", " ")
    Loop
    
    'break fraction into array based on " " as delimiter
    numParts = Split(tempNum, " ")
    
    bigBucket = 0
    smallBucket = 0
    preNum = 0
    
    ' loop thru each parsed word
    For Each numPart In numParts
            
        If (objSmallNumber.exists(numPart)) Then
            ' add numbers. they might be final or might have a multiplier (like "hundred" next)
            smallBucket = smallBucket + objSmallNumber(numPart)
            tempNum = ""
            
        ElseIf (objMidNumber.exists(numPart)) Then
            ' add numbers. they might be final or might have a multiplier (like "hundred" next)
            smallBucket = smallBucket + objMidNumber(numPart)
            tempNum = ""
            
        ElseIf (objBigNumber.exists(numPart)) Then
            ' a multiplier factor is found.
            bigBucket = bigBucket + smallBucket * objBigNumber(numPart)
            smallBucket = 0
            tempNum = ""
                    
        ElseIf (numPart = CurrencyBill) Then
            ' currency unit found. so next would be fraction
            
            'store total so far. this is before the fraction
            preNum = smallBucket + bigBucket
            smallBucket = 0
            bigBucket = 0
            bHasBill = True 'flag raised, that currency bill unit was found
           
        ElseIf (numPart = CurrencyChg) Then
            ' fraction unit found example "cent". End of the number in word
            bHasChange = True
            Exit For
         
         ElseIf (numPart = numberSeparator) Then
            ' is a valid string in number string but is not a number example "AND"
            'nothing
       
        Else
            MsgBox "Error. Do Not recognise '" & numPart & "' in '" & numInWords & "'"
            wordToDigits = ""
            Exit Function
            
        End If
        
    Next

    ' Add up numbers so far in the que
    myNumber = smallBucket + bigBucket
    
    If (Not bHasBill And Not bHasChange) Then
        ' did not encounter currecny unit or fraction unit
        myNumber = myNumber
        
    ElseIf (bHasBill And Not bHasChange) Then
        ' did encounter currecny unit but did not encounter fraction unit
        myNumber = preNum
    
    ElseIf (Not bHasBill And bHasChange) Then
        ' did not encounter currecny unit but did encounter fraction unit
        myNumber = objSmallNumber("zero") & CurrencyDelim & Right(objSmallNumber("zero") & objSmallNumber("zero") & myNumber, 2)
        
    Else
        ' default, encounter both currency unit and fraction unit
        myNumber = preNum & CurrencyDelim & Right(objSmallNumber("zero") & objSmallNumber("zero") & myNumber, 2)
    End If
    
    wordToDigits = CDbl(myNumber)

End Function
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 23, 2010 at 08:24 AM
This function below would convert words into digits


Public Function wordToDigits(numInWords As String) As Double

Dim objSmallNumber As Object ' dictionay objects
Dim objBigNumber As Object ' dictionary objects
Dim tempNum As String ' to lower case the variable passed

Dim numParts, numPart As Variant

Dim preNum As Double
Dim bigBucket As Double
Dim smallBucket As Double
Dim myNumber As Variant

Dim CurrencyBill As String ' currency unit. Leave blank if not needed
CurrencyBill = "dollars"

Dim CurrencyChg As String
CurrencyChg = "cents" ' currency unit. Leave blank if not needed

Dim CurrencyDelim As String ' delimited between currency unit. Leave blank if not needed
CurrencyDelim = "."

Set objSmallNumber = CreateObject("Scripting.Dictionary")
objSmallNumber.Add "zero", 0
objSmallNumber.Add "one", 1
objSmallNumber.Add "two", 2
objSmallNumber.Add "three", 3
objSmallNumber.Add "four", 4
objSmallNumber.Add "five", 5
objSmallNumber.Add "six", 6
objSmallNumber.Add "seven", 7
objSmallNumber.Add "eight", 8
objSmallNumber.Add "nine", 9
objSmallNumber.Add "ten", 10
objSmallNumber.Add "eleven", 11
objSmallNumber.Add "twelve", 12
objSmallNumber.Add "thirteen", 13
objSmallNumber.Add "fourteen", 14
objSmallNumber.Add "fifteen", 15
objSmallNumber.Add "sixteen", 16
objSmallNumber.Add "seventeen", 17
objSmallNumber.Add "eighteen", 18
objSmallNumber.Add "nineteen", 19
objSmallNumber.Add "twenty", 20
objSmallNumber.Add "thirty", 30
objSmallNumber.Add "forty", 40
objSmallNumber.Add "fifty", 50
objSmallNumber.Add "sixty", 60
objSmallNumber.Add "seventy", 70
objSmallNumber.Add "eighty", 80
objSmallNumber.Add "ninety", 90

Set objBigNumber = CreateObject("Scripting.Dictionary")
objBigNumber.Add "hundred", 100
objBigNumber.Add "thousand", 1000
objBigNumber.Add "million", 1000000
objBigNumber.Add "billion", 1000000000

tempNum = LCase(numInWords)

numParts = Split(tempNum, " ")

bigBucket = 0
smallBucket = 0
preNum = 0

For Each numPart In numParts
numPart = Trim(numPart)


If (objSmallNumber.exists(numPart)) Then

smallBucket = smallBucket + objSmallNumber(numPart)
tempNum = ""

ElseIf (objBigNumber.exists(numPart)) Then

bigBucket = bigBucket + smallBucket * objBigNumber(numPart)
smallBucket = 0
tempNum = ""

ElseIf ((numPart = "and") Or (numPart = "")) Then
'nothing

ElseIf (numPart = CurrencyBill) Then
preNum = smallBucket + bigBucket

smallBucket = 0
bigBucket = 0

ElseIf (numPart = CurrencyChg) Then
'end of story
Exit For
Else

wordToDigits = ""
Exit Function

End If

Next

myNumber = smallBucket + bigBucket

If preNum <> 0 Then
If myNumber <> "" Then
myNumber = preNum & CurrencyDelim & myNumber
Else
myNumber = preNum

End If

End If


wordToDigits = CDbl(myNumber)


End Function
-1