Dirty data cleanup in Excel.

[pcutler]

I’ve got an Excel issue and I’m a bit over my head with it.

I could really use a hand.

I’ve got a text file with 15,000+ records that I need to pull into Excel. The file contains names, phone numbers & addresses and is space delimited.

Some of the records have Mr. or Mrs before the name, some contain a middle initial or name, and some of the addresses have several spaces in them. All of this causes the data to be spread out over a number of columns and inter- mixed. The phone number column will contain names and initials instead of phone numbers for some records.

If a name includes Mr, Mrs, a middle name or initial, the last name will be moved over into the phone number column. The phone number for that record in turn will be pushed over into the first column of the address. It quickly goes downhill from there.

Is there a way in Excel to look at a column, and move the data for any cell containing text into the column to its left? The data needs to be added to the existing contents of the cell on the left, not replace it. If at the same time, the remaining cells in that row could be shifted to the left, the data would then appear in the correct columns.

I’d appreciate any input you have to offer.

Thanks in advance.

Peter.

 

[mintjulep]

For a manual approach I would do something along these lines:

Import into a single column.

Use TRIM() and CLEAN() to remove most of the multiple spaces and other non-printing junk characters that may be in there.

Copy and Paste Special | Values the Trim Clean text into another column.

Use Find and Replace to replace "Mr.", "Mrs." etc. with nothing. (assuming that you want to remove that from the data)

Attack what is left with Data | Text to Columns delimited

This should get you something slightly less messy than your original situation.

Use sort and filter to identify and isolate the various "types" or categories of problems that now exist and attack each type separately.

 

[SkipVought]

hi,

Your explanation is vague and almost meaningless.

Please COPY - PASTE several representative rows of data here, using the TT TGML TAG. If you do not know what TGML Tags are, please SEARCH for TGML on this page, and then look for the TT tag.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Madawc]

I'd suggest putting the data into MS Word and cleaning it there. The 'replace' function is much more versitile that that for Excel. Choose [More] and then [Special] to see the options.

You can also convert tabular data to text and back again, I've always found this convenient for data clean-up.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP