zip code - can't trim preceding space

[jlajoie]

I've got my ZIP codes formatted as text so that the 0's don't disappea
when I merge the file into word, but I've got a space before ALL o
these ZIP Codes and cannot seem to get the trim formula to work. Fo
example,

Cell A1 is this:
(the space I want to trim)02087

In Cell B1, I enter:
=TRIM(A1)

But nothing transfers into Cell B1 and the formula appears as text i
the cell...Help

Attachment filename: mailing list sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56530

 

[Ken Wright]

I'll bet you inserted a column to do this, and that column was B. When you do
this, the column takes on the characteristics of the column to it's left, so all
your cells will automatically have been formatted as text, same as your zips.
Format the column as general and then redo your formula.

 

[David McRitchie]

They are probably Char(160) instead of spaces.

First format your column as text, then run the
fixuszip5 macro in join.htm
http://www.mvps.org/dmcritchie/excel/join.htm#fixuszip5

If you were to run TRIMALL macro you would get numbers
after removing the nonbreaking space character (&nbsp or
Char(160).

In Mail Merge
to alleviate formatting problems such as with currency so you use what you see in Excel: (#DDE)
In the Confirm Data Source dialog box,
click the MS Excel Worksheets via DDE (*.xls), and then click OK.
more information and references on my site for Mail Merge
http://www.mvps.org/dmcritchie/excel/mailmerg.htm

You can check if it was a char(160) by using CODE Worksheet Formula
it only looks at the first byte, so sometimes you have to use MID or RIGHT
but for this you only want to know what the first byte is
=CODE(A1)
more information in
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
http://www.mvps.org/dmcritchie/excel/strings.htm

 

[Ken Wright]

Hi Dave

Likely to be the case anyway, but given the Ops words

I'm pretty sure the cell is formatted as text and stopping his formulas working.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

 

[David McRitchie]

But you want text for zip codes, even for US zip code.
You do not want a mixture of 5 digit numbers and zip+4
text that won't sort properly, And you don't want a mixture
of 5 and 9 digit numbers they won't sort properly.

Mail Merge considerations were mentioned earlier for
those with Excel 2002, no problem in Excel 2000.

They are text because they have a char(160) in front, but the
char(160) will not be acceptable to mail merge creating US
postal bar codes.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

Hi Dave

 

[Ken Wright]

LOL - we're talking by each other here Dave. My suggestion was for him to
format his helper column (ie Column B with his formulas) as general so that when
he puts his formulas in it they work and don't display as text formulas. As
long as column A remains as text as I assume it is, then TRIM(A1) will still
return a text value to B1 and not drop any leading 0s. I wasn't suggesting
formatting his column of Zips as general.

That having been said, I know zip ( excuse the pun :-> ) about US zip codes, so
I may be missing something here anyway ;-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

But you want text for zip codes, even for US zip code.
You do not want a mixture of 5 digit numbers and zip+4
text that won't sort properly, And you don't want a mixture
of 5 and 9 digit numbers they won't sort properly.

Mail Merge considerations were mentioned earlier for
those with Excel 2002, no problem in Excel 2000.

They are text because they have a char(160) in front, but the
char(160) will not be acceptable to mail merge creating US
postal bar codes.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

Hi Dave

Likely to be the case anyway, but given the Ops words


I'm pretty sure the cell is formatted as text and stopping his formulas working.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------



you
see in Excel: (#DDE) you
do left, so
all