replace - with missing numbers (e.g 1-4 = 1, 2,3,4)
On Monday, May 7, 2018 at 4:03:24 PM UTC-4, jlclyde wrote:
Hello, I am hoping that you can help me with a formula to replace a dash with the missing numbers. If I have 1-4 I would like to return 1,2,3,4.
Is this possible?
Here is an old macro that I wrote years ago. It will replace any kind of range with listed values; for example, 1-4,5,6,7-10 will become 1,2,3,4,5,6,7,8,9,10
Public Sub RangeToNum()
' Example
' Input: 12,14,18,20-25,27-30,33
' Output: 12,14,18,20,21,22,23,24,25,27,28,29,30,33
Dim va As Variant
Dim rng As Excel.Range
Dim s As String
Dim i As Long, j As Long
Dim iPos As Long
Dim iChar As Long
Dim sStart As String
Dim iEnd As Long
Dim iLen As Long
Dim sChar As String
Dim iStart As Long
For Each rng In Selection.Cells
s = vbNullString
' split by comma
va = Split(rng.Value, ",")
' loop through each value
For i = LBound(va) To UBound(va)
' attempt to split by -
iPos = InStr(1, va(i), "-")
' if successful...
If iPos 0 Then
' capture left and right side of -
sStart = CStr(Left(va(i), iPos - 1))
iEnd = CLng(Right(va(i), Len(va(i)) - iPos))
For j = 1 To Len(sStart)
sChar = CLng(Mid$(sStart, j, 1))
If IsNumeric(sChar) Then
iStart = CLng(Mid$(va(i), j, iPos - j))
Exit For
End If
Next j
For j = iStart To iEnd
s = s & CStr(j) & ","
Next j
Else
s = s & va(i) & ","
End If
Next i
s = Left$(s, Len(s) - 1)
rng.Offset(0, 1).Value = "'" & s
Next rng
End Sub
|