[Brenc]

I have a spreed sheet that contains a 5 digit zip that I need to convert to a 9 digit. How would I go about doingin this?


I have 1800 enteries

[colbtech]

With leading zeroes? e.g. 51251 = 000051251

If so select the cells that hold the zip codes and select Format, Cells, Number, Custom. Enter 9 zeroes, hit enter

[ScottGem]

How would you do this? The Zip+4 coding is specific to an address. If you want to input the plus 4 for an individual just type in -xxxx at the end of the entry.

[Brenc]

No leadinf zeros... see example below

Before

27954
28510
28510
28510

After

27506-0000
27822-0000
28434-0000
28473-0000

[ScottGem]

Ok, so you just want to add the 4 zeros? In a blank Column put the expression:

=E2 & "-0000"

where E is the column with the zipcode and to the first row of records. Then copy that formula down the column. Finally, copy the Column with the formulas and use Edit-Paste Special>Values to copy it back to the original column.

[Brenc]

I have a spreed sheet that contains a 5 digit zip that i need to convert to a 9 digit. How would I go about doingin this?


I have 1800 enteries

I have 1800 entires to correct...

[ScottGem]

And your point? All you need do is copy and paste one formula down the entire blank column, then copy and paste that column into the original calum as I said. I do this all the time when I have to adjust data in a column.

[Brenc]

I know close to nothing about excel. The formula to take the first five digits then add -0000 to the end of it is what I am looking for... Sorry

[ScottGem]

No need to apologize. The ability to copy formulas like I suggested is one of the most basic uses of a spreadsheet, so I assumed you would be familiar with it.

[Brenc]

I have added a col. In this new col I would like to take the zip and add the -0000
Col A Col B
27506 27506-0000

[ScottGem]

I told you what to do.

[JBeaucaire]

I would suggest 3 columns.

A = Zip
B = +4 digits
C = The whole shebang

Notice the highlighted formula in the picture below...

Now, highlight cell C3, press CTRL-C to copy it, then highlight all C2-C1800 and press ENTER. This copies your formula from C1 down the rest of the rows and will duplicate the process.

The benefit of this approach is it allows you to easily add the +4 digits as you get them.

[mdosh01]

That is what ScottGem has given you. If column A has your zip codes (assuming you are starting in row 2 of the spreadsheet), then enter his formula in cell B2: =B2 & "-0000" and press enter. You should see the zip code from B1 with "-0000" added on the end.

Now, with cell B2 active (i.e. use the arrow key to highlight B2), hold down the shift key and at the same time press the page down key repeatedly until you get to the same row as the last entry in column A. Don't worry if you go past the last row, you can delete that later.

Once there, release the shift key and press CTRL-D to copy the formula down, or click Edit, Fill, Down. All your zip codes should now be in column B with "-0000" added to the end.

[colbtech]

Select the cells that hold the zip codes and select Format, Cells, Number, Custom. Enter 00000-0000, hit enter