Convert numbers into hundreds, thousands & millions

[Expert Opinion]

Hi,
Need your help in code which will convert the numbers into Hundreds, thousands and millions upon selecting option in sheet1. Formatting or coding should be applicable in all the sheets (upcoming new sheets too) and only numbers to be converted and not dates. I have attached a sample file for your review where format is not same for two sheets and same will be the case for new worksheets too in case added.

Please help me on this.



Thanks

 

[Joe4]

It isn't quite clear what you want to do based on your one sample image.
How do you know how the numbers are to be converted?
What indicates if those numbers represent hundreds, thousands, or millions?

 

[Expert Opinion]

Hi, Thanks for taking time out to check this.

I have added radio button in the another sheet and upon selection, I would like to use the code which will change the number to shortest way possible..i.e. 100,000 is 100 (thousand conversion) and so on and so forth. I am unable to attach file here hence pasted screenshot of problem area.

Thanks

 

[Joe4]

Well, since you did not show the sheet where that selection happens, I cannot program specific to that. But here is the general structure that does most of what you want. You will just need to fill in the details and make edits where necessary.

Sub MyFormat()

    Dim wsF As Worksheet, wsD As Worksheet
    Dim factor As String
    Dim mult As Long
    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
'   Set worksheet where factor is indicated
    Set wsF = Sheets("Sheet1")
'   Set worksheet were data is located
    Set wsD = Sheets("Sheet2")

'   Get factor from first Sheet
    factor = wsF.Range("A1")
    
'   Determine multiplier
    Select Case factor
        Case "Hundreds"
            mult = 100
        Case "Thousands"
            mult = 1000
        Case "Millions"
            mult = 1000000
        Case Else
            MsgBox "No valid multiplier selected", vbOKOnly, "ERROR!"
            Exit Sub
    End Select

'   Go to data sheet amd set data range
    wsD.Activate
    Set rng = wsD.UsedRange
    
'   Loop through range and update numbers
    For Each cell In rng
        If IsNumeric(cell) And Len(cell) > 0 Then cell.Value = cell.Value * mult
    Next cell
        
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete", vbOKOnly

End Sub

 

[Expert Opinion]

Hi Joe,

This works to an extent for one of the worksheets. Only change will be in formula will be Cell.Value/Mult. Also can we add any code for changing the same to original number post changing it to hundreds or thousands. - Please guide.

Also this covers only one of the worksheets but I want to apply it to all worksheets (20 worksheets - where numbers are there) except main worksheet. Is this possible please?

Please suggest.

 

[kvsrinivasamurthy]

Try this UDF code

Function NumInHundredsThousandsMillions(IpNum As Long)
If IpNum > 999999999 Then NumInHundredsThousandsMillions = "ERROR": Exit Function

Dim TempNum As Long
Dim Num(1 To 3)
Dim Dig1, Dig2, Pos
Dim IpnumTxt As String, StrHun As String, Str2digit As String, TempStr As String
Dim T As Long
Dig1 = Array("", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ", _
        "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Senenteen ", "Eighteen ", "nineteen ")

Dig2 = Array("", "", "twenty ", "thirty ", "fourty ", "fifty ", "sixty ", "seventy ", "eighty ", "ninety ")

Pos = Array("", "Million ", "Thousand ", "")

Num(1) = Int(IpNum / 1000000)
Num(2) = Int((IpNum - Num(1) * 1000000) / 1000)
Num(3) = IpNum Mod 1000

For T = 1 To 3
 If Num(T) > 0 Then
 If Len(Num(T)) = 3 Then StrHun = Dig1(Int(Num(T) / 100)) & "Hundred "
 Num(T) = Right(Num(T), 2) + 0
 Str2digit = Dig2(Int(Num(T) / 10)) & Dig1(Num(T) Mod 10)
 TempStr = TempStr & StrHun & Str2digit & Pos(T)
 End If
Next T
NumInHundredsThousandsMillions = TempStr
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
Save file as .xlsm

 

[Expert Opinion]

T

Try this UDF code

Function NumInHundredsThousandsMillions(IpNum As Long)
If IpNum > 999999999 Then NumInHundredsThousandsMillions = "ERROR": Exit Function

Dim TempNum As Long
Dim Num(1 To 3)
Dim Dig1, Dig2, Pos
Dim IpnumTxt As String, StrHun As String, Str2digit As String, TempStr As String
Dim T As Long
Dig1 = Array("", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ", _
        "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Senenteen ", "Eighteen ", "nineteen ")

Dig2 = Array("", "", "twenty ", "thirty ", "fourty ", "fifty ", "sixty ", "seventy ", "eighty ", "ninety ")

Pos = Array("", "Million ", "Thousand ", "")

Num(1) = Int(IpNum / 1000000)
Num(2) = Int((IpNum - Num(1) * 1000000) / 1000)
Num(3) = IpNum Mod 1000

For T = 1 To 3
 If Num(T) > 0 Then
 If Len(Num(T)) = 3 Then StrHun = Dig1(Int(Num(T) / 100)) & "Hundred "
 Num(T) = Right(Num(T), 2) + 0
 Str2digit = Dig2(Int(Num(T) / 10)) & Dig1(Num(T) Mod 10)
 TempStr = TempStr & StrHun & Str2digit & Pos(T)
 End If
Next T
NumInHundredsThousandsMillions = TempStr
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
Save file as .xlsm

This UDF function is made available in Excel post adding this code in macro however this will not help us automate things basis our selection I believe. Am I missing something here?

Thanks

 

[kvsrinivasamurthy]

This is to be used like other excel functions. Say A2 you are having number. B2 you want display in words. In B2 formula is
=NumInHundredsThousandsMillions(A2).
Formula can be used any where in workbook. Pasting the code I have given already.

 

[Expert Opinion]

This is to be used like other excel functions. Say A2 you are having number. B2 you want display in words. In B2 formula is
=NumInHundredsThousandsMillions(A2).
Formula can be used any where in workbook. Pasting the code I have given already.

Yes that I understood but we need dynamic conversion on existing cells as more columns and new worksheets will be added in future.

 

[kvsrinivasamurthy]

Where the results should be.