Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expanding sequence of data
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expanding sequence of data
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 "Cong Nguyen" wrote in message ... 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 Nguyen |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expanding sequence of data
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... ----------------------------------------------------------------------- "Cong Nguyen" wrote in message ... 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 Nguyen |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Expanding sequence of data
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" wrote in message ... 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 "Zone" wrote: 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 "Cong Nguyen" wrote in message ... 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 Nguyen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expanding amount of data in a formula | Excel Discussion (Misc queries) | |||
sorting data and expanding the selection | Excel Discussion (Misc queries) | |||
Expanding data | Excel Worksheet Functions | |||
Expanding Data validation from List | Excel Discussion (Misc queries) | |||
Linked query not expanding with new data | Excel Worksheet Functions |