How do I delete dead cell space?

  • Thread starter Thread starter Jeffrey
  • Start date Start date
J

Jeffrey

I have a report that was exported from a system that I have no control over.
It's an address list that exported in CSV format. I'm running Excel 07.

I'm creating a simple address list to do a mail merge. Just a few headers
such as this:

(A)First Name, (B)MI, ©Last Name, (D)Address, (E)City, (F) State, (G)Zip

Pretty simple until I do a mail merge and it's outputting like this:

John _____ A Smith_________
123 Main St________________________
Anytown___________ USA___ 12345

The dashes are actually there and blue on the merge, but in the excel file,
there are no dashes.... just SPACE. If I click on cell A2 to select the
first name of this record, and then depress F2 to "edit it", the cursor is
blinking about 5 or 6 spaces to the right of the last character. Each column
has this "dead space" after the text with the exception of the MI column.

Is there any quick way to remove this dead space from the end of all these
cells? This file has 250 rows x 7 rows. (1750 individual cells I would have
to manually edit the space out of.

I have already tried "Clearing Formats" in the Editing block of the Home
tab. - It didn't work.

I hope there is someone here that understands what this is. I am quite
comfortable using excel... just never had this come up before. Any and all
feed back appreciated!!

Jeffrey
 
If those are normal spaces, you can highlight all the data and do CTRL-
H (Find & Replace) as follows:

Find what: <space><space>
Replace with: leave blank
Click Replace All.

By removing double-blanks you will keep the single blanks that can
exist in your addresses. You may end up with a single blank at the end
of some fields, but I guess you can live with that.

Hope this helps.

Pete
 
If you went to Sheet2!A1 and put in:

=TRIM(Sheet1!A1)

Then Fill->Right for 6 more, then Fill-Down (columns A:G) for 249
more, you should have a cleared data table. Cop and Paste Special-
Values back to Sheet1 if desired.
Click to expand...

BTW, your .CSV is likely exported as a "Fixed Width, Comma
Delimited" .TXT file. If you cannot get rid of the fixed width option
when it is created, then go the long route to File->Import->Import
Wizard rather than simply double-clicking it to auto-open in Excel.
There is an option to use fixed width within the wizard.
 
WOW! Both ideas are great, I used the Trim command. Worked perfectly!

Thank you so much!
 
Back
Top