Fill Series in a formula

[hpa]

Hello Excel Smarties,


I have a list that contains some numbers and some ranges of numbers as text. I'm hoping to end up with a list of all numbers expressed in the list. Here is a sample, it's easier to show than explain:


92002-92014

99201-99205

99211-99215

99217-99220

99241

99245

99271-99275

99301-99303

99311-99313

99321-99323

99331-99333

99341-99355

99384-99387

99394-99397

99401-99404

99411

99412

99420

99429


Can anyone think of a way to do it?


Thanks,

Sasha

 

[DonMinter]

Are you wanting to generate a new list that would be:


92002

92003

92004

(etc)

92014

99201

99202

(etc)

99205

99211


and so on? I am asking if your desire is to parse the list into all of the numbers in each series, along with the individual numbers.


don

 

[Luke M]

This macro will let you select the range, and then outputs the list to col B. Note that depending on the size of the ranges and/or amount of numbers, this could take awhile.

[pre]

Sub CreateList()
Dim CValue As Variant
Dim xFirst As Long
Dim xLast As Long

Application.ScreenUpdating = False
i = 1

For Each c In Selection
CValue = c.Value
If WorksheetFunction.IsNumber(CValue) Then
Cells(i, "B") = CValue
i = i + 1
Else
xFirst = Mid(CValue, 1, WorksheetFunction.Find("-", CValue) - 1)
xLast = Mid(CValue, WorksheetFunction.Find("-", CValue) + 1, 999)
For x = xFirst To xLast
'Change this letter if you want a different column for output
Cells(i, "B") = x
i = i + 1
Next x
End If
Next c

Application.ScreenUpdating = True

End Sub

[/pre]

 

[hpa]

DonMinter, that's exactly right.


Luke M, you are indeed an Excel Ninja. Worked perfectly, thanks.

 

[Luke M]

You're welcome, glad I could help. =)