Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / August 2007

Tip: Looking for answers? Try searching our database.

Format of downloaded data

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MikieSlats - 21 Aug 2007 02:48 GMT
> A data provider enables me to download real estate data that includes a
column of sale prices in dollar amounts.

> The search through Internet explorer shows a page of data which can then be
> downloaded to Excel.  However the dollar items come into Excel as labels
> rather than numbers which then prevents me from manipulating this data.
>
> This only happens when I download the data with my office desktop  computer.
>  When I download it at home or on my laptop the data comes through as numbers
> not labels.  This suggests that I have a setting which is incorrect on my
> office desktop computer probably in IE.
>
> Does anybody have any thoughts?
>
> Mikie
Wild Bill - 21 Aug 2007 06:47 GMT
After importing do the imported cells show Text under menu format/cells?

I don't know how it happened but you can at least deal with it if you
don't figure it out. I'll address that, though hopefully someone else
who's done a lot of web-grabs will recognize your plight and explain
just why it happened.

Are there extraneous characters on the labels, such as a trailing minus
sign?  If not, then they may look like "labels" (text) but you can still
do your math on them.  Sometimes Excel "imports as text" and the usual
remedy is to click a blank cell, copy it, select the imported values,
and Paste Special, clicking on the "Add" check box.  This would "add
zero" to each of the selected cells, ridding the text aspect.

The Text format can have another nasty effect that may be what you're
running into.  If the cell you do the manipulation in ("the formula
cell") is text format (check menu format/cells/number), then if you type
=a1*1.1, then that's all you get - no multiplication or math is done at
all.  You need to format the formula cell as General or Number before
entering the formula in that situation. (Or fix them after the fact by
replacing = with = .)

Finally, if there are those extraneous characters, they can be stripped
off using worksheet functions like LEFT, MID and LEN. Fortunately these
are not necessary to handle commas.  If cell A1 shows 175,000, you can
still go =a1*1.1 (again, enter the formula in a nontext cell). (However
keep in mind that there may be invisible characters picked up from a web
page, often at the beginning or end of the range.)

Obviously you'd like to avoid all of that and just get it as numeric in
the first place. My guess there would be that you're not selecting
exactly the same data to copy/paste. See if your situation persists if
you select EXACTLY the same thing. My recollection is that with some
data transfers that Microsoft looks at the first value copied and
decides text vs. numeric from that - but I'm not certain if or when that
occurs.  Maybe you're copying text headings in one situation but not the
other?

>> A data provider enables me to download real estate data that includes a
>> column of sale prices in dollar amounts.
[quoted text clipped - 11 lines]
>>
>> Mikie
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.