ExcelBanter - Removing Dirty Data From an Imported DOS database

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Removing Dirty Data From an Imported DOS database (https://www.excelbanter.com/excel-programming/368154-removing-dirty-data-imported-dos-database.html)

Removing Dirty Data From an Imported DOS database

Hello,

I am using Excel 2003. I have imported data from an old DOS database (don't
know name of it). This data comes from another department in a column
spreadsheet format and I have no control over how it is presented to me.

I transfer this data to Excel and try to work on it. I discover lots of
spaces after each field item eg intead of a word being clean as in "data"
(with four characters) I get "data " the word with additional but blank
characters. This problem exists in all the columns within the Excel sheet I
am working on.

An additional problem is if I clean the data by doing a find-replace. It
initially looks OK but if I then try to calculate with this corrected data I
get no results eg countif data should give a sensible result but it gives me
no result at all.

A sillier example would be as in countif "male" and countif "female". Say
this should be 50/50 = 100 on average but I am getting 50 male but 100 female
= 150 which is not sensible.

How please can I get around these problems? I am having to print out the
database and re-enter everything manually into a fresh Excel worksheet which
is taking me a vast number of hours each time.

Iain Scott

Removing Dirty Data From an Imported DOS database

I'd take it into Access first the create a query based on the table
something like;

SELECT Trim([Field1]) AS Expr1, Trim([Field2]) AS Expr2, Trim([Field3]) AS
Expr3
FROM Table1;

and export it to Excel.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Iain Scott" wrote:
| Hello,
|
| I am using Excel 2003. I have imported data from an old DOS database
(don't
| know name of it). This data comes from another department in a column
| spreadsheet format and I have no control over how it is presented to me.
|
| I transfer this data to Excel and try to work on it. I discover lots of
| spaces after each field item eg intead of a word being clean as in "data"
| (with four characters) I get "data " the word with additional but
blank
| characters. This problem exists in all the columns within the Excel sheet
I
| am working on.
|
| An additional problem is if I clean the data by doing a find-replace. It
| initially looks OK but if I then try to calculate with this corrected data
I
| get no results eg countif data should give a sensible result but it gives
me
| no result at all.
|
| A sillier example would be as in countif "male" and countif "female". Say
| this should be 50/50 = 100 on average but I am getting 50 male but 100
female
| = 150 which is not sensible.
|
| How please can I get around these problems? I am having to print out the
| database and re-enter everything manually into a fresh Excel worksheet
which
| is taking me a vast number of hours each time.
|
| Iain Scott

Removing Dirty Data From an Imported DOS database