Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
jermsalerms
 
Posts: n/a
Default Adding a 0 to a four digit number


I have 4000+ zipcodes in a .csv file that I have imported to excel.

The .csv file shows all zipcodes that start with 0 as a for digit
number instead of 5. I need to add the 0 infront of the four that show
up.

for example

19601 fills in correctly
08054 shows as 8054

I need an if then statement that basically says if 4 digits then add a
0 infront is 5 digits remain the same.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500920

  #2   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default Adding a 0 to a four digit number

As these are codes then they should be stored as text, so the following
will do what you want:

=IF(LEN(A1)=5,""&A1,IF(LEN(A1)=4,"0"&A1,"00"&A1))

This caters for 3-, 4- and 5-digit numbers, but hopefully you can see
the logic of how to cope if you also have 2 digit numbers. It does not
check for 6-digit or larger numbers.

Hope this helps.

Pete

  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default Adding a 0 to a four digit number

On Thu, 12 Jan 2006 18:46:04 -0600, jermsalerms
wrote:


I have 4000+ zipcodes in a .csv file that I have imported to excel.

The .csv file shows all zipcodes that start with 0 as a for digit
number instead of 5. I need to add the 0 infront of the four that show
up.

for example

19601 fills in correctly
08054 shows as 8054

I need an if then statement that basically says if 4 digits then add a
0 infront is 5 digits remain the same.


When you say the .csv file shows the numbers as four digits, are you looking at
the .csv file with a text editor, or merely opening it in Excel?

If, when you open it in a text editor (e.g. Notepad), the zipcodes display
properly, then you can change the file type to a .txt file. When you open it
in Excel you will go to the text editor and can specify the column type as
"Text".

If that is not possible, what to do depends on your goals.

If you are just interested in having the column appear with the leading zero,
Select the column
Format/Cells/Number/Custom Type: 00000

If you need to change the zip codes to text strings, then, assuming the
zipcodes are in column A, in some unused column enter the formula:

=TEXT(A1,"00000")

Copy/drag down as far as needed.
Edit/Copy
Select A1 (or whatever the initial cell is).

Edit/Paste Special/Values


--ron
  #4   Report Post  
Posted to microsoft.public.excel.misc
mphell0
 
Posts: n/a
Default Adding a 0 to a four digit number


Format CellsSpecialZip Code


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=500920

  #5   Report Post  
Posted to microsoft.public.excel.misc
jermsalerms
 
Posts: n/a
Default Adding a 0 to a four digit number


I ended up using petes suggestions with and extra if statement that said
that if the cell is less than 2 numbers the result is "". This prevented
me from having 0's show up when there was no zipcode provided.

I was trying to write the formula along these lines and could remember
that is was the & symbol that I was needing to make it all come
together.

Thanks


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=500920



  #6   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default Adding a 0 to a four digit number

Thanks for the feedback.

Pete

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding number only if CDN is displayed in next cell ED New Users to Excel 2 December 21st 05 11:39 PM
How do I copy the last digit of a number from a cell Carlos Excel Discussion (Misc queries) 4 September 29th 05 07:17 PM
Entering a 16 digit number Tbird Excel Discussion (Misc queries) 1 August 11th 05 03:11 AM
How do i format a 5 to 6 digit number into the correct date? date cell configuration Excel Worksheet Functions 4 June 10th 05 08:07 PM
16 digit number wont keep alteration unless format cell to text Croc001 Excel Discussion (Misc queries) 3 March 30th 05 09:12 AM


All times are GMT +1. The time now is 02:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"