Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 7th 18, 09:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 409
Default 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   Report Post  
Old May 7th 18, 11:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,588
Default 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   Report Post  
Old May 9th 18, 01:52 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 227
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I replace my missing Formula bar in Excel? Amit Excel Discussion (Misc queries) 2 January 4th 06 06:32 PM
to find missing serial numbers in randomly generated numbers B.H. Hadi Excel Worksheet Functions 2 December 1st 05 10:56 PM
Missing row numbers, HELP !!!! Tony Excel Discussion (Misc queries) 2 August 17th 05 02:27 AM
row numbers missing etc Mike Excel Programming 4 June 10th 04 10:38 PM
Missing Numbers Soniya[_2_] Excel Programming 5 December 18th 03 02:25 PM


All times are GMT +1. The time now is 05:24 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017