If a zip code ends with 4 zeros, I'd like to eliminate them and have the zip
code just be 5 numbers (or text). I'm not sure how to do this If/then
statement. Here's what I have (which doesn't work).

=IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))

Thank you in advance for any help.
Karl

Maybe

=IF(AND(LEN(J2)=10,RIGHT(J2,4)="0000"),LEFT(J2,5), J2)



--


Regards,


Peo Sjoblom



"Karl H" <KarlH@discussions.microsoft.com> wrote in message
news:DA4CB5F3-A90D-4DC2-A1CD-6AE4DA8CD2A1@microsoft.com...
> If a zip code ends with 4 zeros, I'd like to eliminate them and have the
> zip
> code just be 5 numbers (or text). I'm not sure how to do this If/then
> statement. Here's what I have (which doesn't work).
>
> =IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))
>
> Thank you in advance for any help.
> Karl
>

Thank you, but it didn't work. That turned "32401" to 00003-2401 and
324010000 to 32401-0000.
I wonder if the "0000" is just being interpreted as any 4 numeric characters?

"Peo Sjoblom" wrote:

> Maybe
>
> =IF(AND(LEN(J2)=10,RIGHT(J2,4)="0000"),LEFT(J2,5), J2)
>
>
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
>
> "Karl H" <KarlH@discussions.microsoft.com> wrote in message
> news:DA4CB5F3-A90D-4DC2-A1CD-6AE4DA8CD2A1@microsoft.com...
> > If a zip code ends with 4 zeros, I'd like to eliminate them and have the
> > zip
> > code just be 5 numbers (or text). I'm not sure how to do this If/then
> > statement. Here's what I have (which doesn't work).
> >
> > =IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))
> >
> > Thank you in advance for any help.
> > Karl
> >
>
>
>

No it does not turn 32401 to 00003-2401, That is just the display not the
real values. You didn't say these were numerical values, all you have to do
if they are numerical values is to change the format to 00000




--


Regards,


Peo Sjoblom


"Karl H" <KarlH@discussions.microsoft.com> wrote in message
news:3B4008B2-80E1-4306-BB96-9B3F075B38E1@microsoft.com...
> Thank you, but it didn't work. That turned "32401" to 00003-2401 and
> 324010000 to 32401-0000.
> I wonder if the "0000" is just being interpreted as any 4 numeric
> characters?
>
> "Peo Sjoblom" wrote:
>
>> Maybe
>>
>> =IF(AND(LEN(J2)=10,RIGHT(J2,4)="0000"),LEFT(J2,5), J2)
>>
>>
>>
>> --
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>>
>> "Karl H" <KarlH@discussions.microsoft.com> wrote in message
>> news:DA4CB5F3-A90D-4DC2-A1CD-6AE4DA8CD2A1@microsoft.com...
>> > If a zip code ends with 4 zeros, I'd like to eliminate them and have
>> > the
>> > zip
>> > code just be 5 numbers (or text). I'm not sure how to do this If/then
>> > statement. Here's what I have (which doesn't work).
>> >
>> > =IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))
>> >
>> > Thank you in advance for any help.
>> > Karl
>> >
>>
>>
>>

"Karl H" <KarlH@discussions.microsoft.com> wrote in message
news:DA4CB5F3-A90D-4DC2-A1CD-6AE4DA8CD2A1@microsoft.com...
> If a zip code ends with 4 zeros, I'd like to eliminate them and have the
> zip
> code just be 5 numbers (or text). I'm not sure how to do this If/then
> statement. Here's what I have (which doesn't work).
>
> =IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))
>
> Thank you in advance for any help.
> Karl
>
How about just using the Find and Replace function (CTRL+F) ??

Assuming your zipcodes are expressed normally (i.e. 12345-1234 and
12345-0000):

Find what: enter -0000
Replace with: leave blank
Click "Replace All"
--
alan

Thanks, Peo...that should do it.
Karl

"Peo Sjoblom" wrote:

> No it does not turn 32401 to 00003-2401, That is just the display not the
> real values. You didn't say these were numerical values, all you have to do
> if they are numerical values is to change the format to 00000
>
>
>
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
> "Karl H" <KarlH@discussions.microsoft.com> wrote in message
> news:3B4008B2-80E1-4306-BB96-9B3F075B38E1@microsoft.com...
> > Thank you, but it didn't work. That turned "32401" to 00003-2401 and
> > 324010000 to 32401-0000.
> > I wonder if the "0000" is just being interpreted as any 4 numeric
> > characters?
> >
> > "Peo Sjoblom" wrote:
> >
> >> Maybe
> >>
> >> =IF(AND(LEN(J2)=10,RIGHT(J2,4)="0000"),LEFT(J2,5), J2)
> >>
> >>
> >>
> >> --
> >>
> >>
> >> Regards,
> >>
> >>
> >> Peo Sjoblom
> >>
> >>
> >>
> >> "Karl H" <KarlH@discussions.microsoft.com> wrote in message
> >> news:DA4CB5F3-A90D-4DC2-A1CD-6AE4DA8CD2A1@microsoft.com...
> >> > If a zip code ends with 4 zeros, I'd like to eliminate them and have
> >> > the
> >> > zip
> >> > code just be 5 numbers (or text). I'm not sure how to do this If/then
> >> > statement. Here's what I have (which doesn't work).
> >> >
> >> > =IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))
> >> >
> >> > Thank you in advance for any help.
> >> > Karl
> >> >
> >>
> >>
> >>
>
>
>

I like it--easier than code-thank you!
Karl

"*alan*" wrote:

>
> "Karl H" <KarlH@discussions.microsoft.com> wrote in message
> news:DA4CB5F3-A90D-4DC2-A1CD-6AE4DA8CD2A1@microsoft.com...
> > If a zip code ends with 4 zeros, I'd like to eliminate them and have the
> > zip
> > code just be 5 numbers (or text). I'm not sure how to do this If/then
> > statement. Here's what I have (which doesn't work).
> >
> > =IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))
> >
> > Thank you in advance for any help.
> > Karl
> >
> How about just using the Find and Replace function (CTRL+F) ??
>
> Assuming your zipcodes are expressed normally (i.e. 12345-1234 and
> 12345-0000):
>
> Find what: enter -0000
> Replace with: leave blank
> Click "Replace All"
> --
> alan
>
>

On Tue, 27 Nov 2007 06:46:04 -0800, Karl H <KarlH@discussions.microsoft.com>
wrote:

>If a zip code ends with 4 zeros, I'd like to eliminate them and have the zip
>code just be 5 numbers (or text). I'm not sure how to do this If/then
>statement. Here's what I have (which doesn't work).
>
>=IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))
>
>Thank you in advance for any help.
>Karl

If your ZIP codes are entered as numbers, then you can either use the custom
format:

Format/Cells/Number/Custom Type: [<100000]00000;00000-0000

OR the formula:

=TEXT(A1,"[<100000]00000;00000-0000")


--ron