How to convert a series of numbers
 

I have received an EXCEL 2003 worksheet with a column that contains many zip
codes, improperly formatted. The cells contain, for instance:
10751746, but I want 01075
10271234, but I want 01027
10359999, but I want 01035

How can I convert the numbers above to give me the numbers I want as
indicated above? I will appreciate advice.

Convert the data to text
then in in B1 put ="0"&A1
and in C1 put =LEFT(B1,5)

-----Original Message-----
I have received an EXCEL 2003 worksheet with a column
that contains many zip
codes, improperly formatted. The cells contain, for
instance:
10751746, but I want 01075
10271234, but I want 01027
10359999, but I want 01035

How can I convert the numbers above to give me the
numbers I want as
indicated above? I will appreciate advice.
.

=0&LEFT(A1,4)

"Robert Judge" wrote in message
...
I have received an EXCEL 2003 worksheet with a column that contains many
zip
codes, improperly formatted. The cells contain, for instance:
10751746, but I want 01075
10271234, but I want 01027
10359999, but I want 01035

How can I convert the numbers above to give me the numbers I want as
indicated above? I will appreciate advice.

=IF(LEN(A1)=8,0&LEFT(A1,4),LEFT(A1,5))

-- Carlos

"Robert Judge" wrote in message
...
I have received an EXCEL 2003 worksheet with a column that contains many
zip
codes, improperly formatted. The cells contain, for instance:
10751746, but I want 01075
10271234, but I want 01027
10359999, but I want 01035

How can I convert the numbers above to give me the numbers I want as
indicated above? I will appreciate advice.

If not all are 9 digit zips you could use this:

=IF((LEN(A1)=8)+(LEN(A1)=4)0,0&LEFT(A1,4),LEFT(A1 ,5))

-- Carlos

"CarlosAntenna" wrote in message
...
=IF(LEN(A1)=8,0&LEFT(A1,4),LEFT(A1,5))

-- Carlos

"Robert Judge" wrote in message
...
I have received an EXCEL 2003 worksheet with a column that contains many
zip
codes, improperly formatted. The cells contain, for instance:
10751746, but I want 01075
10271234, but I want 01027
10359999, but I want 01035

How can I convert the numbers above to give me the numbers I want as
indicated above? I will appreciate advice.