Number Range expand question

iceman19330
iceman19330 used Ask the Experts™
on
I have a spreadsheet that was given to me and it include zip code ranges with only the first 3 digits.  so in a column A1 004-005 and in column B1 it will have 2 or 3 or something like that.  Column A2 it might be 006-063 and B2 might be 3, while column A3 might be 008.  So what I am looking for is a way to break out the ranges into single rows so it would take A1 004-005 and then it would be A1 004 and A2 005 keeping the value of B1.  Is there a way to do that?  Right now my only option is to do it by hand and that could take a few hours to do.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
iceman19330,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick

Author

Commented:
Here is the actual file.  
UPS-ZONE-CHART.xls

Commented:
Try formula below:
=TEXT(A7,"000")
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
That only moved A7 to another cell it did nothing to separate the two.
Data/text to columns... should work. The delimeter will be the "-".

NG,

Author

Commented:
does that even work for ranges like 010-061 etc.

Commented:
Try Macro Below
Sub AutoMove()
Dim Cnt As Long

Sht = "Sheet1"
Sheets(Sht).Select
Cells.Select
Selection.ClearContents
Cnt = 0

Set Rng = Worksheets("UPS 07 ZONE CHART").Range("A7:A46")
         
For Each cel In Rng
    If InStr(cel.Value, "-") > 0 Then
       For i = Left(cel.Value, 3) To Right(cel.Value, 3)
           Cnt = Cnt + 1
           Worksheets(Sht).Cells(Cnt, 1).NumberFormat = "@"
           Worksheets(Sht).Cells(Cnt, 1).Value = Format(i, "000")
           Worksheets(Sht).Cells(Cnt, 2).Value = cel.Offset(0, 1).Value
       Next i
    Else
       Cnt = Cnt + 1
       Worksheets(Sht).Cells(Cnt, 1).NumberFormat = "@"
       Worksheets(Sht).Cells(Cnt, 1).Value = Format(cel.Value, "000")
       Worksheets(Sht).Cells(Cnt, 2).Value = cel.Offset(0, 1).Value
    End If
Next cel

End Sub

Open in new window

I don't see 010-061 in your data?

NG,
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

why do you need the data in the format you describe? If you want to use it for a lookup formula source, it would be sufficient to use a table like

Dest. ZIP      Ground
      
004                 3
006                  45
008               [1]
009                  45
010                    2
044                    3
045                    2
046                    3
048                    2

If you need to look up the Ground number for the Destination Zip 015, for example, a Vlookup with the TRUE parameter would return 2

This way, you could keep the list short and it would only need a little re-arranging and cleanup (removing the dash and the number after the dash).

Would that be worth pursuing for you?

cheers, teylyn

Like the following?

NG,
UPS-ZONE-CHART.xls

Author

Commented:
@wchh - script says subscript out of range.

@NG - well kinda but more like for example.  
144 3
145 3
146 3

@teylyn- I am importing it to a sql database, it just didnt want to have to do each iteration, that could take several hours and likely lots of mistakes.
Commented:
Please refer to the attachement...
UPS-ZONE-CHART-1-.xls
Gonna need VBA for that wchh has it for you.

NG,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial