Ask your question Print Format Forum list Help them Statistics Search Charter Forum Office software

Convert number to text in excel sheet

Last answer on Nov 9, 2009 6:41:53 am GMT Tarali, on Dec 29, 2008 9:00:30 am GMT

Report this message to moderators

Hello sir

im facing lots of truble from few days

im trying to put in formula bar to convert number to text in excel sheet. I got one formula but im not getting properly i.e. =text(value,format_text). With this formula i tried so many time but unable to get my answer. Plz help me sir

with best regard

tarali

Configuration: Windows XP
Internet Explorer 6.0

Best answers for « convert number to text in excel sheet » in :

Compare 2 excel sheet and combine uniq data Show

Create an Attendance Report with Excel sheet Show Create an Attendance Report with Excel sheet Issue Solution Issue In the case you want to create an attendance report with Excel , here below you will find a nice example: Consider that your report will have the following...

Comparing two excel sheets Show

How to compare 2 excel sheet and combine uniq data? ShowHow to compare 2 excel sheet and combine uniq data? Issue Solution Note Issue I have 2 excel sheets , A and B. There are redundant data in both sheets as well as uniq. All i want is to combine both with no redundant records. (A+B...

Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...

How to freeze a row in an excel sheet ShowHow to freeze a row in an excel sheet To freeze a line in an excel sheet, for example line 1: Select line 2. Go to menu Window� and select Freeze panes�.

Changin format of date in excel Show

Copying data from one Excel sheet to another. Show

Excel Macro Help Please (Solved) Show

Download PDF Conversion Series - Text to PDF ShowDescription The application is designed by Dawningsoft Inc. Simple and easy to use, the application has been awarded from certain places. It offers an intuitive interface. Text To PDF is a tool that enables you to convert your document to PDF...

Download DBF To XLS Converter ShowThis is a very useful software that will allow anyone to convert DBF files into Excel's .xls format files. Features: The user interface of the converter is very simple to comprehend and almost anyone can do it within some simple clicks. All...

Ask your question Reply

haveffun, on Dec 29, 2008 11:19:21 am GMT

Hi,
you will get all the information you need on the official website of microsoft
try it:
http://office.microsoft.com/en-us/excel/HA011366191033.aspx

Reply to haveffun

WD, on Dec 31, 2008 2:11:18 am GMT

If you need a formula to simply change numbers to text, use =concatenate([Cell#]). If you need to limit data in a cell to text only, set the cell format to Text.

Reply to WD

niki, on Feb 6, 2009 4:40:26 am GMT

+60

Hi
sir i need a formula for numbers ( Figures) are convert in words.

Like Rs.20 =Twenty only( In Formula)

Thanks
Regards
Nikhil Mehta

Reply to niki

S.Varma, on Feb 9, 2009 6:37:58 am GMT

Hai,
I have been searching for the same. There are some Add On s available, This one works (link), but it does not retain the input value in numerals. But may be helpful for you. Check this.
http://www.microsoft.com/...

Similar add on is available for Excel as well. Works fine for Word 2002 and higher versions. If you come across some better solutions, do post it as well.

Reply to S.Varma

Naufal, on Feb 14, 2009 7:43:42 am GMT

Hello,

Have you got the formula for it, if not reply here...

i have one formula to solve....

Naufal
Dubai

Reply to Naufal

DK, on Feb 18, 2009 8:37:23 am GMT

DEAR
PLEASE SEND ME THAT FORMULA.

dk

Reply to DK

naufal, on Feb 18, 2009 9:12:37 am GMT

+27

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

Reply to naufal

Noorul Ameen, on Feb 22, 2009 9:00:53 am GMT

Dear Naufal,

Good Day.

Can u help me to get number2text in Excel. Because I need it in Saudi Riyals. I have one add-ins that too written in Indian Rupees.

Kindly help me

Thanks & Regards

Noorul Ameen.

Reply to Noorul Ameen

naresh, on Mar 5, 2009 5:03:15 am GMT

Can u send me the formula in indian rs.i will be obliged

Reply to naresh

naufal, on Mar 5, 2009 6:50:26 am GMT

Dear Mr.Naresh
for getting in Rs you have to change the word "Dollar" and "Cents" in quotes to "Rupees" and "Paise" respectievely
IMP: Only change which in quotes(" " ).

Reply to naufal

Al, on Mar 9, 2009 10:30:20 am GMT

Hi,

could you please help me on this. I have copied the codes in the module and then how do I go about. for example what I am trying is I want to convert Rs.100/- as Rupees One Hundredy Only.

since I want to print amounts like 100s or 1000s or 1,00,000s or more into text format as above.

thanks in advance for your help.

regards,
Al

Reply to Al

sweetguy, on May 24, 2009 12:43:46 pm BST

Hi ,

I have already type the work into the marco module , how to use ? Create macro ? please teach me .

Best Regards
Sweetguy

Reply to sweetguy

DEEPAK SRIVASTAVA, on Jun 1, 2009 9:34:48 am BST

Detail how ti use amount in word in excel work book

Reply to DEEPAK SRIVASTAVA

123456, on Jul 2, 2009 12:48:25 pm BST

Hi

After Entering the codes how to activate that.

kindly clarify.

Lakshmanan

Reply to 123456

Mukesh, on Mar 1, 2009 4:48:54 am GMT

Dear Naufal,

I neet your help. i used spellnumber formula but its not working. pls tell me how to use this.

Thanks & Regards

Mukesh Kumar

Reply to Mukesh

naufal, on Mar 1, 2009 7:43:19 am GMT

Dear Mukesh,

Tell me what errors showing there, reply: naufalokay atgmail

Reply to naufal

Mukesh, on Mar 1, 2009 11:27:13 am GMT

Hello Naufal,

Thanks dear...i got the formula of Spellnumber from you kind help.

You said that we used this formula in the same excel sheet not in the new worksheet. Can we apply in all new excel sheet or not ? Otherwise we have to use them in the same sheet we made first time.

Reply to Mukesh

SARANG, on Apr 17, 2009 9:51:06 am BST

Dear Naufal

your software help me for converting amount in Text,but the problem only remain is that im in India and i want amount in INR,

Pl help me

Sarang Palorkar
9822470123 / 93732030821

Reply to SARANG

dolphy, on Jun 9, 2009 8:59:59 am BST

KINDLY SENT ME THE EXACT FORMULA TO CONVERT NUMERIC VALUE INTO WORDS IN INDIAN SYSTEM

FOR EXAMPLE 185256 LIKE 'ONE LAKH EIGHTY FIVE THOUSAND TWO HUNDRED FIFTY SIX ONLY

Reply to dolphy

JD, on Jun 20, 2009 11:10:44 am BST

I WANT TO CONVERT NUMBER INTO TEXT.

SAY 123 = ONE HUNDRED AND TWENTY ONLY

KINDLY TELL ME HOW.

REGARDS

Reply to JD

atik, on Jul 5, 2009 1:56:22 pm BST

Convert number into text
like: 4520=four thousand five hundred twenty only
pls solve with a single formula if possible.

Reply to atik

rakesh, on May 15, 2009 6:16:10 am BST

Convert number to text in excel sheet
how to convert a number to a text
for eg: 50 to fifty only

Reply to rakesh

jk patel, on Mar 16, 2009 1:08:44 pm GMT

Convert number to text in excel sheet

Reply to jk patel

WASIM, on Mar 22, 2009 10:48:45 am GMT

Hi
sir i need a formula for numbers ( Figures) are convert to words.

Like Rs.23 =twenty three( In Formula)

Thanks
best Regards
WASIM

Reply to WASIM

hussein, on Mar 23, 2009 2:08:18 pm GMT

1]i have 2 excel worksheet in which i have inputted the data and i analysing sheet in which i have given formulas to copy the figures from the 2 excel worksheet.i want to know if some one make changes in some cell or if some one make any changes in any of the excel worksheet,so can i come to knw that where the changes has been made by giving some formulas or by some colours that particular cell has been changed.

2] i want to protect some of the cells in my excel sheet,kindly suggest some solution

3] i want to learn pivot table

