Remember Me?

#1
May 7th 18, 09:03 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2007 Posts: 409
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
May 7th 18, 11:11 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,632
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
May 9th 18, 01:52 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 228
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Amit Excel Discussion (Misc queries) 2 January 4th 06 07:32 PM B.H. Hadi Excel Worksheet Functions 2 December 1st 05 11:56 PM Tony Excel Discussion (Misc queries) 2 August 17th 05 02:27 AM Mike Excel Programming 4 June 10th 04 10:38 PM Soniya[_2_] Excel Programming 5 December 18th 03 03:25 PM

All times are GMT +1. The time now is 08:38 PM.