|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
How to convert number to text in excel
Hello friends, I have facing some issue from few days where i am trying to create some formula to convert number to text in Excel 2007. I am trying to create one formula but not getting it properly, i am trying to use =text(value,format_text) but not able to get output. Can anyone help me out with this issue. Thanks in Advance. |
#2
| |||
| |||
Convert number to text in excel
If you really want to convert number into text then open Excel and press ALT+F11 to start Visual Basic Editor. Once it get open then click on insert menu and then click Module. Now copy paste the following code Code: Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollar, Cent, Temp Dim Decimal_place, 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. Decimal_place = InStr(MyNumber, ".") ' Convert cent and set MyNumber to dollar amount. If Decimal_place > 0 Then Cent = GetTens(Left(Mid(MyNumber, Decimal_place + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, Decimal_place - 1)) End If Count = 1 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollar = Temp & Place(Count) & Dollar If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollar Case "" Dollar = "No Dollar" Case "One" Dollar = "One Dollar" Case Else Dollar = Dollar & " Dollar" End Select Select Case Cent Case "" Cent = " and No Cent" Case "One" Cent = " and One Cent" Case Else Cent = " and " & Cent & " Cent" End Select SpellNumber = Dollar & Cent 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 |
#3
| |||
| |||
Convert text to number in Excel 2007
I am also facing similar type of issue in excel but over here i need to convert text to number, can anyone tell me how to do that, when i asked my friend about he he told me to click on Format then goto cells option then select Number and finally Text command but that didn't help me a lot. Does anyone know any proper solution or any code which i can use and convert text to number. |
#4
| |||
| |||
How to convert number to text in excel
If you want to convert number t text in excel then you can make use of TEXT function which allows you to converts a value to text in a specific number format. For that you need to insert a temporary blank column lets take it as B and then in Cell B3, enter the following formula =TEXT(A3,"0"). Now fill the formula in B3 down to B4:B100. Now you need to change the formulas to values in order to have them become text. Highlight cells B3:B100. Then use Ctrl+C to copy, then click Edit > Paste Special > Values > OK. Now the entries in column B will now be text versions of the numbers in column A. Copy column B back into column A and delete the temporary column B. |
#5
| |||
| |||
but plz tell me how to get this into excel. I mean after putting any no how will i get the text value. i.e 128/- = one hundred twenty eight ![]() ![]() ![]() Quote:
|
#6
| |||
| |||
Re: How to convert number to text in excel
Hi tijusxxx, You can download Excel Convert Numbers to Text Software from here that can easily convert numbers or currency to words, in MS Excel. For example, "7" becomes "seven". Also, convert numbers to letters in cells (for example: A = 1, B = 2, etc.). Note that Excel 2000 or higher is required. This is the trial version of the software so it might be limited to usage.
__________________ Education, Career and Job Discussions |
#7
| |||
| |||
Re: How to convert number to text in excel
I will provide a modified code for anyone who wants to use it and it can be used for any currency, whether its Indian or American, which is given to you can accordingly change the currency inputs, however copy this code to regular VBA module of your workbook: Code: Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Rupees, Paise, 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 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 <> "" 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 = "Zero Rupees" Case "One" Rupees = "One Rupee" Case Else Rupees = Rupees & " Rupees" End Select Select Case Paise Case "" Paise = " and Zero Paise" Case "One" Paise = " and One Paise" Case Else Paise = " and " & Paise & " Paise" End Select SpellNumber = 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 |
#8
| |||
| |||
Re: How to convert number to text in excel
There is a add-in for that. Called as Spell Currency Excel add-in. This add-in helps you to convert a numerical currency value to text. I had used this and it works nicely. You need to use a formula for that called as SpellCurr after installing this plug-in. Usually this add-in is a kind of macro tool that can be used. You need to copy the script of this adding which you can find from web, and then create macro. Run the macro and then use SpellCurr syntax. |
#9
| |||
| |||
Re: How to convert number to text in excel
I also need this im going to use it. Thanx |
#10
| |||
| |||
Re: How to convert number to text in excel
you can put the VB script in the excel by using the VB script you can convert the number to text. |
![]() |
|
Tags: convert number, excel 2007, office 2007, text |
Thread Tools | Search this Thread |
|
![]() | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find the number of text repeated in columns of Excel | Jigisha | MS Office Support | 4 | 24-02-2012 04:35 PM |
Need help to convert a Text file to Excel via VBA Macro | DEvOTeEL | MS Office Support | 1 | 24-02-2012 01:47 PM |
How to convert a decimal number into words in Excel using VBA? | Gandhik | Windows Software | 3 | 27-11-2010 12:59 AM |
How to convert vb .text to number format | Brunoz | Software Development | 6 | 04-07-2009 09:18 PM |
How to count number of text occurences in Excel column | Anas | Windows Software | 3 | 09-06-2009 05:01 PM |