yahoo download spreadsheet
George
I'm trying to download data from a webpage into an excel
sheet. The resultant excel sheet
does not contain the data in the appropriate rows/columns.
System:
Microsoft Office 2000 Professional
Internet Explorer version 5.00 with update SP2
1) access to webpage http://biz.yahoo.com/p/_noncyc-
bevnon.html
2) click on "Download Spreadsheet"
3) save as filename 'abc' with format 'txt' and
encoding 'Western European (Windows)'.
This file already has the data mixed up. (see all errors a
to e below)
4) At excel, menu 'Data', submenu item 'Get External
Data', with delimitor(s) of a comma selected.
Errors
The result contains the following errors:
a) The first column does not alway contain the entire
company's name.
b) The last column does not always contain one piece of
info. Sometimes it contains a value and a part of the
company's name.
c) The company's name enclosed with double quotes
d) Some second columns contains the word 'In.' or 'S.A.'
subsequent data are right shifted by one column.
e) Some rows only contain a partial name and the
associated data is found in the subsequent row.
I suspect the company's name are too long and/or contain
special characters (periods, comma,
hyphen). Also, there is possibly no carriage return or tab
key at the end of each row.
How can one create an excel sheet that contains a given
company and all its data on a single row
(as in the original webpage above, but with the excel
capabilities to delete rows and sort by several variables)
at the same time.)? Note: the header line is nice to have
but is not important as one can manually
enter this with little time.
Please show a simple procedure to achieve the correct data.
Your assistance is most appreciated.
Thank you,
adetaylor
and right-click>Copy. Open a fresh Excel worksheet
and paste it.
It seems to work for me.
--Brian Taylor
Refinate (C)2001, 20 free trials.
Click to: Rearrange Cells or Cell Content,
Find Duplicates, Count Items, Import, much more.
www.adetaylor.com
"George" <george3...@yahoo.com> wrote in message
news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03...
George
Mr. Taylor,
Your suggestion works! Terrific! Thank you!
Just one followup question:
The 'sort' function creates strange results on only one
particular column (market capitalization) where it appears
all the 'values' are treated as characters.
Sampling of data after a sort is 1.9B, 10.7B, 138.4B,
14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M'
is
millions.
How can one convert all these 'values' into the proper
numbers?
Thank you again,
George
>.
>
adetaylor
Then Edit>Replace hold the Alt key and use
the number keypad on the right of the keyboard
to enter 0160 as the "Find what". Be sure
the "Replace with" is empty. Replace All.
That is a common HTML character that looks
like a space.
Alternatively, download Refinate and use it
for free to convert to numbers by dragging
and clicking (apply Refinate's Number-Values action).
--Brian Taylor
Refinate (C)2001, 20 free trials.
Click to: Rearrange Cells or Cell Content,
Find Duplicates, Count Items, Import, much more.
www.adetaylor.com
"George" <george3...@yahoo.com> wrote in message
news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04...
Jon Peltier
there is text, not numbers, in the cells. But I see 'M's and 'B's, which are
text, too. Here's how to change them into numbers that Excel understands:
Select the numbers.
Do a find and replace, and replace B with E9, then replace M with E6.
Billions are 10^9 or 1E9, and Millions are 10^6 or 1E6. This converts the
non-numbers into numbers (I did a little test just now), so they can be
sorted normally.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <a36R8.17345$Fv1.1...@newsread2.prod.itd.earthlink.net>,
adetaylor said...
adetaylor
and didn't take enough time to understand the
question properly but figured it might help to
know about character 160.
--Brian
"Jon Peltier" <jonpe...@yahoo.com> wrote in message
news:O8Yu7dmGCHA.2404@tkmsftngp08...
adetaylor
and didn't take enough time to understand the
question properly but figured it might help to
know about character 160.
--Brian
"Jon Peltier" <jonpe...@yahoo.com> wrote in message
news:O8Yu7dmGCHA.2404@tkmsftngp08...
George
Hi Jon & Brian,
I tried the character 160 and wasn't getting any
results, so I thought it was my computer and will
troubleshoot that over next little while.
It is useful info to know of the character 160.
The transfer of data into the excel sheet and the sort
function works well. Thanks to both of you.
Much appreciated,
George
>> >>Í{ wÀ E lgQ_4ú*_<zS ì[ >
>.
>
David McRitchie
to convert character 160 to nothing. CHR(160) is the
non breaking space character ( ) in HTML and
should be converted to a normal space.
FWIW the CLEAN Worksheet Function is also
sometimes suggested and would only affect
char(0) through Char(31), Char(129), Char(141),
char(143) and Char(144).
To fix up unwanted extra spaces afterwards there is
TRIM to remove spaces left and right; and replace can be
used to replace 2 spaces with 1 space depending
on your data.
There is a difference between TRIM in Excel and in VBA.
Excel will reduce internal spaces, VBA will not.
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"George" <george3...@yahoo.com> wrote in message news:1146201c21ab4