Whar is the formula to convert 1234 in text like one thousand two hundred thirty four,

Ad

Hello,

Did you try this?

=SpellNumber(A1)

Here is a web site with other solutions.

Website

Have a great day,

ArmyMason

Posted on Feb 26, 2009

Ad

Hi,

a 6ya Technician can help you resolve that issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repair professionals here in the US.

click here to Talk to a Technician (only for users in the US for now) and get all the help you need.

Goodluck!

Posted on Jan 02, 2017

Ad

One hundred thirty nine thousand eight hundred and fifty two

Sep 20, 2016 | Computers & Internet

The regular is actually 3500 in order to four thousand Btu's associated with air conditioning have to make up for the quantity of temperature created with a one thousand watts lighting. Instance: ten By one thousand watts lamps will need among thirty-five, 000 as well as forty, 000 Btu's associated with air conditioning. Five By one thousand watts lamps will need among seventeen, five hundred as well as twenty, 000 Btu's associated with air conditioning. They are common recommendations because space circumstances, background air flow temps as well as air flow techniques almost all differ.

Oct 04, 2014 | Heating & Cooling

Write the first number out( Seventy Five) Then at the first comma write out what it represents (million) Then write out the next number (eight hundred ninety three) then what the next comma represents (thousand) then the next number (nine hundred thirty eight). Put it all together and you get your answer (seventy five million eight hundred ninety three thousand nine hundred thirty eight) its easy if you break it down section by section and remember what each comma represents. I hope this helped you out

Sep 01, 2011 | Office Equipment & Supplies

Here is a very popular bit of code **from Microsoft**
that will
convert any currency amount in a cell to English words. **All code and
text from
below here is the work of Microsoft.**

**Summary**

This article shows you how to create a sample, user-defined function named ConvertCurrencyToEnglish() to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56:**One Thousand
Two Hundred
Thirty Four Dollars And Fifty Six Cents**

The Function Wizard can also be used to enter a custom function in a worksheet. To use the Function Wizard, follow these steps:

1. Click the Function Wizard button, and select User Defined under Function Category.

2. Select ConvertCurrencyToEnglish, and enter your number or cell reference.

3. Click Finish

**To Create the Sample Functions**

1. Insert a module sheet into a workbook. To do this in Microsoft Excel 97 or Microsoft Excel 98, point to Macro on the Tools menu, and then click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu. In Microsoft Excel 5.0 or 7.0, point to Macro on the Insert menu and click Module.

2. Type the following code into the module sheet.

**Function ConvertCurrencyToEnglish (ByVal MyNumber)**

Dim Temp

Dim Dollars, Cents

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Million "

Place(4) = " Billion "

Place(5) = " Trillion "

' Convert MyNumber to a string, trimming extra spaces.

MyNumber = Trim(Str(MyNumber))

' Find decimal place.

DecimalPlace = InStr(MyNumber, ".")

' If we find decimal place...

If DecimalPlace > 0 Then

' Convert cents

Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)

Cents = ConvertTens(Temp)

' Strip off cents from remainder to convert.

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber <> ""

' Convert last 3 digits of MyNumber to English dollars.

Temp = ConvertHundreds(Right(MyNumber, 3))

If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

' Remove last 3 converted digits from MyNumber.

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

' Clean up dollars.

Select Case Dollars

Case ""

Dollars = "No Dollars"

Case "One"

Dollars = "One Dollar"

Case Else

Dollars = Dollars & " Dollars"

End Select

' Clean up cents.

Select Case Cents

Case ""

Cents = " And No Cents"

Case "One"

Cents = " And One Cent"

Case Else

Cents = " And " & Cents & " Cents"

End Select

ConvertCurrencyToEnglish = Dollars & Cents

**End Function**

**Private Function ConvertHundreds (ByVal MyNumber)**

Dim Result As String

' Exit if there is nothing to convert.

If Val(MyNumber) = 0 Then Exit Function

' Append leading zeros to number.

MyNumber = Right("000" & MyNumber, 3)

' Do we have a hundreds place digit to convert?

If Left(MyNumber, 1) <> "0" Then

Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "

End If

' Do we have a tens place digit to convert?

