Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a 0 to a four digit number
Thanks for the feedback.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding number only if CDN is displayed in next cell | New Users to Excel | |||
How do I copy the last digit of a number from a cell | Excel Discussion (Misc queries) | |||
Entering a 16 digit number | Excel Discussion (Misc queries) | |||
How do i format a 5 to 6 digit number into the correct date? | Excel Worksheet Functions | |||
16 digit number wont keep alteration unless format cell to text | Excel Discussion (Misc queries) |