Reply to hussein

sohail, on May 19, 2009 12:57:13 pm BST

Hi
sir i need a formula for numbers ( Figures) are convert in words.

Like Rs.20 =Twenty only( In Formula)

Thanks
Regards
sohail

Reply to sohail

sohail, on May 19, 2009 12:58:18 pm BST

Hi
sir i need a formula for numbers ( Figures) are convert in words.

Like Rs.20 =Twenty only( In Formula)

Thanks
Regards
SOHAIL

Reply to sohail

gaurav70090, on Jun 24, 2009 3:49:33 pm BST

Value
Enter Value

Formula for Crore
Crore
=(IF(A2>99999999,LEFT(RIGHT(A2,9),2),IF(A2>9999999,LEFT(RIGHT(A2,8),1),0)))

Amount In Word
=IF(IF(ISNA(VLOOKUP($D2,$A:$B,2,0))," ",VLOOKUP($D2,$A:$B,2,0))=" ",IF(ISNA(VLOOKUP($D2,$A:$B,2,0))," ",VLOOKUP($D2,$A:$B,2,0)),IF(ISNA(VLOOKUP($D2,$A:$B,2,0))," ",VLOOKUP($D2,$A:$B,2,0))&" "&$D$1)

Reply to gaurav70090

amit, on Aug 30, 2009 1:03:05 pm BST

