Removing 2 extra spaces in front of dates in imported excel doc

[Hannah]

I export a report from a customer on the web. Their worksheet contains a
delivery schedule that I must compare to my schedule. I need to use a formula
to calculate the 20th day prior to their on dock date. The dates on their
worksheet have 2 additional unneeded spaces in the beginning of the date (in
each cell). I need to remove these spaces in order to format the cells and
then use a formula. I have tried everything from "Find & Replace" to retyping
each individual date. (There are about 2000 dates on this report).

Please help!

 

[Peo Sjoblom]

Run this macro


http://www.mvps.org/dmcritchie/excel/join.htm

same website also has instruction to install macros

note that sometimes date values converts to their raw serial numbers, the
values are still the same but you need to format them as dates again just
for display, for instance Apr 12th 2006 has a serial of
38819

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Alan]

Are you sure that they are spaces, CHAR(32) which is ASCII32 or CHAR(160),
ASCII160 which looks like a space but isnt?
If one of the the entries from your report were in A1, try
=CODE(A1) and see if it returns 32 or 160
If it returns 160, highlight the whole sheet and go 'Find and replace' > In
Replace what, hold down the ALT key and enter 0160, (nothing will show in th
box), leave 'Replace with' blank and hit 'Replace All'
Regards,
Alan.

 

[Hannah]

That was awesome! Thank you so much! I have been working on this for 2 weeks!

Thanks again!

 

[Peo Sjoblom]

You seem to have picked up the right macro although my link was slightly
incorrect

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

is the right link to the macro


Peo