If Mid(MyNumber, 2, 1) <> "0" Then

Result = Result & ConvertTens(Mid(MyNumber, 2))

Else

' If not, then convert the ones place digit.

Result = Result & ConvertDigit(Mid(MyNumber, 3))

End If

ConvertHundreds = Trim(Result)

**End Function**

**Private Function ConvertTens (ByVal MyTens)**

Dim Result As String

' Is value between 10 and 19?

If Val(Left(MyTens, 1)) = 1 Then

Select Case Val(MyTens)

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

' .. otherwise it's between 20 and 99.

Select Case Val(Left(MyTens, 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

' Convert ones place digit.

Result = Result & ConvertDigit(Right(MyTens, 1))

End If

ConvertTens = Result

**End Function**

**Private Function ConvertDigit (ByVal MyDigit)**

Select Case Val(MyDigit)

Case 1: ConvertDigit = "One"

Case 2: ConvertDigit = "Two"

Case 3: ConvertDigit = "Three"

Case 4: ConvertDigit = "Four"

Case 5: ConvertDigit = "Five"

Case 6: ConvertDigit = "Six"

Case 7: ConvertDigit = "Seven"

Case 8: ConvertDigit = "Eight"

Case 9: ConvertDigit = "Nine"

Case Else: ConvertDigit = ""

End Select

**End Function**

This article shows you how to create a sample, user-defined function named ConvertCurrencyToEnglish() to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56:

The Function Wizard can also be used to enter a custom function in a worksheet. To use the Function Wizard, follow these steps:

1. Click the Function Wizard button, and select User Defined under Function Category.

2. Select ConvertCurrencyToEnglish, and enter your number or cell reference.

3. Click Finish

1. Insert a module sheet into a workbook. To do this in Microsoft Excel 97 or Microsoft Excel 98, point to Macro on the Tools menu, and then click Visual Basic Editor. In the Visual Basic Editor, click Module on the Insert menu. In Microsoft Excel 5.0 or 7.0, point to Macro on the Insert menu and click Module.

2. Type the following code into the module sheet.

Dim Temp

Dim Dollars, Cents

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Million "

Place(4) = " Billion "

Place(5) = " Trillion "

' Convert MyNumber to a string, trimming extra spaces.

MyNumber = Trim(Str(MyNumber))

' Find decimal place.

DecimalPlace = InStr(MyNumber, ".")

' If we find decimal place...

If DecimalPlace > 0 Then

' Convert cents

Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)

Cents = ConvertTens(Temp)

' Strip off cents from remainder to convert.

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber <> ""

' Convert last 3 digits of MyNumber to English dollars.

Temp = ConvertHundreds(Right(MyNumber, 3))

If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

' Remove last 3 converted digits from MyNumber.

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

' Clean up dollars.

Select Case Dollars

Case ""

Dollars = "No Dollars"

Case "One"

Dollars = "One Dollar"

Case Else

Dollars = Dollars & " Dollars"

End Select

' Clean up cents.

Select Case Cents

Case ""

Cents = " And No Cents"

Case "One"

Cents = " And One Cent"

Case Else

Cents = " And " & Cents & " Cents"

End Select

ConvertCurrencyToEnglish = Dollars & Cents

Dim Result As String

' Exit if there is nothing to convert.

If Val(MyNumber) = 0 Then Exit Function

' Append leading zeros to number.

MyNumber = Right("000" & MyNumber, 3)

' Do we have a hundreds place digit to convert?

If Left(MyNumber, 1) <> "0" Then

Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "

End If

' Do we have a tens place digit to convert?

If Mid(MyNumber, 2, 1) <> "0" Then

Result = Result & ConvertTens(Mid(MyNumber, 2))

Else

' If not, then convert the ones place digit.

Result = Result & ConvertDigit(Mid(MyNumber, 3))

End If

ConvertHundreds = Trim(Result)

Dim Result As String

' Is value between 10 and 19?

If Val(Left(MyTens, 1)) = 1 Then

Select Case Val(MyTens)

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

' .. otherwise it's between 20 and 99.

Select Case Val(Left(MyTens, 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

' Convert ones place digit.

Result = Result & ConvertDigit(Right(MyTens, 1))

End If

ConvertTens = Result

Select Case Val(MyDigit)

Case 1: ConvertDigit = "One"

Case 2: ConvertDigit = "Two"

Case 3: ConvertDigit = "Three"

Case 4: ConvertDigit = "Four"

Case 5: ConvertDigit = "Five"

Case 6: ConvertDigit = "Six"

Case 7: ConvertDigit = "Seven"

Case 8: ConvertDigit = "Eight"

Case 9: ConvertDigit = "Nine"

Case Else: ConvertDigit = ""

End Select

Apr 15, 2010 | Broderbund Learn MS Windows XP and Excel...

- Start Microsoft Excel.
- Press ALT+F11 to start the Visual Basic Editor.
- On the
**Insert**menu, click**Module**. - Type the following code into the module sheet. Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, 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 cents and set MyNumber to dollar amount. If DecimalPlace > 0 Then Cents = 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 Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 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 SpellNumber = Dollars & Cents 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

- Select the cell that you want.
- Click Paste Function on the Standard toolbar.
- Under Function category, click User Defined.
- Under Function name, click SpellNumber, and then click OK.
- Enter the number or cell reference that you want, and then click OK.

- Select the cell that you want.
- Click Insert Function on the Standard toolbar.
- Under Or select a category, click User Defined.
- In the Select a function list, click SpellNumber, and then click OK.
- Enter the number or cell reference that you want, and then click OK.

Apr 01, 2010 | Microsoft Windows XP Professional

Hiii

Regards.

G'Day

- Start Microsoft Excel.
- Press ALT+F11 to start the Visual Basic Editor.
- On the
**Insert**menu, click**Module**. - Type the following code into the module sheet.
**Option Explicit**

'Main Function

Function SpellNumber(ByVal MyNumber)

Dim Dollars, Cents, 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 cents and set MyNumber to dollar amount.

If DecimalPlace > 0 Then

Cents = 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 Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 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

SpellNumber = Dollars & Cents

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

Regards.

G'Day

Sep 10, 2009 | Microsoft Office Professional 2007 Full...

Dear,

you've use macro to this.

**Press Alt+F11**

it will open Microsoft Vsual Basic Editor.

Click on__Module in Insert Menu__ and paste the following codings:

**Function ConvRs(ByVal MyNumber)**

Dim Rupees, Paise, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Lac "

Place(4) = " Crore "

Place(5) = " Arab " ' String representation of amount

MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none

DecimalPlace = InStr(MyNumber, ".")

' Convert Paise and set MyNumber to Rupee amount

If DecimalPlace > 0 Then

Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber <> ""

If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))

If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))

