Help removing "space" character from imported data

Help removing "space" character from imported data

Post by c21hcnRnYW » Wed, 02 Jul 2008 03:05:00


I am copying data from the web that's in a spreadsheet format and which can
be dropped straight into Excel. The information is yearly values that are
displayed for each month of the year. I have two problems as a result:

1. The data is "backward" - on the sheet I want to input this data, it
starts in year 2004 and moves month by month down the spreadsheet, ending
with 2008. The data on the website *begins* 12/08 and moves down the page so
that the earliest data is at the bottom rather than the top. (I resolved
this by putting the data in the order I want it and doing a vlookup against
the data I'm pasting from the web).

I have about 150 companies for which I have to update 2006, 2007 and 2008
data by month so being able to copy from the site, do a tiny bit of
manipulation and pasting the result into my ultimate spreadhseet would be
great and a real time saver (and much faster than hand-keying all the info!).

Thanks for any ideas you have on making this process faster and more
efficient.

mm



2. Second issue is that there is an extra space in the monthly data at the
end of the data. Conseqeuently, when I paste the vlookup result info (I also
copy that info and paste again in the sheet as a value . . .) each cell that
has data has a space at the end of it. I can go into each one and remove the
space but the "find and replace" feature won't remove the space universally.
 
 
 

Help removing "space" character from imported data

Post by c21hcnRnYW » Wed, 02 Jul 2008 03:07:01


2. Second issue is that there is an extra space in the monthly data at the

 
 
 

Help removing "space" character from imported data

Post by Pete_U » Wed, 02 Jul 2008 03:18:44

If you imported or copied that data from a web site you probably have
the non-breaking space character (char 160) instead of a normal space.
Here is a response I gave to another poster earlier today:

"...
Perhaps you had leading or trailing spaces in those cells, or, if you
downloaded the data from a web site, maybe the non-breaking space
character (char 160). You can highlight the data, do CTRL-H (Edit/
Replace) and:

Find what: <space>
Replace With: leave blank
click Replace All

Then CTRL-H again, and:

Find what: Alt-0160
Replace With: leave blank
click Replace All

where Alt-0160 means hold the Alt key down and type 0160 on the
numeric keypad, then release Alt.
..."

The second one applies particularly to you.

Hope this helps.

Pete




On Jun 30, 7:07爌m, smartgal < XXXX@XXXXX.COM >
 
 
 

Help removing "space" character from imported data

Post by Roger Govi » Wed, 02 Jul 2008 03:38:26

see response to your later posting

--
Regards
Roger Govier