Expanding sequence of data

C

Cong Nguyen

Please show me how to go from:
234-238 (in one cell) to: 234 235 236 237 238 (each in individual
cell).
My worksheet has so many of these numbers and can't use "FILL".
Thank you so much for your help.
Sincerely,
 
Z

Zone

Cong, assuming they're all integers with no spaces, this should work. Copy
this code and paste into a standard module. Select as many cells as needed
(selection must be continuous and should all be in the same column). Hope
this helps! James

Sub Expand()
Dim L As String, R As String, P As Integer
Dim cell As Range, k As Integer
For Each cell In Selection
P = InStr(cell, "-")
If P > 1 Then
L = Left(cell, P - 1)
R = Right(cell, Len(cell) - P)
For k = 0 To CInt(R) - CInt(L)
cell.Offset(0, k + 1) = L + k
Next k
End If
Next cell
End Sub
 
E

Earl Kiosterud

Multiposted.

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
C

Cong Nguyen

Dear "Zone",
Your solution works perfectly for data from 4 digit or less. It would not
work in cases similar to:314510-314550
Please advise.
Thank you.
--
Cong Nguyen
[email protected]


Zone said:
Cong, assuming they're all integers with no spaces, this should work. Copy
this code and paste into a standard module. Select as many cells as needed
(selection must be continuous and should all be in the same column). Hope
this helps! James

Sub Expand()
Dim L As String, R As String, P As Integer
Dim cell As Range, k As Integer
For Each cell In Selection
P = InStr(cell, "-")
If P > 1 Then
L = Left(cell, P - 1)
R = Right(cell, Len(cell) - P)
For k = 0 To CInt(R) - CInt(L)
cell.Offset(0, k + 1) = L + k
Next k
End If
Next cell
End Sub
Click to expand...
 
Z

Zone

Cong, it won't work on those numbers because they're type long instead of
type integer. Change the For k=0 line like this:

For k = 0 To CLng(R) - CLng(L)

James

Cong Nguyen said:
Dear "Zone",
Your solution works perfectly for data from 4 digit or less. It would not
work in cases similar to:314510-314550
Please advise.
Thank you.
Click to expand...
 
Top