How to convert a series of numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying a | delimiter to separate a series of Zip codes 7
Series fill only copying and fill series greyed out 1
Excel Conditional Formatting 1
Fill/Extend Series adding decimal points to some numbers 1
Convert WAV to MP3 4
How to convert cell contents 1
Convert Numbers to Text 2
Convert Week number to Month 0

Back
Top