Expanding sequence of data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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
 
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...
 
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
(e-mail address removed)


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...
 
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...
 
Back
Top