If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees

If Count = 1 And Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

If Count > 1 And Len(MyNumber) > 2 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 2)

Else

MyNumber = ""

End If

End If

Count = Count + 1

Loop

Select Case Rupees

Case ""

Rupees = "No Rupees"

Case "One"

Rupees = "One Rupee"

Case Else

**'Rupees = Rupees & " Rupees"**

Rupees = "Rupees " & Rupees

**End Select**

Select Case Paise

Case ""

**'Paise = ""**

Paise = " Only"

Case "One"

Paise = " and One Paisa"

Case Else

Paise = " and " & Paise & " Paise"

**End Select**

ConvRs = Rupees & Paise

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

save this as as**ConvertRs.xla in Microsoft\AddIns folder**

now, you'll have to activate this AddIns:

in your Excel Worksheet select:

**Tools\Add.Ins...**

Check the ConvertRs option

now this will be used as User Defined Function.

you can insert it from the Function Options or just enter the following command in the desired cell where you want the result:

**=ConvertRs(Cell Reference)**

Cell Reference means where the number is available, you want to convert.

Try this and then tell me the responce.

Bhasker Kumar

you've use macro to this.

it will open Microsoft Vsual Basic Editor.

Click on

Dim Rupees, Paise, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Lac "

Place(4) = " Crore "

Place(5) = " Arab " ' String representation of amount

MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none

DecimalPlace = InStr(MyNumber, ".")

' Convert Paise and set MyNumber to Rupee amount

If DecimalPlace > 0 Then

Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber <> ""

If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))

If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))

If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees

