Zip Code Range Extraction

23hawk

New Member
Joined
Sep 3, 2009
Messages
8
I have been trying to figure out how to extract zipcode range from a single cell. My data contains a single column with multiple formats. One cell could have a single zipcode like 30040. But, another could have a range like 30040-99. Sample data below. Would like to have just individual zipcodes in a single column.</SPAN>
30002-05</SPAN>
30009 - 10</SPAN>
30021</SPAN>
30022-23</SPAN>
30029-32</SPAN>

<TBODY>
</TBODY>

The output I am trying to get is below

30002
30003
30004
30005
30009
30010
30021
30022
30023
30029
30030
30031
30032 </SPAN>
Any help on this is greatly appreciated - We Love MREXCEL !!​
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

23hawk

New Member
Joined
Sep 3, 2009
Messages
8
Not quite what I am needing. I need the list to extract the numbers in the ranges then also list the number that are individually listed.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming data in Column A, and output in Column B. Change as necessary.

Code:
Sub test()
Dim m, c As Range, s As String, x As Long
Range("B:B").ClearContents
With CreateObject("Scripting.Dictionary")
    For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        s = Left(c, 5)
        If Not .exists(s) Then .Add s, s
    Next
    For Each m In .items
        x = x + 1
        Range("B" & x) = m
    Next
End With
End Sub
 

23hawk

New Member
Joined
Sep 3, 2009
Messages
8

ADVERTISEMENT

That is close however I am looking for a way to list the numbers from each cell to a single cell - such as 30002-30005 is in 1 cell. I need it to create (extract)a list of cells in a column from that cell to show 30002
30003
30004
30005
And then list the one number in a cell if that is all there is - like 30010 then go to the next and extract again if it is a new range like 30012-14 .
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
Sub test()
Dim x As Long, y As Long, c As Range, t
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    t = Split(c, "-")
    If UBound(t) = 0 Then
        x = x + 1
        Cells(x, 2) = c
    Else
        For y = Val(t(0)) To Val(Left(t(0), 3) & t(1))
            x = x + 1
            Cells(x, 2) = y
        Next y
    End If
Next
End Sub
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I got a message from you saying it didn't stop. What range is your data actually in? Can you provide an actual sample of your data? Are there formulas in the column with your zip code ranges and formulas in cells that appear blank?

These were my results based on the samples you posted:

Excel Workbook
AB
130002-0530002
230009 - 1030003
33002130004
430022-2330005
530029-3230009
630010
730021
830022
930023
1030029
1130030
1230031
1330032
Sheet1
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
Sub test()
Dim x As Long, y As Long, c As Range, t
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    t = Split(c, "-")
    If UBound(t) = 0 Then
        x = x + 1
        Cells(x, 2) = c
    Else
        For y = Val(t(0)) To Val(t(1))
            x = x + 1
            Cells(x, 2) = y
        Next y
    End If
Next
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,498
Messages
5,832,060
Members
430,109
Latest member
tinezi

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