How to convert number to text in excel

Jevin · #1 20-10-2009

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.

Mecurtis · #2 20-10-2009

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

Solaris · #3 20-10-2009

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.

yuppicide · #4 20-10-2009

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.

tijusxxx · #5 31-12-2010

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

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

EINSTEIN_007 · #6 31-12-2010

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.

Janos™ · #7 07-05-2012

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

Phalguni88 · #8 10-05-2012

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.

Jaffary · #9 13-05-2012

I also need this
im going to use it.

Thanx

ronaldstanley · #10 17-05-2012

you can put the VB script in the excel by using the VB script you can convert the number to text.

Thread Tools	Search this Thread
Show Printable Version Email this Page	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 03:35 PM
Need help to convert a Text file to Excel via VBA Macro	DEvOTeEL	MS Office Support	1	24-02-2012 12:47 PM
How to convert a decimal number into words in Excel using VBA?	Gandhik	Windows Software	3	26-11-2010 11:59 PM
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

How to convert number to text in excel

Software Development