| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
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. . |
|
#3
|
|||
|
|||
|
=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. |
|
#4
|
|||
|
|||
|
=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. |
|
#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 "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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
| extract numbers, convert to date | Excel Discussion (Misc queries) | |||
| Convert to Numbers | Excel Worksheet Functions | |||
| Convert text to numbers | Excel Discussion (Misc queries) | |||
| How to convert Numbers to text | Excel Worksheet Functions | |||
Linear Mode
