Desparate excel help needed.....

G

Guest

Guest
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
> 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
 
Upvote 0
Highlight the column of data.
<Data>
<Text to Columns>
Specify how it is to be parsed (fixed or delimited)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top