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?

Hi,

Am Mon, 7 May 2018 13:03:21 -0700 (PDT) schrieb jlclyde:

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.

in Excel 365 you could use:

=TEXTJOIN(", ",1,1*ROW(INDIRECT(LEFT(A1,FIND("-",A1)-1)&":"&MID(A1,FIND("-",A1)+1,99))))
and insert this formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

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