Ask a question Report

How to convert a numeric value into eng. word [Solved/Closed]

raj86 - Latest answer on Dec 22, 2012 06:03AM
Hello,
How to convert a numeric value into English words in Excel plz show that formula
Read more 
Answer
+177
moins plus
Hi.

Below code enter in excel sheet
1. Press Alt+F11 > copy and pate in module (if module not show the insert module click insert on menu bar > Module)
2. Alt+Q Save code and return on Excel sheet
3. Insert > function > user defined > A1 or (relevent cell)

Function words(fig, Optional point = "Point") As String
Dim digit(14) As Integer
alpha = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
figi = Trim(StrReverse(Str(Int(Abs(fig)))))
For i = 1 To Len(figi)
digit(i) = Mid(figi, i, 1)
Next
For i = 2 To Len(figi) Step 3
If digit(i) = 1 Then
digit(i) = digit(i - 1) + 10: digit(i - 1) = 0
Else: If digit(i) > 1 Then digit(i) = digit(i) + 18
End If
Next
For i = 1 To Len(figi)
If (i Mod 3) = 0 And digit(i) > 0 Then words = "hundred " & words
If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _
words = Choose(i / 3, "thousand ", "million ", "billion ") & words
words = Trim(alpha(digit(i)) & " " & words)
Next
If fig <> Int(fig) Then
figc = StrReverse(figi)
If figc = 0 Then figc = ""
figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & ".", ""))
words = Trim(words & " " & point)
For i = 1 To Len(figd)
If Val(Mid(figd, i, 1)) > 0 Then
words = words & " " & alpha(Mid(figd, i, 1))
Else: words = words & " Zero"
End If
Next
End If
If fig < 0 Then words = "Negative " & words
End Function



FROM :-
Email id and phone number removed for security

rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - Dec 8, 2012 06:27AM
@xyleen:

1. open the excel file

2. Press ALT and F11 keys at the same time

3 Click on "Insert" and insert a "module"

