Verified answer

You have to use VBA to build a function before you can do that with a formula

After setting up with the instruction below Use the following to change numeric to word

e.g. Cell A1 = SpellNumber(1000), you will get a thousand in the cell

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

http://support.microsoft.com/default.aspx?scid=KB;...

Change all the Dollar to Rupee will meet your requirements, hope this helps

A pivot table would be the simplest, but you want a formula so OK. First the pivot table. Highlight all of the cells that contain data, including the header information. Go to Data>Pivot Table. When the dialog box opens, Click Finish. The Pivot Table Field list will open. Drag the data to the far right column and to the data field. This will be automatically set to count. It will give you a count and a summary of each down the left column. Right click on the top left gray button and choose Field Settings. A dialog will open allowing you to adjust for your needs. Anytime you add new data, you will need to refresh the pivot table by right clicking anywhere on the table and select Refresh. AutoFilter: Select the cell at the top of your list and click Data>Filter>Auto Filter. A drop down box will appear. Click on the arrow and select the filter criteria "New Dehli", no quotes. Look at the bottom left corner of your screen. It will say "x of xx records found". This will give you a quick count. Formula: Select and empty cell and type =countif(a1:a1000,"New Dehli") This will count all of the records that have the exact text "New Dehli", no quotes, in them. You will need to select several cells to place this formula in. Replace "New Dehli" with "Agra", etc. as needed. This can be done on your summary tab. It does not have to be on the same work sheet.

here's a simple excel formula. =if(A1=1000,"One Thousand Rupees Only","")