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.