4.In the solution ( http://en.kioskea.net/forum/affich-94180-how-to-convert-a-numeric-value-into-eng-word#6 ) the code starts at "Function words(fig, Optional point = "Point") As String " and ends at "End Function". Copy the complete code and paste on the newly inserted module

5 Press ALT and Q keys at same time to save code

6 Return on Excel sheet

7. use the formula like
=words(A1) --> using value from cell A1tc.
=words(123456)--> using numbers diferctly
sikarwar- Dec 15, 2012 09:43AM
Thanks a lot ! Dear..............................
sikarwar- Dec 15, 2012 09:43AM
Thank a lot ..................................
techgirl- Dec 15, 2012 12:50PM
it works!
venki- Dec 22, 2012 06:03AM
its working thank you
Answer
+20
moins plus
Here is the code:
using System;
namespace custom.util
{
public class NumberToEnglish
{
public String changeNumericToWords(double numb)
{
String num = numb.ToString();
return changeToWords(num, false);
}
public String changeCurrencyToWords(String numb)
{
return changeToWords(numb, true);
}
public String changeNumericToWords(String numb)
{
return changeToWords(numb, false);
}
public String changeCurrencyToWords(double numb)
{
return changeToWords(numb.ToString(), true);
}
private String changeToWords(String numb, bool isCurrency)
{
String val = "", wholeNo = numb, points = "", andStr = "", pointStr="";
String endStr = (isCurrency) ? ("Only") : ("");
try
{
int decimalPlace = numb.IndexOf(".");
if (decimalPlace > 0)
{
wholeNo = numb.Substring(0, decimalPlace);
points = numb.Substring(decimalPlace+1);
if (Convert.ToInt32(points) > 0)
{
andStr = (isCurrency)?("and"):("point");// just to separate whole numbers from points/cents
endStr = (isCurrency) ? ("Cents "+endStr) : ("");
pointStr = translateCents(points);
}
}
val = String.Format("{0} {1}{2} {3}",translateWholeNumber(wholeNo).Trim(),andStr,pointStr,endStr);
}
catch { ;}
return val;
}
private String translateWholeNumber(String number)
{
string word = "";
try
{
bool beginsZero = false;//tests for 0XX
bool isDone = false;//test if already translated
double dblAmt = (Convert.ToDouble(number));
//if ((dblAmt > 0) && number.StartsWith("0"))
if (dblAmt > 0)
{//test for zero or digit zero in a nuemric
beginsZero = number.StartsWith("0");
int numDigits = number.Length;
int pos = 0;//store digit grouping
String place = "";//digit grouping name:hundres,thousand,etc...
switch (numDigits)
{
case 1://ones' range
word = ones(number);
isDone = true;
break;
case 2://tens' range
word = tens(number);
isDone = true;
break;
case 3://hundreds' range
pos = (numDigits % 3) + 1;
place = " Hundred ";
break;
case 4://thousands' range
case 5:
case 6:
pos = (numDigits % 4) + 1;
place = " Thousand ";
break;
case 7://millions' range
case 8:
case 9:
pos = (numDigits % 7) + 1;
place = " Million ";
break;
case 10://Billions's range
pos = (numDigits % 10) + 1;
place = " Billion ";
break;
//add extra case options for anything above Billion...
default:
isDone = true;
break;
}
if (!isDone)
{//if transalation is not done, continue...(Recursion comes in now!!)
word = translateWholeNumber(number.Substring(0, pos)) + place + translateWholeNumber(number.Substring(pos));
//check for trailing zeros
if (beginsZero) word = " and " + word.Trim();
}
//ignore digit grouping names
if (word.Trim().Equals(place.Trim())) word = "";
}
}
catch { ;}
return word.Trim();
}
private String tens(String digit)
{
int digt = Convert.ToInt32(digit);
String name = null;
switch (digt)
{
case 10:
name = "Ten";
break;
case 11:
name = "Eleven";
break;
case 12:
name = "Twelve";
break;
case 13:
name = "Thirteen";
break;
case 14:
name = "Fourteen";
break;
case 15:
name = "Fifteen";
break;
case 16:
name = "Sixteen";
break;
case 17:
name = "Seventeen";
break;
case 18:
name = "Eighteen";
break;
case 19:
name = "Nineteen";
break;
case 20:
name = "Twenty";
break;
case 30:
name = "Thirty";
break;
case 40:
name = "Fourty";
break;
case 50:
name = "Fifty";
break;
case 60:
name = "Sixty";
break;
case 70:
name = "Seventy";
break;
case 80:
name = "Eighty";
break;
case 90:
name = "Ninety";
break;
default:
if (digt > 0)
{
name = tens(digit.Substring(0, 1) + "0") + " " + ones(digit.Substring(1));
}
break;
}
return name;
}
private String ones(String digit)
{
int digt = Convert.ToInt32(digit);
String name = "";
switch (digt)
{
case 1:
name = "One";
break;
case 2:
name = "Two";
break;
case 3:
name = "Three";
break;
case 4:
name = "Four";
break;
case 5:
name = "Five";
break;
case 6:
name = "Six";
break;
case 7:
name = "Seven";
break;
case 8:
name = "Eight";
break;
case 9:
name = "Nine";
break;
}
return name;
}
private String translateCents(String cents)
{
String cts = "", digit = "", engOne = "";
for (int i = 0; i < cents.Length; i++)
{
digit = cents[i].ToString();
if (digit.Equals("0"))
{
engOne = "Zero";
}
else
{
engOne = ones(digit);
}
cts += " " + engOne;
}
return cts;
}
}
}

Answer
+15
moins plus
' BELOW FUNCTION COPY AND PASTE
' 1 Start Microsoft Excel.
' 2. Press ALT+F11 to start the Visual Basic Editor.
' 3. On the Insert menu, click Module.
' 4. Type the following code into the module sheet.

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Rupees, Paisa, 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 Paisa and set MyNumber to Rupee amount.
If DecimalPlace > 0 Then
Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"0", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Rupee"
Case Else
Rupees = Rupees & " Rupees only"
End Select
Select Case Paisa
Case ""
Paisa = ""
Case "One"
Paisa = ""
Case Else
Paisa = " and " & Paisa & " Paisa"
End Select
SpellNumber = Rupees '& Paisa
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 "
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

'@@@@@@@@@@@

' 1. Select the cell that you want.
' 2. Click Insert Function on the Standard toolbar.
' 3. Under Or select a category, click User Defined.
' 4. In the Select a function list, click SpellNumber,
' and then click OK.
' 5. Enter the number or cell reference that you want,
' and then click OK.
'
' rajput391@yahoo.com
' Cell # 00963336904099

usha- Jan 23, 2010 06:10AM
Hi,
I want to convert figures to words in Excel 2007. The formula suggested by you does not work in Excel 2007. Do you have any other way of doing it. Pls reply soon.
Answer
+7
moins plus
Kindly mail me the formula to convert numeric value into english words in indian system in Microsoft Excel 2002

Answer
+4
moins plus
thx a lot dear........

Md. Sagir Hossain- Dec 5, 2012 10:24PM
How to fix it in excel always. I get it result only when apply it. but can not fixed. please how can I fixed (Permanently Save) in my computer. My window is 7 and office 2003.
This document entitled « How to convert a numeric value into eng. word » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.