Results 1 to 10 of 10

Thread: How to convert number to text in excel

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

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

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

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

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

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

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

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

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

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

Similar Threads

  1. Find the number of text repeated in columns of Excel
    By Jigisha in forum MS Office Support
    Replies: 4
    Last Post: 24-02-2012, 04:35 PM
  2. Need help to convert a Text file to Excel via VBA Macro
    By DEvOTeEL in forum MS Office Support
    Replies: 1
    Last Post: 24-02-2012, 01:47 PM
  3. Replies: 3
    Last Post: 27-11-2010, 12:59 AM
  4. How to convert vb .text to number format
    By Brunoz in forum Software Development
    Replies: 6
    Last Post: 04-07-2009, 09:18 PM
  5. How to count number of text occurences in Excel column
    By Anas in forum Windows Software
    Replies: 3
    Last Post: 09-06-2009, 05:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,624,814.65542 seconds with 17 queries