Sir
i want to take a forum convert num to text in excel

Reply to amit

nim, on Oct 30, 2009 11:43:01 am GMT

I need it

Reply to nim

Naufal, on Feb 14, 2009 7:46:14 am GMT

Hello Dear

If not solved yet , reply me....

Regards,
Naufal
Dubai

Reply to Naufal

naufal, on Feb 22, 2009 9:16:36 am GMT

Hello Dera Ameen,

Paste these codes instead of old codes

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"

Case Else

Dollars = Dollars

End Select

' Clean up cents.

Select Case Cents

Case ""

Cents = " Only."

Case "One"

Cents = " And One Fill Only."

Case Else

Cents = " And " & Cents & " Fills Only."

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

Regards,
Naufal OA
naufalokay atgmail

Reply to naufal

Ami, on Nov 9, 2009 6:41:53 am GMT

Dear Sir,

I just want to know that after put the formula in VB then what we have to do?
As i m not good in VB kindly guide me.

Thanks,

Amit

Reply to Ami

gv, on May 26, 2009 4:47:55 pm BST

Hi Expert,

Please assist where can i get the NumtoText convertor in french, or how to built/run this add-in using microsoft visual basic in excel 2003/2007, and also provide the code.

Appreciate your help.

best regards,
Gurvis

Reply to gv

balaji.shettigar@gmail.com, on Jun 18, 2009 8:38:27 am BST

Dear Naufal,

when ever i use spellnumber it takes me to the VB page and it highlights this place

Place(2) = � Thousand �.

please help me

Balaji Shettigar
Dubai

Reply to balaji.shettigar@gmail.com

, on Jun 18, 2009 8:41:34 am BST

Dear Naufal,

when ever i use spell number i get it takes me to the VB page andit highlights this place

Place(2) = � Thousand �.
please help me

Balaji Shettigar
Dubai

Reply to

naufal, on Feb 14, 2009 8:04:05 am GMT

Use this link : http://support.microsoft.com/kb/213360

Reply to naufal

naufal, on Mar 1, 2009 7:40:17 am GMT

Dear Mr.Mukesh

follow instructions as in my reply on : Wednesday February 18, 2009 09:12:37 AM
still you have any doubt reply me: naufalokay atgmail

Reply to naufal

naufal, on Mar 1, 2009 11:34:49 am GMT

Dear mukesh,

