• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Customized Conversion Indian Currency to Words With Excel VBA (Request)|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Customized Conversion Indian Currency to Words With Excel VBA (Request)|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosCustomized Conversion Indian Curren…
sp_PrintTopic sp_TopicIcon
Customized Conversion Indian Currency to Words With Excel VBA (Request)
Avatar
A Ray

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
July 24, 2019
sp_UserOfflineSmall Offline
1
July 24, 2019 - 12:09 am
sp_Permalink sp_Print

Can you please make a code for Indian Rupees in Whole numbers? Please note that Trillion, Million and Billion is not used in Indian Currency system. Comma for separation is also used in a different manner.

For example the number 1234567899000 in Rupees is written as ₹ 123456,78,99,000 in short and Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six Crore Seventy Eight Lakh Ninety Nine Thousand. Use of the word ‘Only’ at the end has become obsolete now-a-days.

Can you please post a code which converts ₹ 123456,78,99,000 to Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six Crore Seventy Eight Lakh Ninety Nine Thousand in words. The suffix 'only' is not needed.

Thanks in advance and Regards

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
July 26, 2019 - 1:33 am
sp_Permalink sp_Print sp_EditHistory

Hi Ray,

There are already a few codes for that conversion. You should try them, it's much easier than writing a similar code from scratch, the codes to convert normal numbers to words cannot be converted to rupees.

http://www.freevbcode.com/Show.....sp?ID=7814

https://excel-macro.tutorialho.....into-text/

Looks like there is already an adapted version of Phil's code here: https://exceldatapro.com/spell.....an-rupees/

Without a reference to the original code creator, of course...

Avatar
A Ray

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
July 24, 2019
sp_UserOfflineSmall Offline
3
July 27, 2019 - 5:09 am
sp_Permalink sp_Print sp_EditHistory

Hi Bomea,

It's really a great help. Thanks a lot. The code in the second link seems a close match. But unfortunately I do not want it with a conversion button. I liked the way Phil's code do, i.e. when I put the number in column A, the converted words will appear in column B. I don't know VBA coding. Can you please modify the following code so that there is no convert button and no 'Main Page' or 'Sheets' issue. It will  be as easy as putting the number in any cell in column A and get the result in corresponding cell in column B? Regards.

Sub sumit()

Dim mainWorkBook

Set mainWorkBook = ActiveWorkbook

intRows = mainWorkBook.Sheets("Main").UsedRange.Rows.Count
'MsgBox intRows
For i = 1 To intRows
intValue = mainWorkBook.Sheets("Main").Range("A" & i)
If intValue <> "" Then
mainWorkBook.Sheets("Main").Range("B" & i) = FnConvert(intValue)
End If
Next

 

End Sub
Function FnConvert(strNumber)

blnDecimalExist = False
strNumber = CStr(strNumber)

If InStr(1, strNumber, ".", vbTextCompare) > 0 Then
arrSplit = Split(strNumber, ".")
strNumber = arrSplit(0)
strDecimal = arrSplit(1)

If Len(strDecimal) > 2 Then
strDecimal = Mid(strDecimal, 0, 2)
End If

If Len(strDecimal) > 0 And Len(strDecimal) < 2 Then
strDecimalConversion = FnGetUnitDigit(strDecimal)
End If
If Len(strDecimal) > 1 And Len(strDecimal) < 3 Then
strDecimalConversion = FnGetTensDigit(strDecimal)
End If

blnDecimalExist = True

End If

