Number Range expand question

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

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

Select allOpen 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.

Gonna need VBA for that wchh has it for you.

NG,