it will not come in the next new file you are opening.so you have to do the same.

Reply to naufal

Sanju, on Apr 11, 2009 6:23:44 am BST

How to translet a number in Words, like 101 = One Hundred One

Reply to Sanju

mubashir aziz, on Apr 13, 2009 8:26:26 am BST

Just Press Alt + F11 in a new work book and enter this code

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 Cent"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cent"
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

Now in work sheet write =SpellNumber(A4 or cell reference) it will convert it in Words ....

Reply to mubashir aziz

Tashfeen, on May 12, 2009 8:51:51 am BST

I typy all formating in Visual Baisc as per your describing then i type in cell(A5) "1234" and type in cell(F5) "=SPELLNUMBER(A5) but result is "#NAME?"
Plz guide me, where i mistaked.

Reply to Tashfeen

gharbi rad, on May 25, 2009 11:13:28 am BST

Hi,
For Tashfeen
Probably � you have 2 modules by same Function name SpellNumber( )!!
thinks

Reply to gharbi rad

balaji.shetttigar@gmail.com, on Jun 18, 2009 9:25:14 am BST

Hi There

i have added the code in theVB module.
when i enter the values i get a syntax error and it takes me to this VB page higlighting

Place(2) = � Thousand �

please advice and correct me

Rgds
Balaji

Reply to balaji.shetttigar@gmail.com

svc, on Apr 15, 2009 3:44:42 pm BST

http://in.geocities.com/shahshaileshs/

Reply to svc

Dhinu, on Apr 20, 2009 10:38:54 am BST

Sir
i need a formula for numbers ( Figures) are convert to words.

Like Rs.52 = Rs. Fifty two only.( In Formula)

pls send my e.mail id

thanks

dhinesh.s

Reply to Dhinu

Varun, on Apr 24, 2009 11:37:56 am BST

Hi

Please advise how to convert numeric figure in text in exel sheet

Regards
Varun

Reply to Varun

Samuel, on Apr 28, 2009 5:16:45 am BST

How to Convert Number to Text in Excel Sheet

Reply to Samuel

Ishwar Singh, on Apr 30, 2009 7:21:35 pm BST

Dear Sir,

I want any number in words.

Reply to Ishwar Singh

emmee, on May 14, 2009 1:31:10 pm BST

Hai dear ,

i am using that formula but when i apply the formula for 0.50 it come like this "No Dirhams and Fifty Fills
" is there any way to over come this , kindly advice

Reply to emmee

mubashir aziz, on May 28, 2009 3:46:04 am BST

Me and other guys have pasted macros to solve the problem but i don't know why peoples are not looking whole thread and just asking for code ?????

Please see the whole post and many places code is pasted and please keep in your mind that there is no built-in formula in Excel to solve you problem except some Functions and Add Ins ...
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

Reply to mubashir aziz

sridhar, on Jun 22, 2009 10:07:10 am BST

KINDLY SENT ME THE EXACT FORMULA TO CONVERT NUMERIC VALUE INTO WORDS IN INDIAN SYSTEM

FOR EXAMPLE 185256 LIKE 'ONE LAKH EIGHTY FIVE THOUSAND TWO HUNDRED FIFTY SIX ONLY

with regards
Sridhar

Reply to sridhar

sridhar, on Jun 22, 2009 10:08:11 am BST

KINDLY SENT ME THE EXACT FORMULA TO CONVERT NUMERIC VALUE INTO WORDS IN INDIAN SYSTEM

FOR EXAMPLE 185256 LIKE 'ONE LAKH EIGHTY FIVE THOUSAND TWO HUNDRED FIFTY SIX ONLY

Reply to sridhar

madhuri, on Jun 24, 2009 8:29:05 am BST

KINDLY SENT ME THE EXACT FORMULA TO CONVERT NUMERIC VALUE INTO WORDS IN INDIAN SYSTEM

FOR EXAMPLE 185256 LIKE 'ONE LAKH EIGHTY FIVE THOUSAND TWO HUNDRED FIFTY SIX ONLY

