Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Desparate excel help needed.....

  1. #1
    Guest

    Default

    I copied many columns of numerical data from a website into excel, even though the numerical data is in separate columns, excel is treating the numbers as text-I can't format it or use the data in calculations or formulas....can anyone help to convert them into numbers for use on Excel.
    Thanks in advance...
    Mary

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have problems with that myself and usually what I do is paste the data onto Notepad first, select all then paste it into Excel. Not sure if this is the best way, but I do it so infrequently I haven't bothered to find a better way.

    HTH

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There are probably a few ways to do that, one way might be to enter the numeral 1 in a cell, copy the cell, then hilite your range(s), click on Edit > Paste Special, choose Multiply in the Operation section, and hit OK. You could achieve the same effect by entering a 0 in another cell, copying that, and Paste Special for Add.

    Any help? If I misunderstood, sorry, please repost.

    Tom Urtis

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-12 18:18, Tom Urtis wrote:
    There are probably a few ways to do that, one way might be to enter the numeral 1 in a cell, copy the cell, then hilite your range(s), click on Edit > Paste Special, choose Multiply in the Operation section, and hit OK. You could achieve the same effect by entering a 0 in another cell, copying that, and Paste Special for Add.

    Any help? If I misunderstood, sorry, please repost.

    Or include the above in your formula eg
    =vlookup(A2+0,table,col,false) will do the same thing

    Tom Urtis

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    > Or include the above in your formula eg
    =vlookup(A2+0,table,col,false) will do the same thing

    Sam,

    A2+0 in VLOOKUP will not alter the format A2, while Edit|Paste Special >Add (adding a zero) will change text-formatted numbers permanently into items that Excel recognizes as numbers.

    Aladin

  6. #6
    Guest

    Default

    Highlight the column of data.


    Specify how it is to be parsed (fixed or delimited)

  7. #7
    Guest

    Default

    Let me try that again...
    highlight your column of data
    select "DATA"
    Select "TEXT TO COLUMNS"
    Specify how it is to be parsed - fixed width or delimited by spaces, tabs, commas or whatever

  8. #8
    Guest

    Default

    Thank you for helping....but I tried everything menitoned except for the VLOOKUP function...and nothing worked! Anyone else have any other suggestions, please be specific or detailed as to exactly how to convert text data into numercial data.
    Thank you all!!!
    Mary
    btw, the columns of data I have: one column is date, quantity, and price-but in all in text format.

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When you first paste to excel, try doing paste special, and try selecting different things, like html, text, etc.

    Or, when you paste to notepad, save it as a .txt file. Then open that file from within excel, and use the file importing wizard.

    If it turns out that say, there are always five spaces between columns, you could take the text into word, do a find and replace, finding " " and replacing with "," and this will make importing text easier into excel.

  10. #10
    New Member
    Join Date
    Feb 2002
    Location
    Presque Isle, Maine, USA
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi;

    Name the range of numbers-seen-as-text "FIX"
    and run this code from a module in the VBE (alt+F11):

    Sub fixem()
    Dim c as range
    Dim x as Long
    For each c in Range("FIX").cells
    x = c.value
    c.value = x
    Next
    End Sub

    Try it out on the first couple cells first just to be safe.

    Hope this helps...
    Gahagan

    [ This Message was edited by: Gahagan on 2002-03-13 21:30 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •