i am working with excel sheet. i want to add two numbers and want to display result both in number and text. if you add one and two the result will be three. i want to display result in both shape like in text and number together. please help me to solve my problem. thanks in advance
As a more complete reference than the links provided in the answer:
Option Explicit
Sub testNumbers()
MsgBox nrToWord(99) '-> "Ninety-Nine"
MsgBox nrToWord("three") '-> "3"
MsgBox wordToNr(21) '-> "Twenty-One"
MsgBox wordToNr("seventy five") '-> "75"
End Sub
'------------------------------------------------------------------------------------------
Helper functions:
Option Explicit
Private Const SP As String = "-" 'SeParator (dash)
Private Const ITM_00 As String = "zero"
Private Const ITM_01 As String = "one"
Private Const ITM_02 As String = "two"
Private Const ITM_03 As String = "three"
Private Const ITM_04 As String = "four"
Private Const ITM_05 As String = "five"
Private Const ITM_06 As String = "six"
Private Const ITM_07 As String = "seven"
Private Const ITM_08 As String = "eight"
Private Const ITM_09 As String = "nine"
Private Const ITM_TEEN As String = "teen"
Private Const ITM_11 As String = "eleven"
Private Const ITM_12 As String = "twelve"
Private Const ITM_13 As String = "thir" & ITM_TEEN
Private Const ITM_14 As String = ITM_04 & ITM_TEEN
Private Const ITM_15 As String = "fif" & ITM_TEEN
Private Const ITM_16 As String = ITM_06 & ITM_TEEN
Private Const ITM_17 As String = ITM_07 & ITM_TEEN
Private Const ITM_18 As String = "eigh" & ITM_TEEN
Private Const ITM_19 As String = "nine" & ITM_TEEN
Private Const ITM_10 As String = "ten"
Private Const ITM_20 As String = "twenty"
Private Const ITM_30 As String = "thirty"
Private Const ITM_40 As String = "forty"
Private Const ITM_50 As String = "fifty"
Private Const ITM_60 As String = "sixty"
Private Const ITM_70 As String = "seventy"
Private Const ITM_80 As String = "eighty"
Private Const ITM_90 As String = "ninety"
Private Const NR1000 As Long = 1000
Private Const LARGE_SET_1_01 As String = "hundred"
Private Const LARGE_SET_1_02 As String = "thousand"
Private Const LARGE_SET_2_01 As String = "million"
Private Const LARGE_SET_2_02 As String = "billion (milliard)"
Private Const LARGE_SET_2_03 As String = "trillion"
Private Const LARGE_SET_2_04 As String = "quadrillion"
Private Const LARGE_SET_2_05 As String = "quintillion"
Private Const LARGE_SET_2_06 As String = "sextillion"
Private Const LARGE_SET_2_07 As String = "septillion"
Private Const LARGE_SET_2_08 As String = "octillion"
Private Const LARGE_SET_2_09 As String = "nonillion"
Private Const LARGE_SET_2_10 As String = "decillion"
Private Const LARGE_NUM_2_01 As String = "1000000"
Private Const LARGE_NUM_2_02 As String = "1000000000"
Private Const LARGE_NUM_2_03 As String = "1000000000000"
Private Const LARGE_NUM_2_04 As String = "1000000000000000"
Private Const LARGE_NUM_2_05 As String = "1000000000000000000"
Private Const LARGE_NUM_2_06 As String = "1000000000000000000000"
Private Const LARGE_NUM_2_07 As String = "1000000000000000000000000"
Private Const LARGE_NUM_2_08 As String = "1000000000000000000000000000"
Private Const LARGE_NUM_2_09 As String = "1000000000000000000000000000000"
Private Const LARGE_NUM_2_10 As String = "1000000000000000000000000000000000"
Private Const LARGE_SET_3_01 As String = "undecillion"
Private Const LARGE_SET_3_02 As String = "duodecillion"
Private Const LARGE_SET_3_03 As String = "tredecillion"
Private Const LARGE_SET_3_04 As String = "quattuordecillion"
Private Const LARGE_SET_3_05 As String = "quindecillion"
Private Const LARGE_SET_3_06 As String = "sexdecillion (sedecillion)"
Private Const LARGE_SET_3_07 As String = "septendecillion"
Private Const LARGE_SET_3_08 As String = "octodecillion"
Private Const LARGE_SET_3_09 As String = "novemdecillion (novendecillion)"
Private Const LARGE_SET_3_10 As String = "vigintillion"
Private Const LARGE_SET_3_11 As String = "centillion"
'Private nrAsWords As Dictionary, wordsAsNr As Dictionary, nr(0 To 100) As Long
Private nrAsWords As Object, wordsAsNr As Object, nr(0 To 100) As Long
Sub testNumbers()
MsgBox nrToWord(99) '-> "Ninety-Nine"
MsgBox nrToWord("three") '-> "3"
MsgBox wordToNr("21") '-> "Twenty-One"
MsgBox wordToNr("seventy five") '-> "75"
End Sub
Public Function nrToWord(ByVal num As String, Optional propr As Boolean =True) As String
Dim result As String, nr As Variant
setNumberAndWordLists
nr = Val(num)
If IsNumeric(nr) And Val(nr) > 0 Then
result = nrAsWords(nr)
If propr Then
result = Replace(StrConv(Replace(result, SP, " "), vbProperCase), " ", SP)
End If
Else
result = wordsAsNr(Replace(num, " ", SP))
End If
nrToWord = result
End Function
Public Function wordToNr(ByVal txt As String) As String
wordToNr = nrToWord(txt)
End Function
Private Sub setNumberAndWordLists()
If nrAsWords Is Nothing Then
Dim i As Byte
For i = 0 To 100
nr(i) = i
Next
Set nrAsWords = CreateObject("Scripting.dictionary")
Set wordsAsNr = CreateObject("Scripting.dictionary")
nrAsWords.CompareMode = vbBinaryCompare 'case sensitive
wordsAsNr.CompareMode = vbTextCompare 'case insensitive
With nrAsWords
.Add Key:=nr(0), Item:=ITM_00
.Add Key:=nr(1), Item:=ITM_01
.Add Key:=nr(2), Item:=ITM_02
.Add Key:=nr(3), Item:=ITM_03
.Add Key:=nr(4), Item:=ITM_04
.Add Key:=nr(5), Item:=ITM_05
.Add Key:=nr(6), Item:=ITM_06
.Add Key:=nr(7), Item:=ITM_07
.Add Key:=nr(8), Item:=ITM_08
.Add Key:=nr(9), Item:=ITM_09
.Add Key:=nr(10), Item:=ITM_10
.Add Key:=nr(11), Item:=ITM_11
.Add Key:=nr(12), Item:=ITM_12
.Add Key:=nr(13), Item:=ITM_13
.Add Key:=nr(14), Item:=ITM_14
.Add Key:=nr(15), Item:=ITM_15
.Add Key:=nr(16), Item:=ITM_16
.Add Key:=nr(17), Item:=ITM_17
.Add Key:=nr(18), Item:=ITM_18
.Add Key:=nr(19), Item:=ITM_19
.Add Key:=nr(20), Item:=ITM_20
.Add Key:=nr(21), Item:=ITM_20 & SP & ITM_01
.Add Key:=nr(22), Item:=ITM_20 & SP & ITM_02
.Add Key:=nr(23), Item:=ITM_20 & SP & ITM_03
.Add Key:=nr(24), Item:=ITM_20 & SP & ITM_04
.Add Key:=nr(25), Item:=ITM_20 & SP & ITM_05
.Add Key:=nr(26), Item:=ITM_20 & SP & ITM_06
.Add Key:=nr(27), Item:=ITM_20 & SP & ITM_07
.Add Key:=nr(28), Item:=ITM_20 & SP & ITM_08
.Add Key:=nr(29), Item:=ITM_20 & SP & ITM_09
.Add Key:=nr(30), Item:=ITM_30
.Add Key:=nr(31), Item:=ITM_30 & SP & ITM_01
.Add Key:=nr(32), Item:=ITM_30 & SP & ITM_02
.Add Key:=nr(33), Item:=ITM_30 & SP & ITM_03
.Add Key:=nr(34), Item:=ITM_30 & SP & ITM_04
.Add Key:=nr(35), Item:=ITM_30 & SP & ITM_05
.Add Key:=nr(36), Item:=ITM_30 & SP & ITM_06
.Add Key:=nr(37), Item:=ITM_30 & SP & ITM_07
.Add Key:=nr(38), Item:=ITM_30 & SP & ITM_08
.Add Key:=nr(39), Item:=ITM_30 & SP & ITM_09
.Add Key:=nr(40), Item:=ITM_40
.Add Key:=nr(41), Item:=ITM_40 & SP & ITM_01
.Add Key:=nr(42), Item:=ITM_40 & SP & ITM_02
.Add Key:=nr(43), Item:=ITM_40 & SP & ITM_03
.Add Key:=nr(44), Item:=ITM_40 & SP & ITM_04
.Add Key:=nr(45), Item:=ITM_40 & SP & ITM_05
.Add Key:=nr(46), Item:=ITM_40 & SP & ITM_06
.Add Key:=nr(47), Item:=ITM_40 & SP & ITM_07
.Add Key:=nr(48), Item:=ITM_40 & SP & ITM_08
.Add Key:=nr(49), Item:=ITM_40 & SP & ITM_09
.Add Key:=nr(50), Item:=ITM_50
.Add Key:=nr(51), Item:=ITM_50 & SP & ITM_01
.Add Key:=nr(52), Item:=ITM_50 & SP & ITM_02
.Add Key:=nr(53), Item:=ITM_50 & SP & ITM_03
.Add Key:=nr(54), Item:=ITM_50 & SP & ITM_04
.Add Key:=nr(55), Item:=ITM_50 & SP & ITM_05
.Add Key:=nr(56), Item:=ITM_50 & SP & ITM_06
.Add Key:=nr(57), Item:=ITM_50 & SP & ITM_07
.Add Key:=nr(58), Item:=ITM_50 & SP & ITM_08
.Add Key:=nr(59), Item:=ITM_50 & SP & ITM_09
.Add Key:=nr(60), Item:=ITM_60
.Add Key:=nr(61), Item:=ITM_60 & SP & ITM_01
.Add Key:=nr(62), Item:=ITM_60 & SP & ITM_02
.Add Key:=nr(63), Item:=ITM_60 & SP & ITM_03
.Add Key:=nr(64), Item:=ITM_60 & SP & ITM_04
.Add Key:=nr(65), Item:=ITM_60 & SP & ITM_05
.Add Key:=nr(66), Item:=ITM_60 & SP & ITM_06
.Add Key:=nr(67), Item:=ITM_60 & SP & ITM_07
.Add Key:=nr(68), Item:=ITM_60 & SP & ITM_08
.Add Key:=nr(69), Item:=ITM_60 & SP & ITM_09
.Add Key:=nr(70), Item:=ITM_70
.Add Key:=nr(71), Item:=ITM_70 & SP & ITM_01
.Add Key:=nr(72), Item:=ITM_70 & SP & ITM_02
.Add Key:=nr(73), Item:=ITM_70 & SP & ITM_03
.Add Key:=nr(74), Item:=ITM_70 & SP & ITM_04
.Add Key:=nr(75), Item:=ITM_70 & SP & ITM_05
.Add Key:=nr(76), Item:=ITM_70 & SP & ITM_06
.Add Key:=nr(77), Item:=ITM_70 & SP & ITM_07
.Add Key:=nr(78), Item:=ITM_70 & SP & ITM_08
.Add Key:=nr(79), Item:=ITM_70 & SP & ITM_09
.Add Key:=nr(80), Item:=ITM_80
.Add Key:=nr(81), Item:=ITM_80 & SP & ITM_01
.Add Key:=nr(82), Item:=ITM_80 & SP & ITM_02
.Add Key:=nr(83), Item:=ITM_80 & SP & ITM_03
.Add Key:=nr(84), Item:=ITM_80 & SP & ITM_04
.Add Key:=nr(85), Item:=ITM_80 & SP & ITM_05
.Add Key:=nr(86), Item:=ITM_80 & SP & ITM_06
.Add Key:=nr(87), Item:=ITM_80 & SP & ITM_07
.Add Key:=nr(88), Item:=ITM_80 & SP & ITM_08
.Add Key:=nr(89), Item:=ITM_80 & SP & ITM_09
.Add Key:=nr(90), Item:=ITM_90
.Add Key:=nr(91), Item:=ITM_90 & SP & ITM_01
.Add Key:=nr(92), Item:=ITM_90 & SP & ITM_02
.Add Key:=nr(93), Item:=ITM_90 & SP & ITM_03
.Add Key:=nr(94), Item:=ITM_90 & SP & ITM_04
.Add Key:=nr(95), Item:=ITM_90 & SP & ITM_05
.Add Key:=nr(96), Item:=ITM_90 & SP & ITM_06
.Add Key:=nr(97), Item:=ITM_90 & SP & ITM_07
.Add Key:=nr(98), Item:=ITM_90 & SP & ITM_08
.Add Key:=nr(99), Item:=ITM_90 & SP & ITM_09
.Add Key:=nr(100), Item:=LARGE_SET_1_01
.Add Key:=NR1000, Item:=LARGE_SET_1_02
.Add Key:=LARGE_NUM_2_01, Item:=LARGE_SET_2_01 '1 & String(6, 0)
.Add Key:=LARGE_NUM_2_02, Item:=LARGE_SET_2_02 '1 & String(9, 0)
.Add Key:=LARGE_NUM_2_03, Item:=LARGE_SET_2_03 '1 & String(12, 0)
.Add Key:=LARGE_NUM_2_04, Item:=LARGE_SET_2_04 '1 & String(15, 0)
.Add Key:=LARGE_NUM_2_05, Item:=LARGE_SET_2_05 '1 & String(18, 0)
.Add Key:=LARGE_NUM_2_06, Item:=LARGE_SET_2_06 '1 & String(21, 0)
.Add Key:=LARGE_NUM_2_07, Item:=LARGE_SET_2_07 '1 & String(24, 0)
.Add Key:=LARGE_NUM_2_08, Item:=LARGE_SET_2_08 '1 & String(27, 0)
.Add Key:=LARGE_NUM_2_09, Item:=LARGE_SET_2_09 '1 & String(30, 0)
.Add Key:=LARGE_NUM_2_10, Item:=LARGE_SET_2_10 '1 & String(33, 0)
.Add Key:=LARGE_SET_3_01, Item:=LARGE_SET_3_01
.Add Key:=LARGE_SET_3_02, Item:=LARGE_SET_3_02
.Add Key:=LARGE_SET_3_03, Item:=LARGE_SET_3_03
.Add Key:=LARGE_SET_3_04, Item:=LARGE_SET_3_04
.Add Key:=LARGE_SET_3_05, Item:=LARGE_SET_3_05
.Add Key:=LARGE_SET_3_06, Item:=LARGE_SET_3_06
.Add Key:=LARGE_SET_3_07, Item:=LARGE_SET_3_07
.Add Key:=LARGE_SET_3_08, Item:=LARGE_SET_3_08
.Add Key:=LARGE_SET_3_09, Item:=LARGE_SET_3_09
.Add Key:=LARGE_SET_3_10, Item:=LARGE_SET_3_10
.Add Key:=LARGE_SET_3_11, Item:=LARGE_SET_3_11
End With
With wordsAsNr
.Add Key:=ITM_00, Item:=nr(0)
.Add Key:=ITM_01, Item:=nr(1)
.Add Key:=ITM_02, Item:=nr(2)
.Add Key:=ITM_03, Item:=nr(3)
.Add Key:=ITM_04, Item:=nr(4)
.Add Key:=ITM_05, Item:=nr(5)
.Add Key:=ITM_06, Item:=nr(6)
.Add Key:=ITM_07, Item:=nr(7)
.Add Key:=ITM_08, Item:=nr(8)
.Add Key:=ITM_09, Item:=nr(9)
.Add Key:=ITM_10, Item:=nr(10)
.Add Key:=ITM_11, Item:=nr(11)
.Add Key:=ITM_12, Item:=nr(12)
.Add Key:=ITM_13, Item:=nr(13)
.Add Key:=ITM_14, Item:=nr(14)
.Add Key:=ITM_15, Item:=nr(15)
.Add Key:=ITM_16, Item:=nr(16)
.Add Key:=ITM_17, Item:=nr(17)
.Add Key:=ITM_18, Item:=nr(18)
.Add Key:=ITM_19, Item:=nr(19)
.Add Key:=ITM_20, Item:=nr(20)
.Add Key:=ITM_20 & SP & ITM_01, Item:=nr(21)
.Add Key:=ITM_20 & SP & ITM_02, Item:=nr(22)
.Add Key:=ITM_20 & SP & ITM_03, Item:=nr(23)
.Add Key:=ITM_20 & SP & ITM_04, Item:=nr(24)
.Add Key:=ITM_20 & SP & ITM_05, Item:=nr(25)
.Add Key:=ITM_20 & SP & ITM_06, Item:=nr(26)
.Add Key:=ITM_20 & SP & ITM_07, Item:=nr(27)
.Add Key:=ITM_20 & SP & ITM_08, Item:=nr(28)
.Add Key:=ITM_20 & SP & ITM_09, Item:=nr(29)
.Add Key:=ITM_30, Item:=nr(30)
.Add Key:=ITM_30 & SP & ITM_01, Item:=nr(31)
.Add Key:=ITM_30 & SP & ITM_02, Item:=nr(32)
.Add Key:=ITM_30 & SP & ITM_03, Item:=nr(33)
.Add Key:=ITM_30 & SP & ITM_04, Item:=nr(34)
.Add Key:=ITM_30 & SP & ITM_05, Item:=nr(35)
.Add Key:=ITM_30 & SP & ITM_06, Item:=nr(36)
.Add Key:=ITM_30 & SP & ITM_07, Item:=nr(37)
.Add Key:=ITM_30 & SP & ITM_08, Item:=nr(38)
.Add Key:=ITM_30 & SP & ITM_09, Item:=nr(39)
.Add Key:=ITM_40, Item:=nr(40)
.Add Key:=ITM_40 & SP & ITM_01, Item:=nr(41)
.Add Key:=ITM_40 & SP & ITM_02, Item:=nr(42)
.Add Key:=ITM_40 & SP & ITM_03, Item:=nr(43)
.Add Key:=ITM_40 & SP & ITM_04, Item:=nr(44)
.Add Key:=ITM_40 & SP & ITM_05, Item:=nr(45)
.Add Key:=ITM_40 & SP & ITM_06, Item:=nr(46)
.Add Key:=ITM_40 & SP & ITM_07, Item:=nr(47)
.Add Key:=ITM_40 & SP & ITM_08, Item:=nr(48)
.Add Key:=ITM_40 & SP & ITM_09, Item:=nr(49)
.Add Key:=ITM_50, Item:=nr(50)
.Add Key:=ITM_50 & SP & ITM_01, Item:=nr(51)
.Add Key:=ITM_50 & SP & ITM_02, Item:=nr(52)
.Add Key:=ITM_50 & SP & ITM_03, Item:=nr(53)
.Add Key:=ITM_50 & SP & ITM_04, Item:=nr(54)
.Add Key:=ITM_50 & SP & ITM_05, Item:=nr(55)
.Add Key:=ITM_50 & SP & ITM_06, Item:=nr(56)
.Add Key:=ITM_50 & SP & ITM_07, Item:=nr(57)
.Add Key:=ITM_50 & SP & ITM_08, Item:=nr(58)
.Add Key:=ITM_50 & SP & ITM_09, Item:=nr(59)
.Add Key:=ITM_60, Item:=nr(60)
.Add Key:=ITM_60 & SP & ITM_01, Item:=nr(61)
.Add Key:=ITM_60 & SP & ITM_02, Item:=nr(62)
.Add Key:=ITM_60 & SP & ITM_03, Item:=nr(63)
.Add Key:=ITM_60 & SP & ITM_04, Item:=nr(64)
.Add Key:=ITM_60 & SP & ITM_05, Item:=nr(65)
.Add Key:=ITM_60 & SP & ITM_06, Item:=nr(66)
.Add Key:=ITM_60 & SP & ITM_07, Item:=nr(67)
.Add Key:=ITM_60 & SP & ITM_08, Item:=nr(68)
.Add Key:=ITM_60 & SP & ITM_09, Item:=nr(69)
.Add Key:=ITM_70, Item:=nr(70)
.Add Key:=ITM_70 & SP & ITM_01, Item:=nr(71)
.Add Key:=ITM_70 & SP & ITM_02, Item:=nr(72)
.Add Key:=ITM_70 & SP & ITM_03, Item:=nr(73)
.Add Key:=ITM_70 & SP & ITM_04, Item:=nr(74)
.Add Key:=ITM_70 & SP & ITM_05, Item:=nr(75)
.Add Key:=ITM_70 & SP & ITM_06, Item:=nr(76)
.Add Key:=ITM_70 & SP & ITM_07, Item:=nr(77)
.Add Key:=ITM_70 & SP & ITM_08, Item:=nr(78)
.Add Key:=ITM_70 & SP & ITM_09, Item:=nr(79)
.Add Key:=ITM_80, Item:=nr(80)
.Add Key:=ITM_80 & SP & ITM_01, Item:=nr(81)
.Add Key:=ITM_80 & SP & ITM_02, Item:=nr(82)
.Add Key:=ITM_80 & SP & ITM_03, Item:=nr(83)
.Add Key:=ITM_80 & SP & ITM_04, Item:=nr(84)
.Add Key:=ITM_80 & SP & ITM_05, Item:=nr(85)
.Add Key:=ITM_80 & SP & ITM_06, Item:=nr(86)
.Add Key:=ITM_80 & SP & ITM_07, Item:=nr(87)
.Add Key:=ITM_80 & SP & ITM_08, Item:=nr(88)
.Add Key:=ITM_80 & SP & ITM_09, Item:=nr(89)
.Add Key:=ITM_90, Item:=nr(90)
.Add Key:=ITM_90 & SP & ITM_01, Item:=nr(91)
.Add Key:=ITM_90 & SP & ITM_02, Item:=nr(92)
.Add Key:=ITM_90 & SP & ITM_03, Item:=nr(93)
.Add Key:=ITM_90 & SP & ITM_04, Item:=nr(94)
.Add Key:=ITM_90 & SP & ITM_05, Item:=nr(95)
.Add Key:=ITM_90 & SP & ITM_06, Item:=nr(96)
.Add Key:=ITM_90 & SP & ITM_07, Item:=nr(97)
.Add Key:=ITM_90 & SP & ITM_08, Item:=nr(98)
.Add Key:=ITM_90 & SP & ITM_09, Item:=nr(99)
.Add Key:=LARGE_SET_1_01, Item:=nr(100)
.Add Key:=LARGE_SET_1_02, Item:=NR1000
.Add Key:=LARGE_SET_2_01, Item:=LARGE_NUM_2_01 '1,000,000
.Add Key:=LARGE_SET_2_02, Item:=LARGE_NUM_2_02 '1,000,000,000
.Add Key:=LARGE_SET_2_03, Item:=LARGE_NUM_2_03 '1,000,000,000,000
.Add Key:=LARGE_SET_2_04, Item:=LARGE_NUM_2_04 '1,000,000,000,000,000
.Add Key:=LARGE_SET_2_05, Item:=LARGE_NUM_2_05 '1,000,000,000,000,000,000
.Add Key:=LARGE_SET_2_06, Item:=LARGE_NUM_2_06
.Add Key:=LARGE_SET_2_07, Item:=LARGE_NUM_2_07
.Add Key:=LARGE_SET_2_08, Item:=LARGE_NUM_2_08
.Add Key:=LARGE_SET_2_09, Item:=LARGE_NUM_2_09
.Add Key:=LARGE_SET_2_10, Item:=LARGE_NUM_2_10
.Add Key:=LARGE_SET_3_01, Item:=LARGE_SET_3_01
.Add Key:=LARGE_SET_3_02, Item:=LARGE_SET_3_02
.Add Key:=LARGE_SET_3_03, Item:=LARGE_SET_3_03
.Add Key:=LARGE_SET_3_04, Item:=LARGE_SET_3_04
.Add Key:=LARGE_SET_3_05, Item:=LARGE_SET_3_05
.Add Key:=LARGE_SET_3_06, Item:=LARGE_SET_3_06
.Add Key:=LARGE_SET_3_07, Item:=LARGE_SET_3_07
.Add Key:=LARGE_SET_3_08, Item:=LARGE_SET_3_08
.Add Key:=LARGE_SET_3_09, Item:=LARGE_SET_3_09
.Add Key:=LARGE_SET_3_10, Item:=LARGE_SET_3_10
.Add Key:=LARGE_SET_3_11, Item:=LARGE_SET_3_11
End With
End If
End Sub
'------------------------------------------------------------------------------------------