Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / NewStats: 3,195,599 members, 7,958,840 topics. Date: Thursday, 26 September 2024 at 04:59 AM |
Nairaland Forum / Science/Technology / Programming / How To Convert Numbers To Words In Excel (18877 Views)
Who Build A Cash Daily Buying And Selling Programm In Excel / Querying Documents In Excel Into Html??? / How To Convert Folder/file To Jpeg Format (2) (3) (4)
How To Convert Numbers To Words In Excel by brianromel(m): 12:12pm On Jul 16, 2014 |
For any one using excel constantly, you find that most calculations you make in your day to day work ends with writing the figures in words for an invoice or quotation. Usually everyone develops a format for easy work where you recopy and change the digits on the format, with working calculations already in it to make the job more fasted, particularly if your an accountant with lots of invoices and quotations to do with lots of deadlines to meet. The formulas make things faster. Now havent you ever said, i wish there was a formular to convert the numbers to digits that i could just paste and hey presto its done. Now there is and its easy to do. Here is how. So why you have an excel document open enter Alt + F11 This will take you to the macro's VBS workstation of the excel sheet you are. Here is where you need to put in a certain code. First press insert in the insert tab of the VBS workstation and select Macro now past the code below into it Function SpellNumberToEnglish(ByVal pNumber) 'Updateby20131113 Dim Dollars, Cents arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion " pNumber = Trim(Str(pNumber)) xDecimal = InStr(pNumber, "." If xDecimal > 0 Then Cents = GetTens(Left(Mid(pNumber, xDecimal + 1) & "00", 2)) pNumber = Trim(Left(pNumber, xDecimal - 1)) End If xIndex = 1 Do While pNumber <> "" xHundred = "" xValue = Right(pNumber, 3) If Val(xValue) <> 0 Then xValue = Right("000" & xValue, 3) If Mid(xValue, 1, 1) <> "0" Then xHundred = GetDigit(Mid(xValue, 1, 1)) & " Hundred " End If If Mid(xValue, 2, 1) <> "0" Then xHundred = xHundred & GetTens(Mid(xValue, 2)) Else xHundred = xHundred & GetDigit(Mid(xValue, 3)) End If End If If xHundred <> "" Then Dollars = xHundred & arr(xIndex) & Dollars End If If Len(pNumber) > 3 Then pNumber = Left(pNumber, Len(pNumber) - 3) Else pNumber = "" End If xIndex = xIndex + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumberToEnglish = Dollars & Cents End Function Function GetTens(pTens) Dim Result As String Result = "" If Val(Left(pTens, 1)) = 1 Then Select Case Val(pTens) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else Select Case Val(Left(pTens, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & GetDigit(Right(pTens, 1)) End If GetTens = Result End Function Function GetDigit(pDigit) Select Case Val(pDigit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function Once pasted. Go back to your document your working on, and enter this where you want to type your "Total in words:" "=SpellNumberToEnglish()" (note without the quotation mark). in the bracket after english place you mouse pointer, then select the cell where the number you want to change to words is. press enter and presto its changed it converts it to words. Now you are say it converts it to Dollar and cent. I cant use this. Hold on, I will attach the code for it to convert to Naira and kobo for you below. (You know you could ask one of the programmers on this sectons nicely to do it for you.) You an accountant or are you always working with excel and are facing problems u need solutions to. please drop a line here. The geek squad will look into it. 1 Like
|
Re: How To Convert Numbers To Words In Excel by Mdolalekan2u(m): 10:22pm On Nov 26, 2015 |
pls need the one in naira and kobo that can convert numbers into words correctly eg 1,005,479.57 = One Million and Five Thousand, Four Hundred and Seventy Nine Naira Fifty Seven Kobo. with 'and' in plcce of zero as above. |
Re: How To Convert Numbers To Words In Excel by brianromel(m): 5:32pm On May 24, 2016 |
Mdolalekan2u: Where you see Dollar written, change it to Naira. Where you see Cent written change it to Kobo. |
Re: How To Convert Numbers To Words In Excel by Mdolalekan2u(m): 8:52pm On Jun 05, 2016 |
The major challenge is to convert '0' to 'and' where necessary. Any guru in house to solve this? |
Re: How To Convert Numbers To Words In Excel by okonja(m): 2:33pm On May 19, 2017 |
Option Explicit I figured it out. |
Re: How To Convert Numbers To Words In Excel by KenTimos: 1:55pm On Feb 19, 2020 |
Just a little modification Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Naira, Kobo, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, "." ' Convert Kobo and set MyNumber to dollar amount. If DecimalPlace > 0 Then Kobo = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Naira = Temp & Place(Count) & Naira If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Naira Case "" Naira = "No Naira" Case "One" Naira = "One Dollar" Case Else Naira = Naira & " Naira" End Select Select Case Kobo Case "" Kobo = " Only" Case "One" Kobo = " One Kobo Only " Case Else Kobo = " " & Kobo & " Kobo Only " End Select SpellNumber = Naira & Kobo End Function ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) ' Convert the hundreds place. If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and " End If ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' Converts a number from 10 to 99 into text. Function GetTens(TensText) Dim Result As String Result = "" ' Null out the temporary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19... Select Case Val(TensText) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' If value between 20-99... Select Case Val(Left(TensText, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & GetDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function 2 Likes |
(1) (Reply)
Is Python Really Worth The Buzz And Hype It's Getting ? / Things you need to know as an upcoming programmer and MY CODING DAIRY / Programming Competition: Search Engine Task. Rewards up for grabs.
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 44 |