Need to convert list of 5 digit zip codes to ranges where possible

[Mel07]

I have been unable to figure this one out. I have over 2000 zip codes
in a single column (One 5 digit zip code per row) which are already
sorted in numeric order. I need to put these individucal zip codes
into ranges where possible when zip codes are sequential (i.e.
91714-91734). Is this possible outside of doing this manually because
it's killing me!!! Help.

 

[bpeltzer]

A couple questions. Are the zip codes numeric or text? And what do you want
if there's a loner?
If the codes are numeric, and loners can be shown as 'groups of one' (ex
22033-22033), then here's one approach. If the zip codes are in column A and
sorted in ascending order, beginning in row 2, use column B to designate what
group each zip belongs to. In B2 enter the number 1. In B3 enter the
formula =IF(A3=A2+1,B2,B2+1). Then in column C we'll create the ranges. In
C2 enter the formula =IF(B2=B3,"",INDEX(A:A,MATCH(B2,B:B,FALSE)) & " - " &
A2). Copy that down to C3. Then select B3:C3 and autofill down.
HTH. --Bruce

 

[Duke Carey]

As alternative to Bruce's suggestion, here's a macro that might do the trick

Sub zips()
Dim rng As Range
Dim strFirst As String
Dim intCurr As Long
Dim strlast As String
Dim rngTgt As Range
Dim x As Integer
Dim lTest As Long

Range("B1:B100").ClearContents

Set rngTgt = Range("B1")
strFirst = ""
strlast = ""
intCurr = 0
For Each rng In Selection
lTest = CLng(rng)
If (lTest > intCurr + 1) Then

' no sequence, so write what you have
If Len(strlast) = 0 Then
rngTgt.Offset(x, 0) = strFirst
x = x + 1
Else
rngTgt.Offset(x, 0) = strFirst & " - " & strlast
x = x + 1
End If
strFirst = rng
intCurr = CLng(strFirst)
strlast = ""
Else
' it's still a sequence so increase the upper end and
' the comparison value
strlast = rng
intCurr = CLng(rng)
End If
Next

End Sub

For information on installing the code see
Getting Started with Macros and User Defined Functions

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Mel07]

Hi Bruce,
Thanks for the reply - never done one of these forums actually I'm
hoping your input will help me get throught this work faster so
thanks!
Anyway, the zip codes are numeric. If there's an unique, loner zip
code then I would like to have it simply input it again in the column
next to it, column B. I'm going to give your formula a shot. I
appreciate the help greatly! I'll let you know if it works.