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

toddroys

New Member
Joined
Mar 28, 2013
Messages
7
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jardenp

Active Member
Joined
May 12, 2009
Messages
369
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,054
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
You can do it with this macro...
Rich (BB code):
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

New Member
Joined
Mar 28, 2013
Messages
7
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:p></o:p>
 

Watch MrExcel Video

Forum statistics

Threads
1,133,539
Messages
5,659,392
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top