If Count = 1 And Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

If Count > 1 And Len(MyNumber) > 2 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 2)

Else

MyNumber = ""

End If

End If

Count = Count + 1

Loop

Select Case Rupees

Case ""

Rupees = "No Rupees"

Case "One"

Rupees = "One Rupee"

Case Else

Rupees = "Rupees " & Rupees

Select Case Paise

Case ""

Paise = " Only"

Case "One"

Paise = " and One Paisa"

Case Else

Paise = " and " & Paise & " Paise"

ConvRs = Rupees & Paise

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

save this as as

now, you'll have to activate this AddIns:

in your Excel Worksheet select:

Check the ConvertRs option

now this will be used as User Defined Function.

you can insert it from the Function Options or just enter the following command in the desired cell where you want the result:

Cell Reference means where the number is available, you want to convert.

Try this and then tell me the responce.

Bhasker Kumar

Jul 09, 2009 | Microsoft Computers & Internet

You need to make some changes to the VB script.

here are the steps to guide you

Open Excel - press Alt F11, Click on Insert, Select Module - Copy paste the below mentioned script>>>

Function SpellNumber(ByVal MyNumber, Optional incRupees As Boolean = True)

Dim Crores, Lakhs, Rupees, Paise, Temp

Dim DecimalPlace As Long, Count As Long

Dim myLakhs, myCrores

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 Paise and set MyNumber to Rupees amount.

If DecimalPlace > 0 Then

Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

myCrores = MyNumber \ 10000000

myLakhs = (MyNumber - myCrores * 10000000) \ 100000

MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000

Count = 1

Do While myCrores <> ""

Temp = GetHundreds(Right(myCrores, 3))

If Temp <> "" Then Crores = Temp & Place(Count) & Crores

If Len(myCrores) > 3 Then

myCrores = Left(myCrores, Len(myCrores) - 3)

Else

myCrores = ""

End If

Count = Count + 1

Loop

Count = 1

Do While myLakhs <> ""

Temp = GetHundreds(Right(myLakhs, 3))

If Temp <> "" Then Lakhs = Temp & Place(Count) & Lakhs

If Len(myLakhs) > 3 Then

myLakhs = Left(myLakhs, Len(myLakhs) - 3)

Else

myLakhs = ""

End If

Count = Count + 1

Loop

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 Crores

Case "": Crores = ""

Case "One": Crores = " One Crore "

Case Else: Crores = Crores & " Crores "

End Select

Select Case Lakhs

Case "": Lakhs = ""

Case "One": Lakhs = " One Lakh "

Case Else: Lakhs = Lakhs & " Lakhs "

End Select

Select Case Rupees

Case "": Rupees = "Zero "

Case "One": Rupees = "One "

Case Else:

Rupees = Rupees

End Select

Select Case Paise

Case "": Paise = " and Paise Zero Only "

Case "One": Paise = " and Paise One Only "

Case Else: Paise = " and Paise " & Paise & " Only "

End Select

SpellNumber = IIf(incRupees, "Rupees", "") & Crores & Lakhs & Rupees & Paise

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

>>>Save it as a file on the computer - Select the cell where you want to insert the function - go to Insert - Function - User Defined - Spellnumber - Select the reference value against my number and type true in incrupees.

It would give the value in Word format.

The limiation is it can be only use in teh same workbook.

Next time when you want to use the formula, either you have to redo the process or use this workbook and copy paste data in another one.

Let me know if you face any difficulty.

here are the steps to guide you

Open Excel - press Alt F11, Click on Insert, Select Module - Copy paste the below mentioned script>>>

Function SpellNumber(ByVal MyNumber, Optional incRupees As Boolean = True)

Dim Crores, Lakhs, Rupees, Paise, Temp

Dim DecimalPlace As Long, Count As Long

Dim myLakhs, myCrores

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 Paise and set MyNumber to Rupees amount.

If DecimalPlace > 0 Then

Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

myCrores = MyNumber \ 10000000

myLakhs = (MyNumber - myCrores * 10000000) \ 100000

MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000

Count = 1

Do While myCrores <> ""

Temp = GetHundreds(Right(myCrores, 3))

If Temp <> "" Then Crores = Temp & Place(Count) & Crores

