+ Reply to Thread
Results 1 to 12 of 12

Converting text numbers into real numbers

  1. #1
    Registered User
    Join Date
    01-04-2009
    Location
    big rapids, mi
    MS-Off Ver
    Excel 2007
    Posts
    10

    Converting text numbers into real numbers

    I have copied a report into Excel 2007 using HTML. After pasting the copy, the report appears nicely in columns.

    Three of the columns contain numbers as text.

    I need to be able to use these numbers in calculations. So I need to convert the text to numbers. I have tried the copy multiply method without any success.

    No matter what I try, when I use the resulting number in a formula I get !VALUE.

    Here is one of the numbers $3,538.00.

    Any ideas on how to get these text cells into numbers so they can be used in calculations?
    Last edited by vheying; 08-04-2010 at 04:14 PM.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Converting text numbers into real numbers

    This is extremely heavy handed, but you may be able to amend it to suit your needs..

    =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),",",""),"*",""),"$",""))
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Converting text numbers into real numbers

    If your only problem is that the numbers have an asterisk (*) appended to them,
    try this:

    • Select your range of data
    • Home.Find&Select.Replace
    ...Find what: ~*
    ...Replace with: (leave this field blank)
    ...Click: Replace all

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    01-04-2009
    Location
    big rapids, mi
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Converting text numbers into real numbers

    There is an error in my original post. The text 'number' does not contain an asterisk The number should be $3,538.00.

    I have made many of these conversions a year ago. It was easier than the substitute method. This worn out mind has forgotten how I did it.

  5. #5
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Converting text numbers into real numbers

    There will undoubtedly be simpler solutions, but this will do what you require,

    =VALUE(SUBSTITUTE(A1,"$",""))

  6. #6
    Registered User
    Join Date
    01-04-2009
    Location
    big rapids, mi
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Converting text numbers into real numbers

    The problem is that inside Excel, Excel knows that $3,538.00 is not a number. So even after removing all punctuation, and then multiplying by 1, I receive the error #VALUE!.

    Removing the special characters is not the problem. The problem is how to tell Excel that this is really a number.

  7. #7
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Converting text numbers into real numbers

    There will undoubtedly be simpler solutions, but this will do what you require,

    =VALUE(SUBSTITUTE(A1,"$",""))
    Try it - the result may astound you.

  8. #8
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Converting text numbers into real numbers

    I'm curious, is there text in the number or is there whitespace at the end of the number? For example, does the number appear like this (without the quotations) "1234 "? I had a problem with source numbers having a whitespace at the end of it but it has been resolved with this code:
    Please Login or Register  to view this content.
    this removes any text or whitespace from the end of the number.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  9. #9
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Converting text numbers into real numbers

    Hi
    Try; Find a blank cell, anywhere, and place a 1 (number one), ensure it is formatted as a number.
    Then say colmns A and B are the colmns saved as text, then copy the cell with the 1 in it, highlight both the colmns,and special paste- multiply.
    This will convert what ever you have highlighted to numbers without the need for formulas.
    Hope this helps
    Regards
    Peter
    Sorry already tried that, didn't read to good did I?
    PS can you please save a copy in 2003 of an example and attach it.
    Regards
    Peter
    Last edited by peterjuhnke; 08-04-2010 at 05:29 PM.

  10. #10
    Registered User
    Join Date
    01-04-2009
    Location
    big rapids, mi
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Converting text numbers into real numbers

    I found a very awkward solution.

    I copy the report to an Excel spreadsheet and to a Word document. The report appears in Word as tables.

    In Word I remove all punctuation except periods. Then I copy the number columns and paste them into the Excel spreadsheet as text. In Excel I do the copy multiply thing. The result is cells that Excel considers to be numbers.

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Converting text numbers into real numbers

    If as you say these values are coming via HTML then you may find they include CHAR(160)

    With your original data in place - and assuming US config. - you can try running an Edit -> Replace - in the Find What hold down ALT and type 0160 on the numeric keypad (see note) and leave Replace With as blank.

    note: on a laptop hold ALT + FN and type 0160 on the numeric keypad [ie not the numbers above the letters]

    If the above does not work we'd need to see an example to better assist I think.

  12. #12
    Registered User
    Join Date
    09-28-2011
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Converting text numbers into real numbers

    I tried the following solution and it worked for me:-
    (a) First format cells and define it as a number with nil decimals, Dollar will drop off and any zeroes after last significant digit will go.
    (b) Then use TRIM functionality on the data set which will remove all commas, Drag the formula along the data set.
    (c) Copy paste special - values for this column.
    (d) Use Text to Column function for this trimmed data set
    (i) Select Delimited (ii) In the next screen select Tab and Space (iii) In the third screen let default values prevail (iv) Click on finish
    (e) Insert a new column adjacent to this converted column
    (f) Insert function "Value" against first data field and select the first data field and hit return
    (g) Copy the formula along the entire column data set
    (h) Copy paste "Special" for the converted values
    (i) You will get a number

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1