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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,214,773
Messages
6,121,479
Members
449,034
Latest member
Raygers

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
Back
Top