Change Zip Code Format i.e. Find All Cells With Charachters "abcde" to "abcde-abcde"

[toddroys]

Hi, I'm a newbie this Forum and am newish to Excel. I have a large spreadsheet with multiple columns and rows. A list of zip codes in one column.

The sheet has a macro that kicks out a text file that is accessed by javascript. If a zip-code has a range in a cell like 98950-98953 or 99356-99356 (same zip represented as a range) the javascript can handle. If a single zip code does not have a range like 70601 the javascript does not handle. I would like to find all non-range zip cells like 70601 and replace them with a range like 70601-70601.

I've done a lot of search on ways to handle with IF statements but have not found anything. I've also been learning to use Macros with VB but usually need to find a specific example.

Thanks in advance.

 

[jardenp]

There's probably a more elegant way, but I would insert a column to the right of the zip column, and fill it with the formula =if(len(AdjacentZipCell)=5,AdjacentZipCell&"-"&AdjacentZipCell,AdjacentZipCell)


So, if the zips are in column R, then in S2 you would have =if(len(R2)=5,R2&"-"&R2,R2) and extend that all the way down and then copy column S, paste it back as values, and delete column R.

 

[Rick Rothstein]

You can do it with this macro...

Sub ConvertAllZipsToZipDashZips()
  Dim Addr As String
  Addr = "K2:K" & Cells(Rows.Count, "K").End(xlUp).Row
  Range(Addr) = Evaluate("IF(ISNUMBER(FIND(""-""," & Addr & "))," & _
                         Addr & "," & Addr & "&""-""&" & Addr & ")")
End Sub

Change the three K's that I highlighted in red to the column letter designation for your Zip Code column and change the 2 that I highlighted in blue to the row number containing your first Zip Code.

 

[toddroys]

Awesome guys, thanks! Both methods worked like a charm (I went with the macro in the end) and such quick responses were appreciated. I was really spinning my wheels before coming here and you saved me a bunch of time and frustration. <o></o>