Reply to madhuri

balaji, on Jun 22, 2009 11:20:01 am BST

Dear Sir,
When i Use this spellnumber() function it takes me to the VB script page and says error on " thousand".

Rgds
Balaji Shettigar

Reply to balaji

noecortez, on Jul 3, 2009 11:01:34 pm BST

Puedes utilizar el siguiente codigo, solo tecleas ALT+F11 y le das insertar - modulo. Copias el siguiente codigo:
Function ntot(Numero)
Dim Texto
Dim Millones
Dim Miles
Dim Cientos
Dim Decimales
Dim Cadena
Dim CadMillones
Dim CadMiles
Dim CadCientos
Texto = Numero
Texto = FormatNumber(Texto, 2)
Texto = Right(Space(14) & Texto, 14)
Millones = Mid(Texto, 1, 3)
Miles = Mid(Texto, 5, 3)
Cientos = Mid(Texto, 9, 3)
Decimales = Mid(Texto, 13, 2)
CadMillones = ConvierteCifra(Millones, 1)
CadMiles = ConvierteCifra(Miles, 1)
CadCientos = ConvierteCifra(Cientos, 0)
If Trim(CadMillones) > "" Then
If Trim(CadMillones) = "UN" Then
Cadena = CadMillones & " MILLON"
Else
Cadena = CadMillones & " MILLONES"
End If
End If
If Trim(CadMiles) > "" Then
Cadena = Cadena & " " & CadMiles & " MIL"
End If
If Trim(CadMiles & CadCientos) = "UN" Then
Cadena = Cadena & "UNO CON " & Decimales & "/100"
Else
If Miles & Cientos = "000000" Then
Cadena = Cadena & " " & Trim(CadCientos) & " DE PESOS " & Decimales & "/100"
Else
Cadena = Cadena & " " & Trim(CadCientos) & " PESOS " & Decimales & "/100"
End If
End If
ntot = "(" + Trim(Cadena) + ")"
End Function

Function ConvierteCifra(Texto, SW)
Dim Centena
Dim Decena
Dim Unidad
Dim txtCentena
Dim txtDecena
Dim txtUnidad
Centena = Mid(Texto, 1, 1)
Decena = Mid(Texto, 2, 1)
Unidad = Mid(Texto, 3, 1)
Select Case Centena
Case "1"
txtCentena = "CIEN"
If Decena & Unidad <> "00" Then
txtCentena = "CIENTO"
End If
Case "2"
txtCentena = "DOSCIENTOS"
Case "3"
txtCentena = "TRESCIENTOS"
Case "4"
txtCentena = "CUATROCIENTOS"
Case "5"
txtCentena = "QUINIENTOS"
Case "6"
txtCentena = "SEISCIENTOS"
Case "7"
txtCentena = "SETECIENTOS"
Case "8"
txtCentena = "OCHOCIENTOS"
Case "9"
txtCentena = "NOVECIENTOS"
End Select

Select Case Decena
Case "1"
txtDecena = "DIEZ"
Select Case Unidad
Case "1"
txtDecena = "ONCE"
Case "2"
txtDecena = "DOCE"
Case "3"
txtDecena = "TRECE"
Case "4"
txtDecena = "CATORCE"
Case "5"
txtDecena = "QUINCE"
Case "6"
txtDecena = "DIECISEIS"
Case "7"
txtDecena = "DIECISIETE"
Case "8"
txtDecena = "DIECIOCHO"
Case "9"
txtDecena = "DIECINUEVE"
End Select
Case "2"
txtDecena = "VEINTE"
If Unidad <> "0" Then
txtDecena = "VEINTI"
End If
Case "3"
txtDecena = "TREINTA"
If Unidad <> "0" Then
txtDecena = "TREINTA Y "
End If
Case "4"
txtDecena = "CUARENTA"
If Unidad <> "0" Then
txtDecena = "CUARENTA Y "
End If
Case "5"
txtDecena = "CINCUENTA"
If Unidad <> "0" Then
txtDecena = "CINCUENTA Y "
End If
Case "6"
txtDecena = "SESENTA"
If Unidad <> "0" Then
txtDecena = "SESENTA Y "
End If
Case "7"
txtDecena = "SETENTA"
If Unidad <> "0" Then
txtDecena = "SETENTA Y "
End If
Case "8"
txtDecena = "OCHENTA"
If Unidad <> "0" Then
txtDecena = "OCHENTA Y "
End If
Case "9"
txtDecena = "NOVENTA"
If Unidad <> "0" Then
txtDecena = "NOVENTA Y "
End If
End Select

