Back to Edit in Excel archive index

Back to archive home

I've copied numbers from a web site. When I use those numbers in a formula in Excel 2000 I get a "Value" error. The numbers I copied are probably not true Excel numbers. I don't know what format they're in. I've tried reformatting them using "number" but that doesn't work. Is there a way to turn them into numbers that Excel recognizes?

Post up an example of the numbers and also what result you want: Should not be to difficult..

Put 1 in an empty cell, copy this cell, select all those cells that look like numbers, activate Edit|Paste Special the select the option Multiply.

Aladin

Here are samples of the numbers that I copied from a web site. Thank you

111-07 110-11 110-15 110-13 110-14

Sorry I forgot to paste the numbers.

Here are samples of the numbers that I copied from a web site. Thank you

I'm not sure I understand your answer. Could you pleade be more explicit? Thank you

Dee,

111-07 110-11 110-15 110-13 110-14

Things like these (from your post below)aren't numbers. What do they represent? Give the URL of the Web site from where you copied them, if you like.

Aladin

========

Dee,

Here is the web site from where I copied the numbers. I then replaced the dashes with dots. Is it possible to convert those numbers to a format that I can use in a formula? Again, thank you in advance for any help.

http://www.cbot.com/cbot/quotes/fin_futures_settlement_detail/0,1907,12+424+426,00.html

Easy.

=SUBSTITUTE(A1,"-",".")+0

Copy down as far as needed.

Aladin

I just used the answer you gave me & I got a #VALUE error again. ??????

Dee --

> I just used the answer you gave me & I got a #VALUE error again. ??????

I copied the table from that Web site and bingo --> #VALUE! error.

Those cells contains strange chars. But this works:

=SUBSTITUTE(SUBSTITUTE(B3,CHAR(202),""),"-",".")+0

Aladin

Dee --

Thank you for sticking with me! Sorry that one didn't work either. Another #VALUE! error.

Just sent you a workbook. See if that helps.

Aladin

THANKS, THANKS! I tried copying your formula that worked for B2 (I tested it with =ISNUMBER(B2) & it tested TRUE), the formula changed to use B3 but again I got that darned #VALUE! message. Does the formula have to be changed for each cell? I appreciate your e-mail.

After inserting a column next to the column of "numbers" that we try to convert,

in C2 enter: =SUBSTITUTE(SUBSTITUTE(B2,CHAR(202),""),"-",".")+0

You can copy down this without any adjustment. It will work for all cells that contain those "web numbers".

Aladin

After inserting a column next to the column of "numbers" that we try to convert,

in C2 enter: =SUBSTITUTE(SUBSTITUTE(B2,CHAR(202),""),"-",".")+0

You can copy down this without any adjustment. It will work for all cells that contain those "web numbers".

Aladin

I did just that but again, the error message. If you look at your e-mail the formula was copied down & all the other cells had the same error message.

Would you send me a copy of your own workbook (just) containing the copied web table?

Aladin

I just emailed you the workbook. As you can see the formula works for B2 but not for any of the other cells.

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.