Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I replace my missing Formula bar in Excel? | Excel Discussion (Misc queries) | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions | |||
Missing row numbers, HELP !!!! | Excel Discussion (Misc queries) | |||
row numbers missing etc | Excel Programming | |||
Missing Numbers | Excel Programming |