If Decena <> "1" Then
Select Case Unidad
Case "1"
If SW Then
txtUnidad = "UN"
Else
txtUnidad = "UNO"
End If
Case "2"
txtUnidad = "DOS"
Case "3"
txtUnidad = "TRES"
Case "4"
txtUnidad = "CUATRO"
Case "5"
txtUnidad = "CINCO"
Case "6"
txtUnidad = "SEIS"
Case "7"
txtUnidad = "SIETE"
Case "8"
txtUnidad = "OCHO"
Case "9"
txtUnidad = "NUEVE"
End Select
End If
ConvierteCifra = txtCentena & " " & txtDecena & txtUnidad
End Function

Luego lo mandas llamar desde tu hoja como =ntot(B15), por ejemplo donde B15 es la celda donde esta el numero que quieres convertir a texto.
Para cualquier otro desarrollo estoy a tus ordenes, me puedes escribir a noecortez(arroba)leipzig.com.mx o llamarme al 011+52+55+55654892

Reply to noecortez

Larry, on Jul 4, 2009 5:15:12 pm BST

Try this new UDF that I have written:

Function NumberWords(Value As Double, Optional Style As Integer, Optional Enclose As Boolean, Optional CurrencyRef As String) As Variant

' ------------------------------------------------------------------------------------------------------------
' XXX XXX XXXXXXXX XXXXXXXXX A New Way of Doing Things!
' XXX XXX XXXXXXXXX XXXXXXXXX
' XXX XXX XXX XXX XXX
' XXX XXX XXX XXX XXXXXX
' XXX XXX XXX XXX XXXXXX
' XXX XXX XXX XXX XXX
' XXXXXXXXX XXXXXXXXX XXX EXCEL
' XXXXXXX XXXXXXXX XXX USER DEFINED FUNCTION By: Larry Hash� - 2009 Email: lhasha@usa.com
' ------------------------------------------------------------------------------------------------------------

'DESCRIPTION
'Returns verbose english word string that represents a specified value.
'Optional style selection results in (1) lowercase, (2) Sentence Case, or (3) UPPERCASE.
'Optional enclosure value of TRUE adds leading and trailing asteriks (****) to word string.
'Optional CurrencyRef adds a verbose currency name to the end of the word string (i.e. dollar, euro etc.).
'Current function limits value inputs to -999999999.99 to 999999999.99

Dim NWord(100) As String

