Hi Iain

Take a look at David McRitchie's TRIMALL function.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Regards

Roger Govier


"Iain Halder" wrote in message
...
Hi,

Hope someone here can help me with an irritating problem.

I regularly receive an excel spreadsheet (Excel 98) which is derived
from an old DOS database.

When I try to work on this database (in Excel 2003) using normally
correctly working countif's and sumpoduct's etc I am stymied by what
appears to be dirty or hidden data in the spreadsheets.

There are lots of additional spaces in the individual field items.
However when I clean those I can to delete the blank spaces so that,
say, "data " is corrected to "data" and then try to analyse the
individual fields I get incorrect results so that countif "data" for
example instead of generating the correct number of items results in a
zero result.

Another odd result is countif "male" and countif "female" - which is
usually roughly 50/50. I'm getting a result of 50 "male" and 100
"female" which, obviously, is daft.

I'm at a loss to resolve this except by printing out the original
spreadsheet and then opening a new fresh spreadsheet and typing the
individual columns and rows myself which takes me hours.

Is there a facility within Excel 2003 (or even the new beta 2006
version) which can take data from ancient sources and totally clean
them of polluting material?

Thank you in advance!

Iain Halder
Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<