Expand text number range into separate cells

[kpaull]

Running Excel 2003 SP3.

I have data in excel which looks like this:

Cell A1 1200-1209 Cell B1 1423-1425 Cell C1 1705-1799

I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each row/cell of data I have like this.

Thanks,
Kurtis

 

[VoG]

Try

Sub splt()
Dim LR As Long, i As Long, X As Variant, Y As Variant
LR = Range("A" & Rows.count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        X = Join(Application.Transpose(Application.Transpose(.Resize(, 3))), "-")
        Y = Split(X, "-")
        With .Resize(, 6)
            .Value = Y
            .Value = .Value
        End With
    End With
Next i
End Sub

 

[kpaull]

Okay, that got me to the first stage of what I needed:

From:

A1 B1 C1
1200-1209 1423-1425 1705-1799

To:

A1 B1 C1 D1 E1 F1
1200 1209 1423 1425 1705 1799

Now I need it to fill in the numbers from the range.

e.g. in the case of 1200-1209 I want to see 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 in separate cells from A1 - A10.

Thanks,
Kurtis