Related:

- How to convert number to words in excel in rupees without vba
- How to convert a numeric value into english words in excel 2007 - Best answers
- Number to words in excel without vba - Best answers
- How to convert number into text in Excel ✓ - Forum - Excel
- Formula in Excel to turn numbers into text ✓ - Forum - Excel
- Rupees into words - Forum - Excel
- Convert numbers to words in excel - Forum - Excel
- How To Convert Numbers to Words in Excel - How-To - Excel

## 5 replies

rajput391

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 :-

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 securityEmail id and phone number removed for security

nish

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;

}

}

}

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;

}

}

}

rajput391

' 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

' 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

susanta

Kindly mail me the formula to convert numeric value into english words in indian system in Microsoft Excel 2002

Thanks

please modify the formula for me

example 123456..78

Rupees One Lac Twenty Three Thousand Four Hundred Fifty Six And Seventy Eight Paisa Only

my alternet ID

Email id removed for securitypls give formula convert number to word (in excel or word)

exm:1000Rs (one thousand rupees only)

Thank"s & regards

vinod v sharma

Rupees One Lac Twenty Three Thousand Four Hundred Fifty Six And Seventy Eight Paisa Only

formula in xls

pls give formula convert number to word (in excel or word) in simple way please.

exm:1000Rs (one thousand rupees only)

Thank"s & regards

yaseen