Previous Topic Next Topic

Converting numbers to words in excell

Closed
Edwardian - Sep 3, 2009 at 03:32 AM
 Saji - Jul 8, 2010 at 03:18 AM
Hello,
Can anyone pls gimme a code that will help me convert numbers/figures entered in an excell worksheet to words? eg 70,000 to be converted to seventy thousand naira only.
Thanks.

5 responses

Answer 1 / 5
Saji
Jul 8, 2010 at 03:14 AM
Function English(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 English = "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
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " Trillion"
N = N - Int(N / Trillion) * Trillion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Billion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " Billion"
N = N - Int(N / Billion) * Billion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Million) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Million) & " Million"
N = N Mod Million
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Thousand) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Thousand) & " Thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= 1@) Then
Buf = " (BD:- "
Debug.Print N
Buf = Buf & EnglishDigitGroup(N)
End If

If (Frac = 0@) Then
Buf = Buf & " Only.)"
ElseIf (Int(Frac * 1000@) = Frac * 1000@) Then
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 1000@, "000") & "/1000 Only.)"
Else
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & " "
End If


English = Buf
End Function



Private Function EnglishDigitGroup(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
EnglishDigitGroup = 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
EnglishDigitGroup = 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

EnglishDigitGroup = Buf

End Function
21
Saji
Jul 8, 2010 at 03:18 AM
You Just add the function into your Module In(Press Alt+F11) Excell Sheet, Then Save the file and try to run.
0
Answer 2 / 5
saji
Jun 13, 2010 at 09:26 AM
Convert Numbers to Words in Excell
3
Answer 3 / 5
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Sep 4, 2009 at 02:26 PM
have a look here
microsoft have an example
https://support.microsoft.com/en-us/office/convert-numbers-into-words-a0d166fb-e1ea-4090-95c8-69442cd55d98?ui=en-us&rs=en-us&ad=us
2
Answer 4 / 5
Yogeshg Posts 1 Registration date Thursday January 14, 2010 Status Member Last seen January 15, 2010
Jan 15, 2010 at 08:38 AM
Pls check solution available at http://www.yogeshguptaonline.com/2009/07/excel-functions-convert-numbers-into.html

This allows you to convert numbers to text in any currency. You can download excel file available at this link

Thanks
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
prasad
Jul 8, 2010 at 02:44 AM
Hi Edward,
Iam also having the same query if you got the answer kindly post to me
0

Similar discussions

Date of birth in words
pinckroje -
TrowaD -

1 response
Convert date of birth into words
prakashdnl9 -
vcoolio -

1 response
Date of birth number to words
Makk -
Jane -

2 responses
How to convert date into words
shahid -
2massive-head -

2 responses