Desparate excel help needed.....

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

[Mark O'Brien]

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

[Tom Urtis]

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

[SamS]

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

[Aladin Akyurek]

> 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

Highlight the column of data.


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

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

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.

[tk19]

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.

[Gahagan]

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 ]