If Len(strNumber) > 0 And Len(strNumber) < 2 Then
strTextConversion = FnGetUnitDigit(strNumber)
End If
If Len(strNumber) > 1 And Len(strNumber) < 3 Then
strTextConversion = FnGetTensDigit(strNumber)
End If
If Len(strNumber) > 2 And Len(strNumber) < 4 Then
strTextConversion = FnGetHundreds(strNumber)
End If
If Len(strNumber) > 3 And Len(strNumber) < 6 Then
If Len(strNumber) = 4 Then
strTextConversion = FnGetThousandsOne(strNumber)
End If
If Len(strNumber) = 5 Then
strTextConversion = FnGetThousandsTwo(strNumber)
End If
End If
If Len(strNumber) > 5 And Len(strNumber) < 8 Then
If Len(strNumber) = 6 Then
strTextConversion = FnGetLacsOne(strNumber)
End If
If Len(strNumber) = 7 Then
strTextConversion = FnGetLacsTwo(strNumber)
End If
End If
If Len(strNumber) > 7 And Len(strNumber) < 15 Then
If Len(strNumber) = 8 Then
strTextConversion = FnGetCroreOne(strNumber)
End If
If Len(strNumber) = 9 Then
strTextConversion = FnGetCroreTwo(strNumber)
End If
If Len(strNumber) = 10 Then
strTextConversion = FnGetCroreThree(strNumber)
End If
If Len(strNumber) = 11 Then
strTextConversion = FnGetCroreFour(strNumber)
End If
If Len(strNumber) = 12 Then
strTextConversion = FnGetCroreFive(strNumber)
End If
If Len(strNumber) = 13 Then
strTextConversion = FnGetCroreSix(strNumber)
End If
If Len(strNumber) = 14 Then
strTextConversion = FnGetCroreSeven(strNumber)
End If
End If

If blnDecimalExist Then
strTextConversion = "Rupees " & strTextConversion & " and " & strDecimalConversion & " paise only"
Else
strTextConversion = "Rupees " & strTextConversion
End If
FnConvert = strTextConversion
End Function
Function FnGetCroreSeven(intN)
Dim Str

'temp = FnGetTensDigit(Left(intN, 3))
'If temp <> "" Then
Str = FnGetLacsTwo(Left(intN, 7)) & " Crores " & FnGetLacsTwo(Right(intN, Len(intN) - 7))
'Else
' Str = FnGetLacsTwo(Right(intN, Len(intN) - 3))
'End If
FnGetCroreSeven = Str
End Function

Function FnGetCroreSix(intN)
Dim Str

'temp = FnGetTensDigit(Left(intN, 3))
'If temp <> "" Then
Str = FnGetLacsOne(Left(intN, 6)) & " Crores " & FnGetLacsTwo(Right(intN, Len(intN) - 6))
'Else
' Str = FnGetLacsTwo(Right(intN, Len(intN) - 3))
'End If
FnGetCroreSix = Str
End Function

Function FnGetCroreFive(intN)
Dim Str

'temp = FnGetTensDigit(Left(intN, 3))
'If temp <> "" Then
Str = FnGetThousandsTwo(Left(intN, 5)) & " Crores " & FnGetLacsTwo(Right(intN, Len(intN) - 5))
'Else
' Str = FnGetLacsTwo(Right(intN, Len(intN) - 3))
'End If
FnGetCroreFive = Str
End Function

Function FnGetCroreFour(intN)
Dim Str

'temp = FnGetTensDigit(Left(intN, 3))
'If temp <> "" Then
Str = FnGetThousandsOne(Left(intN, 4)) & " Crores " & FnGetLacsTwo(Right(intN, Len(intN) - 4))
'Else
' Str = FnGetLacsTwo(Right(intN, Len(intN) - 3))
'End If
FnGetCroreFour = Str
End Function

Function FnGetCroreThree(intN)
Dim Str

'temp = FnGetTensDigit(Left(intN, 3))
'If temp <> "" Then
Str = FnGetHundreds(Left(intN, 3)) & " Crores " & FnGetLacsTwo(Right(intN, Len(intN) - 3))
'Else
' Str = FnGetLacsTwo(Right(intN, Len(intN) - 3))
'End If
FnGetCroreThree = Str
End Function

Function FnGetCroreTwo(intN)
Dim Str

temp = FnGetTensDigit(Left(intN, 2))
If temp <> "" Then
Str = FnGetTensDigit(Left(intN, 2)) & " Crores " & FnGetLacsTwo(Right(intN, Len(intN) - 2))
Else
Str = FnGetLacsTwo(Right(intN, Len(intN) - 2))
End If

FnGetCroreTwo = Str
End Function

Function FnGetCroreOne(intN)
Dim Str

temp = FnGetUnitDigit(Left(intN, 1))
If temp <> "" Then
Str = FnGetUnitDigit(Left(intN, 1)) & " Crore " & FnGetLacsTwo(Right(intN, Len(intN) - 1))
Else
Str = FnGetLacsTwo(Right(intN, Len(intN) - 1))
End If

FnGetCroreOne = Str
End Function
Function FnGetLacsTwo(intN)
Dim Str

