desperate... trim function issue...

  • Thread starter Thread starter TerrieS
  • Start date Start date
T

TerrieS

I'm on 97 office and need help.
I have a mailing list but the 5/digit & zip plus 4 digit
zip codes are screwed up. Most have an extra space or two
in front of the zip, so when my mailing house runs it
through their mailing software program, it truncates it
to just 3 digits, kicking out a large portion of the
addresses as undeliverable. I'm on a very limited time
frame here (like tomorrow....?). How can I get the extra
spaces out of the front of the zip codes in these
columned cells, without going into each cell and deleting
the extra 1 to 3 spaces? I found instructions for "trim"
but I couldn't make it work. What am I doing wrong???

Heeeeeeeeelllllllllllppppppppppp!!! Thanks, so much, save
my butt please!!!???
 
Terrie,

In B1, add =TRIM(A1), then copy down column B.

Then select column B, copy, goto Edit>Pastespecial, and select Values

Then delete column A

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Terrie!

If you're having problems with the TRIM function it could be that the
space is CHAR(160) instead of the normal CHAR(32) that TRIM handles.

You could use:

=--SUBSTITUTE(A1,CHAR(160),"")

Or Dave McRitchie has a subroutine that handles both spaces and
CHAR(160) at:

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Seems you could just select the column containing the
Zips, press CTRL-H to invoke the replace command, and
replace the space character with nothing. Just press the
space bar when the cursor is in the Find What box, leave
the replace With box empty, and click the Replace All
button.

LEB
 
You can do that, it's a bit more work than using a macro,
but either way you want to first format the column as text;
otherwise, you will lose the leading zero of the zip codes
that begin with a zero.
 
Correct. To eliminate the manual work, this whole process
could be recorded as a macro.
-----Original Message-----
You can do that, it's a bit more work than using a macro,
but either way you want to first format the column as text;
otherwise, you will lose the leading zero of the zip codes
that begin with a zero.

"LEB" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top