Wednesday, March 3, 2021

एक्सेल में नंबर को वर्ड में लिखने के लिए | Excel tips to convert Number to word

एक्सेल में नंबर को वर्ड में लिखने के लिए | Excel tips to convert Number to word

अगर आप एक्सेल में कोई बिल बनाना है, और उस बिल में अमाउंट इन वर्ड्स लिखना है याने की बिल को जो भी अमाउंट है वो वर्ड में प्रिंट हो जाये तो बस निचे दिए गए स्टेप्स फॉलो कीजियेगा |
१) सबसे पहले एक एक्सेल फाइल ओपन कीजिये और उसके अंदर ALT + F11 प्रेस कीजिये |

२) इसके बाद आपके सामने एक्सेल VBA स्क्रीन आ जाएगी |

३) इस विंडो में नया MODULE  इन्सर्ट कीजिये |

४) निचे दिया गया कोड कॉपी कीजिये और उस MODULE में पेस्ट कीजिये |

Copy Below Program of VBA :

Function SpellNumber(ByVal N As Currency) As String
   Const Thousand = 1000@
   Const Million = Thousand * Thousand
   Const Billion = Thousand * Million
   Const Trillion = Thousand * Billion
   If (N = 0@) Then SpellNumber = “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 >= Trillion) Then
      Buf = Buf & SpellNumberDigitGroup(Int(N / Trillion)) & ” trillion”
      N = N – Int(N / Trillion) * Trillion
      If (N >= 1@) Then Buf = Buf & ” “
   End If
   If (N >= Billion) Then
      Buf = Buf & SpellNumberDigitGroup(Int(N / Billion)) & ” billion”
      N = N – Int(N / Billion) * Billion
      If (N >= 1@) Then Buf = Buf & ” “
   End If
   If (N >= Million) Then
      Buf = Buf & SpellNumberDigitGroup(N Million) & ” million”
      N = N Mod Million
      If (N >= 1@) Then Buf = Buf & ” “
   End If
   If (N >= Thousand) Then
      Buf = Buf & SpellNumberDigitGroup(N Thousand) & ” thousand”
      N = N Mod Thousand
      If (N >= 1@) Then Buf = Buf & ” “
   End If
   If (N >= 1@) Then
      Buf = Buf & SpellNumberDigitGroup(N)
   End If
   SpellNumber = Buf
End Function
Private Function SpellNumberDigitGroup(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
      SpellNumberDigitGroup = 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
      SpellNumberDigitGroup = 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
   SpellNumberDigitGroup = Buf
End Function
५) इस कोड को हमेशा एक्सेल फाइल में फिक्स बनाने के लिए निचे दिया गया वीडियो आप देख सकते है |

- Advertisement -

Related Articles

10 COMMENTS

  1. Dear sir,
    your VBA code does not working properly.

    When I type the formula "=SpellNumber(343890)" then showing this type result….

    "three hundred forty-three thousand eight hundred ninety".
    So, Kindly solve this problem.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay Connected

7,690FansLike
3,476FollowersFollow
853,982SubscribersSubscribe
- Advertisement -

Latest Articles