MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Numbers copied from web site


Posted by Dee on November 10, 2001 11:42 AM

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?


Posted by JACK on November 10, 2001 11:56 AM


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

Posted by Aladin Akyurek on November 10, 2001 12:26 PM

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

Posted by Dee on November 10, 2001 12:44 PM

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

Posted by Dee on November 10, 2001 12:46 PM

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

Posted by Dee on November 10, 2001 12:47 PM

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

Posted by Aladin Akyurek on November 10, 2001 12:59 PM

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

========

Posted by Dee on November 10, 2001 1:58 PM

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

Posted by Aladin Akyurek on November 10, 2001 2:10 PM

Easy.

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

Copy down as far as needed.

Aladin

Posted by Dee on November 10, 2001 2:49 PM

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

Posted by Aladin Akyurek on November 10, 2001 2:55 PM

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

Posted by Dee on November 10, 2001 3:11 PM

Dee --

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

Posted by Aladin Akyurek on November 10, 2001 3:16 PM

Just sent you a workbook. See if that helps.

Aladin

Posted by Dee on November 10, 2001 3:30 PM

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.


Posted by Aladin Akyurek on November 10, 2001 3:42 PM

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

Posted by Aladin Akyurek on November 10, 2001 3:43 PM

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

Posted by Dee on November 10, 2001 3:50 PM

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.

Posted by Aladin Akyurek on November 10, 2001 3:55 PM

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

Aladin

Posted by Dee on November 10, 2001 3:57 PM

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