seperate a full 9-digit zip and add "-", possible?

[cvgreports]

here is my scenerio, I have a list of over 2,000 addresses. And at the end is the 9-digit zip code...(i.e 123456789) and what I want to do is seperate the last four digits and place a dash "-" after the first 5 digits. Is this possible?


Any help I would appreciate, thanks in advance!

 

[Oaktree]

Assuming the 9 digit zip code is in its own cell, just format the cell with number format --> special --> zip code + 4.

 

[cvgreports]

thanks for the reply, all 9 digits without the dash of course are in its own cell and I tried what you just told me and nothing happened. The number stayed the same and it didn't break up the last four digits. Am I missing something else?

 

[Oaktree]

The zip codes may be stored as text.

Try formatting the zip codes as zip + 4 above, putting a 0 in any unused cell, copying the cell with the 0, selecting your cell/s with the zip codes, and using edit --> paste special --> add.

 

[cvgreports]

you were right it was store as text...awesome man, thanks a whole bunch it worked. I love it when I learn something new all the time...thanks again! Have a nice day!!!

you saved me tons of dashes...haha

 

[cvgreports]

i forgot one last question sorry, what if I wanted to remove the last 4 numbers and just use the 5 first numbers, what would I need to do in that case?

 

[juanale81]

I'm assuming you have the entire address on the same cell, and the nine zip code digits are always the last nine characters on the string. For each cell use the RIGHT function like this: +right(cellwithtext; 9) This will extract the last nine characters as text. Then you have to turn this text to value with the VALUE function like this +value(right(cellwithtext;9)) Now you can apply the formatting mentioned on the previous reply (special - zip code +4).

 

[Oaktree]

i forgot one last question sorry, what if I wanted to remove the last 4 numbers and just use the 5 first numbers, what would I need to do in that case?

Along the same line as Juan's post above, you'd use one of the text functions. For example, =LEFT(A1,5) where A1 had your 9 digit zip code would return the first 5 digits of the zip code.

 

[cvgreports]

ok got it, that woked...I can't believe this was really simple. I hope you guys are okay with another ?...

I have 2 columns, one with the first part of the address and the other column with the second part of the address such as APT # etc...I know how to combine both together with the (=cell&cell), but when I do that I need it to place a space in between the end of the first cell data and the second cell data. ie.... 123 Roger St. Apt #5 instead of 123 Roger St.Apt #5

once again thank u so much for the help...

 

[bsnapool]

You mean something like this???

Book1
	A	B	C	D	E	F	G
3		33333333333	33333		3333333333333333
4		44444444444	44444		4444444444444444
5		55555555555	55555		5555555555555555
6		66666666666	66666		6666666666666666
7		77777777777	77777		7777777777777777
8		88888888888	88888		8888888888888888
9		99999999999	99999		9999999999999999
10
11
Sheet1

EDIT: =B3&" "&C3 > Cell&" "&Cell