# Thread: How to convert number to text in excel

1. 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. 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. 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. 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. 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    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```  Reply With Quote

6. Member
Join Date
Dec 2007
Posts
2,291

## 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. 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. Member
Join Date
May 2011
Posts
434

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

office 2007, excel 2007, text, convert number 