'DEFINE NUMBER WORD ARRAY 0 THROUGH 99
NWord(0) = "zero": NWord(1) = "one": NWord(2) = "two": NWord(3) = "three": NWord(4) = "four"
NWord(5) = "five": NWord(6) = "six": NWord(7) = "seven": NWord(8) = "eight": NWord(9) = "nine"
NWord(10) = "ten": NWord(11) = "eleven": NWord(12) = "twelve": NWord(13) = "thirteen"
NWord(14) = "fourteen": NWord(15) = "fifteen": NWord(16) = "sixteen": NWord(17) = "seventeen"
NWord(18) = "eighteen": NWord(19) = "nineteen": NWord(20) = "twenty": NWord(21) = "twenty-one"
NWord(22) = "twenty-two": NWord(23) = "twenty-three": NWord(24) = "twenty-four"
NWord(25) = "twenty-five": NWord(26) = "twenty-six": NWord(27) = "twenty-seven"
NWord(28) = "twenty-eight": NWord(29) = "twenty-nine": NWord(30) = "thirty": NWord(31) = "thirty-one"
NWord(32) = "thirty-two": NWord(33) = "thirty-three": NWord(34) = "thirty-four"
NWord(35) = "thirty-five": NWord(36) = "thirty-six": NWord(37) = "thirty-seven"
NWord(38) = "thirty-eight": NWord(39) = "thirty-nine": NWord(40) = "forty": NWord(41) = "forty-one"
NWord(42) = "forty-two": NWord(43) = "forty-three": NWord(44) = "forty-four": NWord(45) = "forty-five"
NWord(46) = "forty-six": NWord(47) = "forty-seven": NWord(48) = "forty-eight": NWord(49) = "forty-nine"
NWord(50) = "fifty": NWord(51) = "fifty-one": NWord(52) = "fifty-two": NWord(53) = "fifty-three"
NWord(54) = "fifty-four": NWord(55) = "fifty-five": NWord(56) = "fifty-six": NWord(57) = "fifty-seven"
NWord(58) = "fifty-eight": NWord(59) = "fifty-nine": NWord(60) = "sixty": NWord(61) = "sixty-one"
NWord(62) = "sixty-two": NWord(63) = "sixty-three": NWord(64) = "sixty-four": NWord(65) = "sixty-five"
NWord(66) = "sixty-six": NWord(67) = "sixty-seven": NWord(68) = "sixty-eight": NWord(69) = "sixty-nine"
NWord(70) = "seventy": NWord(71) = "seventy-one": NWord(72) = "seventy-two": NWord(73) = "seventy-three"
NWord(74) = "seventy-four": NWord(75) = "seventy-five": NWord(76) = "seventy-six": NWord(77) = "seventy-seven"
NWord(78) = "seventy-eight": NWord(79) = "seventy-nine": NWord(80) = "eighty": NWord(81) = "eighty-one"
NWord(82) = "eighty-two": NWord(83) = "eighty-three": NWord(84) = "eighty-four": NWord(85) = "eighty-five"
NWord(86) = "eighty-six": NWord(87) = "eighty-seven": NWord(88) = "eighty-eight": NWord(89) = "eighty-nine"
NWord(90) = "ninety": NWord(91) = "ninety-one": NWord(92) = "ninety-two": NWord(93) = "ninety-three"
NWord(94) = "ninety-four": NWord(95) = "ninety-five": NWord(96) = "ninety-six": NWord(97) = "ninety-seven"
NWord(98) = "ninety-eight": NWord(99) = "ninety-nine"

'ROUND VALUE TO NEAREST 100THS
Value = Round(Value, 2)
CurrencyRef = LCase(CurrencyRef)

'ERROR IF VALUE EXCEEDS UPWARD LIMITS (MUST BE LESS TAHN ONE BILLION)
If Abs(Value) > 999999999.99 Then Word = CVErr(xlErrNum): GoTo ErrorOut

'ASSIGN NEGATIVE TO WORD IF VALUE IS LESS THAN ZERO
If Value < 0 Then PreFix = "negative ": Value = Abs(Value)

'CONSTRUCT FRACTIONAL STRING
NStr = Trim(Str(Int(Value)))
TDec = Str(Value)
If InStr(1, TDec, ".") = 0 Then TDec = ".00"
If InStr(1, TDec, ".") = Len(TDec) - 1 Then TDec = TDec + "0"
NRem = Mid(TDec, InStr(1, TDec, ".") + 1)
If Len(NRem) > 2 Then NRem = Mid(NRem, 1, 2)

'CONSTRUCT VERBOSE NUMBER WORDS
If Len(NStr) < 3 Then 'ONES AND TENS
Word = NWord(Val(NStr))
End If

If Len(NStr) = 3 Then 'HUNDREDS
Word = NWord(Val(Mid(NStr, 1, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 2, 2)))
End If

