Archive of Mr Excel Message Board


Back to Edit in Excel archive index
Back to archive home

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?

Re: Numbers copied from web site

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..


Re: Numbers copied from web site

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


Re: Numbers copied from web site

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


Re: Numbers copied from web site

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


Re: Numbers copied from web site

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


Re: Numbers copied from web site

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

========


Re: Numbers copied from web site

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


Re: Numbers copied from web site

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

Easy.

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

Copy down as far as needed.

Aladin


Re: Numbers copied from web site

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

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


Yeah, me too (Re: Numbers copied from web site)

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


Re: Yeah, me too (Re: Numbers copied from web site)

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.


Re: Yeah, me too (Re: Numbers copied from web site)

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

Just sent you a workbook. See if that helps.

Aladin


Re: Yeah, me too (Re: Numbers copied from web site)

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.



Re: Yeah, me too (Re: Numbers copied from web site)

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


Re: Yeah, me too (Re: Numbers copied from web site)

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


Re: Yeah, me too (Re: Numbers copied from web site)

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.


Re: Yeah, me too (Re: Numbers copied from web site)

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


Re: Yeah, me too (Re: Numbers copied from web site)

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.


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.