temp = FnGetTensDigit(Left(intN, 2))
If temp <> "" Then
Str = FnGetTensDigit(Left(intN, 2)) & " Lacs " & FnGetThousandsTwo(Right(intN, Len(intN) - 2))
Else
Str = FnGetThousandsTwo(Right(intN, Len(intN) - 2))
End If

FnGetLacsTwo = Str
End Function
Function FnGetLacsOne(intN)
Dim Str
'Str = FnGetUnitDigit(Left(intN, 1)) & " Lac " & FnGetThousandsTwo(Right(intN, Len(intN) - 1))

temp = FnGetUnitDigit(Left(intN, 1))
If temp <> "" Then
Str = FnGetUnitDigit(Left(intN, 1)) & " Lac " & FnGetThousandsTwo(Right(intN, Len(intN) - 1))
Else
Str = FnGetThousandsTwo(Right(intN, Len(intN) - 1))
End If

FnGetLacsOne = Str
End Function
Function FnGetThousandsTwo(intN)
Dim Str
'Str = FnGetTensDigit(Left(intN, 2)) & " Thousand " & FnGetHundreds(Right(intN, Len(intN) - 2))

temp = FnGetTensDigit(Left(intN, 2))
If temp <> "" Then
Str = FnGetTensDigit(Left(intN, 2)) & " Thousand " & FnGetHundreds(Right(intN, Len(intN) - 2))
Else
Str = FnGetHundreds(Right(intN, Len(intN) - 2))
End If

FnGetThousandsTwo = Str
End Function
Function FnGetThousandsOne(intN)
Dim Str
'Str = FnGetUnitDigit(Left(intN, 1)) & " Thousand " & FnGetHundreds(Right(intN, Len(intN) - 1))

temp = FnGetUnitDigit(Left(intN, 1))
If temp <> "" Then
Str = FnGetUnitDigit(Left(intN, 1)) & " Thousand " & FnGetHundreds(Right(intN, Len(intN) - 1))
Else
Str = FnGetHundreds(Right(intN, Len(intN) - 1))
End If

FnGetThousandsOne = Str
End Function
Function FnGetHundreds(intN)
Dim Str
temp = FnGetUnitDigit(Left(intN, 1))
If temp <> "" Then
Str = FnGetUnitDigit(Left(intN, 1)) & " Hundred " & FnGetTensDigit(Right(intN, 2))
Else
Str = FnGetTensDigit(Right(intN, 2))
End If

FnGetHundreds = Trim(Str)
End Function
Function FnGetTensDigit(intN)
Dim Str
If Left(intN, 1) = 1 Then
Select Case Val(intN)
Case 10: Str = "Ten"
Case 11: Str = "Eleven"
Case 12: Str = "Twelve"
Case 13: Str = "Thirteen"
Case 14: Str = "Fourteen"
Case 15: Str = "Fifteen"
Case 16: Str = "Sixteen"
Case 17: Str = "Seventeen"
Case 18: Str = "Eighteen"
Case 19: Str = "Nineteen"
End Select
Else
Select Case Val(Left(intN, 1))
Case 2: Str = "Twenty"
Case 3: Str = "Thirty"
Case 4: Str = "Fourty"
Case 5: Str = "Fifty"
Case 6: Str = "Sixty"
Case 7: Str = "Seventy"
Case 8: Str = "Eighty"
Case 9: Str = "Ninty"
End Select

Str = Str & " " & FnGetUnitDigit(Right(intN, 1))
End If

FnGetTensDigit = Trim(Str)
End Function
Function FnGetUnitDigit(intN)

Dim Str

Select Case Val(intN)
Case 1: Str = "One"
Case 2: Str = "Two"
Case 3: Str = "Three"
Case 4: Str = "Four"
Case 5: Str = "Five"
Case 6: Str = "Six"
Case 7: Str = "Seven"
Case 8: Str = "Eight"
Case 9: Str = "Nine"
End Select
FnGetUnitDigit = Trim(Str)
End Function

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
July 27, 2019 - 1:59 pm
sp_Permalink sp_Print

You already have that. In column B, enter the function like this: =FnConvert(A1)

sp_AnswersTopicAnswer
Answers Post
Avatar
A Ray

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
July 24, 2019
sp_UserOfflineSmall Offline
5
July 28, 2019 - 12:21 am
sp_Permalink sp_Print

Thanks. I followed what you said and it's done.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Bhuwan Devkota
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.