If Len(myCrores) > 3 Then

myCrores = Left(myCrores, Len(myCrores) - 3)

Else

myCrores = ""

End If

Count = Count + 1

Loop

Count = 1

Do While myLakhs <> ""

Temp = GetHundreds(Right(myLakhs, 3))

If Temp <> "" Then Lakhs = Temp & Place(Count) & Lakhs

If Len(myLakhs) > 3 Then

myLakhs = Left(myLakhs, Len(myLakhs) - 3)

Else

myLakhs = ""

End If

Count = Count + 1

Loop

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 Crores

Case "": Crores = ""

Case "One": Crores = " One Crore "

Case Else: Crores = Crores & " Crores "

End Select

Select Case Lakhs

Case "": Lakhs = ""

Case "One": Lakhs = " One Lakh "

Case Else: Lakhs = Lakhs & " Lakhs "

End Select

Select Case Rupees

Case "": Rupees = "Zero "

Case "One": Rupees = "One "

Case Else:

Rupees = Rupees

End Select

Select Case Paise

Case "": Paise = " and Paise Zero Only "

Case "One": Paise = " and Paise One Only "

Case Else: Paise = " and Paise " & Paise & " Only "

End Select

SpellNumber = IIf(incRupees, "Rupees", "") & Crores & Lakhs & Rupees & Paise

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

>>>Save it as a file on the computer - Select the cell where you want to insert the function - go to Insert - Function - User Defined - Spellnumber - Select the reference value against my number and type true in incrupees.

It would give the value in Word format.

The limiation is it can be only use in teh same workbook.

Next time when you want to use the formula, either you have to redo the process or use this workbook and copy paste data in another one.

Let me know if you face any difficulty.

Feb 13, 2009 | Microsoft Excel for PC

Convert to UPPER, Proper or lower

Auto Convert to UPPER, Proper or lower

Stop VBA Being Case Sensitive

Select Case Statement. Great alternative to Else If Statements

VBA: CBool(),CByt(),CCur(),CDate(),CDbl()Double,CDec(),CInt(),CLng(),CSng(),CStr(),CVar().

dDate=cDate("25-Jan-07")

Convert Numbers to Words

Convert Numbers to Dollar Amount in Words

Spreadsheet Converter. Versions: HTML/JavaScript,Excel ASP.Net and Excel Java/JSP. $

SQL Database Migration $

Converters. Database Converters, Spreadsheet Converters, Text Converters, E-mail Conversion, Document Conversion, PDF Conversion and more! $

CSV Converter $

Import SQL Into Excel-SQL Tester $

Import Excel Into Access $

Conditional Formatting

More Than 3 Conditional Formats

Conditional Format Fonts for greater than 3

Custom Formats

Excel see a cells format as having four Sections. These are, from left to right: Positives;Negatives;Zeros;Text.

To hide zeros cell-by-cell use a Custom Number Format like

To hide zeros on the Workbook level go to

Dec 15, 2008 | Microsoft Windows XP Home Edition

1. Four Hundred Seventy-Eight Ten Thousandths

2. One Thousand Four Hundred Thirty Five Ten Thousandths.

3. Three Thousand Two Hundred Five Ten Thousandths.

4. Four thousand Seven Hundred Eighty Four Ten Thousandths.

5. Four Hundred Ninety Seven Ten Thousandths.

OR

1. Zero point zero four seven eight.

2. Zero point one four three five

3. Zero point three two zero five

4. Zero point four seven eight four

5. Zero point zero four nine seven

Thanks and please take a moment to rate as fixya.

2. One Thousand Four Hundred Thirty Five Ten Thousandths.

3. Three Thousand Two Hundred Five Ten Thousandths.

4. Four thousand Seven Hundred Eighty Four Ten Thousandths.

5. Four Hundred Ninety Seven Ten Thousandths.

OR

1. Zero point zero four seven eight.

2. Zero point one four three five

3. Zero point three two zero five

4. Zero point four seven eight four

5. Zero point zero four nine seven

Thanks and please take a moment to rate as fixya.

Jul 06, 2008 | Audio Players & Recorders

32 people viewed this question

Usually answered in minutes!

×