replace - with missing numbers (e.g 1-4 = 1, 2,3,4)
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? |
replace - with missing numbers (e.g 1-4 = 1, 2,3,4)
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 |
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 |
All times are GMT +1. The time now is 07:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com