Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to convert number to text in excel

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 20-10-2009
Member
 
Join Date: Nov 2008
Posts: 89
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.

Reply With Quote
  #2  
Old 20-10-2009
Member
 
Join Date: Apr 2008
Posts: 193
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
Reply With Quote
  #3  
Old 20-10-2009
Member
 
Join Date: Apr 2008
Posts: 240
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.
Reply With Quote
  #4  
Old 20-10-2009
Member
 
Join Date: Feb 2008
Posts: 130
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.
Reply With Quote
  #5  
Old 31-12-2010
Member
 
Join Date: Dec 2010
Posts: 1
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:
Originally Posted by Mecurtis View Post
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
Reply With Quote
  #6  
Old 31-12-2010
Member
 
Join Date: Dec 2007
Posts: 2,268
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.
Reply With Quote
  #7  
Old 07-05-2012
Member
 
Join Date: Jan 2006
Posts: 605
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
Reply With Quote
  #8  
Old 10-05-2012
Member
 
Join Date: May 2011
Posts: 432
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.
Reply With Quote
  #9  
Old 13-05-2012
Member
 
Join Date: Apr 2009
Posts: 2
Re: How to convert number to text in excel

I also need this
im going to use it.

Thanx
Reply With Quote
  #10  
Old 17-05-2012
Member
 
Join Date: May 2012
Posts: 7
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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to convert number to text in excel"
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 10:18 PM
How to count number of text occurences in Excel column Anas Windows Software 3 09-06-2009 06:01 PM


All times are GMT +5.5. The time now is 02:08 PM.