Link to home
Start Free TrialLog in
Avatar of iceman19330
iceman19330

asked on

Number Range expand question

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.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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
Avatar of iceman19330
iceman19330

ASKER

Here is the actual file.  
UPS-ZONE-CHART.xls
Try formula below:
=TEXT(A7,"000")
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,

does that even work for ranges like 010-061 etc.
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,
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
@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.
ASKER CERTIFIED SOLUTION
Avatar of wchh
wchh

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Gonna need VBA for that wchh has it for you.

NG,