इस Excel Formula से आसानी से कन्वर्ट करें नंबर्स को इंडियन करेंसी (Rupees) के टेक्‍स्‍ट में

 

Excel में जब आप कोई Bill book, Cash memo, Check या Draft printing करते हो तो आपको वहां लिखे amount को अंकों के साथ साथ शब्‍दों में (in words) मेें भी टाइप करना होता है 

Indian rupees या Number को Words में Convert करने के लिये आपको सबसे पहले दिये गये फार्मूला काॅॅपी करना हाेगा

Function NumToWord(ByVal N As Currency) As String

   Const Ten = 10@

   Const Hundred = Ten * Ten

   Const Thousand = Ten * Hundred

   Const Lakh = Thousand * Hundred

   Const Crore = Lakh * Hundred

   Const Million = Thousand * Thousand

   Const Billion = Thousand * Million

   Const Trillion = Thousand * Billion

   If (N = 0@) Then NumToWord = "zero": Exit Function

   Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""

   Dim Frac As Currency: Frac = Abs(N - Fix(N))

   If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))

   Dim AtLeastOne As Integer: AtLeastOne = N >= 1

   If (N >= Crore) Then

      Buf = Buf & NumToWordDigitGroup(Int(N / Crore)) & " Crore"

      N = N - Int(N / Crore) * Crore

      If (N >= 1@) Then Buf = Buf & " "

   End If

   If (N >= Lakh) Then

      Buf = Buf & NumToWordDigitGroup(Int(N / Lakh)) & " Lakh"

      N = N - Int(N / Lakh) * Lakh

      If (N >= 1@) Then Buf = Buf & " "

   End If

   If (N >= Thousand) Then

      Buf = Buf & NumToWordDigitGroup(N \ Thousand) & " Thousand"

      N = N Mod Thousand

      If (N >= 1@) Then Buf = Buf & " "

   End If

   If (N >= Hundred) Then

      Buf = Buf & NumToWordDigitGroup(N \ Hundred) & " hundred"

      N = N Mod Hundred

      If (N >= 1@) Then Buf = Buf & " "

   End If

   If (N >= 1@) Then

      Buf = Buf & NumToWordDigitGroup(N)

   End If

   NumToWord = Buf

End Function

Private Function NumToWordDigitGroup(ByVal N As Integer) As String

   Const Hundred = " hundred"

   Const One = "one"

   Const Two = "two"

   Const Three = "three"

   Const Four = "four"

   Const Five = "five"

   Const Six = "six"

   Const Seven = "seven"

   Const Eight = "eight"

   Const Nine = "nine"

   Dim Buf As String: Buf = ""

   Dim Flag As Integer: Flag = False

   Select Case (N \ 100)

      Case 0: Buf = "": Flag = False

      Case 1: Buf = One & Hundred: Flag = True

      Case 2: Buf = Two & Hundred: Flag = True

      Case 3: Buf = Three & Hundred: Flag = True

      Case 4: Buf = Four & Hundred: Flag = True

      Case 5: Buf = Five & Hundred: Flag = True

      Case 6: Buf = Six & Hundred: Flag = True

      Case 7: Buf = Seven & Hundred: Flag = True

      Case 8: Buf = Eight & Hundred: Flag = True

      Case 9: Buf = Nine & Hundred: Flag = True

   End Select

   If (Flag <> False) Then N = N Mod 100

   If (N > 0) Then

      If (Flag <> False) Then Buf = Buf & " "

   Else

      NumToWordDigitGroup = Buf

      Exit Function

   End If

   Select Case (N \ 10)

      Case 0, 1: Flag = False

      Case 2: Buf = Buf & "twenty": Flag = True

      Case 3: Buf = Buf & "thirty": Flag = True

      Case 4: Buf = Buf & "forty": Flag = True

      Case 5: Buf = Buf & "fifty": Flag = True

      Case 6: Buf = Buf & "sixty": Flag = True

      Case 7: Buf = Buf & "seventy": Flag = True

      Case 8: Buf = Buf & "eighty": Flag = True

      Case 9: Buf = Buf & "ninety": Flag = True

   End Select

   If (Flag <> False) Then N = N Mod 10

   If (N > 0) Then

      If (Flag <> False) Then Buf = Buf & "-"

   Else

      NumToWordDigitGroup = Buf

      Exit Function

   End If

   Select Case (N)

      Case 0:

      Case 1: Buf = Buf & One

      Case 2: Buf = Buf & Two

      Case 3: Buf = Buf & Three

      Case 4: Buf = Buf & Four

      Case 5: Buf = Buf & Five

      Case 6: Buf = Buf & Six

      Case 7: Buf = Buf & Seven

      Case 8: Buf = Buf & Eight

      Case 9: Buf = Buf & Nine

      Case 10: Buf = Buf & "ten"

      Case 11: Buf = Buf & "eleven"

      Case 12: Buf = Buf & "twelve"

      Case 13: Buf = Buf & "thirteen"

      Case 14: Buf = Buf & "fourteen"

      Case 15: Buf = Buf & "fifteen"

      Case 16: Buf = Buf & "sixteen"

      Case 17: Buf = Buf & "seventeen"

      Case 18: Buf = Buf & "eighteen"

      Case 19: Buf = Buf & "nineteen"

   End Select

   NumToWordDigitGroup = Buf

    End Function

• फार्मूला काॅपी करने के बाद आप MS Excel ओपन करें और Alt+F11 प्रेस करें 
• ऐसा करने से Microsoft Visual Basic ओपन हो जायेगा
• यहां Insert मेन्‍यू पर जायें और module पर क्लिक करें 
• module में आपको एक Blank Page दिखाई देगा 
• जो फामूॅॅला आपने Microsoft की Site से Copy किया है उसे यहां Paste कर दीजिये 
• अब दोबारा से Alt+F11 प्रेस कीजिये और Excel पर जाईये 
• यहां किसी भी Call में कोई Number जैसे 100 टाइप कीजिये मान लीजिये यह Call A1 है 
• अब उसके सामने वाले Call में या जिस भी सेेेल मेें आप चाहें यह फार्मूला टाइप कीजिये
• =Numtoword(A1)और एंटर कीजिये