If Len(NStr) = 4 Then 'THOUSANDS
Word = NWord(Val(Mid(NStr, 1, 1))) + " thousand "
Word = Word + NWord(Val(Mid(NStr, 2, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 3, 2)))
End If

If Len(NStr) = 5 Then 'TENS OF THOUSANDS
Word = NWord(Val(Mid(NStr, 1, 2))) + " thousand "
Word = Word + NWord(Val(Mid(NStr, 3, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 4, 2)))
End If

If Len(NStr) = 6 Then 'HUNDREDS OF THOUSANDS
Word = NWord(Val(Mid(NStr, 1, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 2, 2))) + " thousand "
Word = Word + NWord(Val(Mid(NStr, 4, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 5, 2)))
End If

If Len(NStr) = 7 Then 'MILLIONS
Word = NWord(Val(Mid(NStr, 1, 1))) + " million "
Word = Word + NWord(Val(Mid(NStr, 2, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 3, 2))) + " thousand "
Word = Word + NWord(Val(Mid(NStr, 5, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 6, 2)))
End If

If Len(NStr) = 8 Then 'TENS OF MILLIONS
Word = NWord(Val(Mid(NStr, 1, 2))) + " million "
Word = Word + NWord(Val(Mid(NStr, 3, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 4, 2))) + " thousand "
Word = Word + NWord(Val(Mid(NStr, 6, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 7, 2)))
End If

If Len(NStr) = 9 Then 'HUNDREDS OF MILLIONS
Word = NWord(Val(Mid(NStr, 1, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 2, 2))) + " million "
Word = Word + NWord(Val(Mid(NStr, 4, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 5, 2))) + " thousand "
Word = Word + NWord(Val(Mid(NStr, 7, 1))) + " hundred "
Word = Word + NWord(Val(Mid(NStr, 8, 2)))
End If

'ADD LEADING NEGATIVE TO WORD IF VALUE IS LESS THAN ZERO
Word = PreFix + Word

'ADD FRACTIONAL 100THS TO WORD
Word = Trim(Word + " and " + NRem + "/100" + " " + CurrencyRef)

'REMOVE UNEEDED REFERENCES
Word = Replace(Word, "zero ", "")
Word = Replace(Word, "hundred thousand hundred ", "hundred thousand ")
Word = Replace(Word, "thousand hundred ", "thousand ")
Word = Replace(Word, "million hundred thousand ", "million ")
Word = Replace(Word, "million hundred ", "million ")

'ADD BACK ZERO ON ZERO INTEGER VALUE
If Mid(Word, 1, 3) = "and" Then Word = "zero " + Word

'----STYLE OPTIONS---- Default or 1 = lowercase, 2 = Sentence Case, 3 = UPPERCASE

If Style > 3 Then Word = CVErr(xlErrValue): GoTo ErrorOut

'MAKE WORD SENTENCE CASE
If Style = 2 Then
Mid(Word, 1, 1) = UCase(Mid(Word, 1, 1))
For X = 2 To Len(Word)
If Mid(Word, X, 1) = " " Or Mid(Word, X, 1) = "-" Then Mid(Word, X + 1, 1) _
= UCase(Mid(Word, X + 1, 1))
Next X
End If

'MAKE WORD UPPERCASE
If Style = 3 Then
Word = UCase(Word)
End If

'----ENCLOSURE OPTION---- Default and FLASE = No enclosure, TRUE = Enclosure

'ENCLOSE WORD WITH LEADING AND TRAILING ASTERICS
If Enclose = True Then Word = "**** " + Word + " ****"

ErrorOut:

'RETURN VERBOSE NUMBER WORD STRING OR ERROR CODE
NumberWords = Word

End Function

Reply to Larry

Gaurav, on Jul 6, 2009 6:55:38 am BST

HI Tarali,

Please give me your mail id i wll snet you complete excel file with complete formula.

Reply to Gaurav

63 answers 12 Next

Ask your question Reply