Cell contents separated by dash"-"

[sainathd]

Hi,
I have cell which contains C45-C90, I want it to split into multiple rows and fill in the form C45, C46,C47...C90

For example:

colA	ColB
C45-C50	0.6
C99-C101	0.5

<tbody>
</tbody>

Expected Result

Col A	Col B	Col C
C45	C45-C50	0.6
C46		0.6
C47		0.6
C48		0.6
C49		0.6
C50		0.6
C99	C99-C101	0.5

<tbody>
</tbody>

C100		0.5
C101		0.5

<tbody>
</tbody>

Hope it's clear, please help me if there is any way I would be able to accomplish this.

 

[Marcelo Branco]

I'll use an excellent function provided by Rick Rothstein - see
Generalized Series Expansions (e.g. AB5-AB9 becomes AB5, AB6, AB7, AB8, AB9)

Before Macro

	A​	B​	C​	D​	E​	F​
1​	C45-C50​	0.6​
2​	C99-C101​	0.5​
3​

<tbody>
</tbody>

My code
Function by Rick Rothstein

Sub aTest()
    Dim rCell As Range, spl As Variant, lin As Long
    
    lin = 1
    For Each rCell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        Cells(lin, "E") = rCell
        spl = Split(ExpandedSeries(rCell.Value), ", ")
        Range("D" & lin).Resize(UBound(spl) + 1) = Application.Transpose(spl)
        Range("F" & lin).Resize(UBound(spl) + 1) = rCell.Offset(, 1)
        lin = lin + UBound(spl) + 1
    Next rCell
End Sub

Function ExpandedSeries(ByVal S As String, Optional Delimiter As String = ", ") As Variant
  Dim X As Long, Y As Long, Z As Long
  Dim Letter As String, Numbers() As String, Parts() As String
  S = Chr$(1) & Replace(Replace(Replace(Replace(Application.Trim(Replace(S, ",", _
      " ")), " -", "-"), "- ", "-"), " ", " " & Chr$(1)), "-", "-" & Chr$(1))
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*-*" Then
      For Z = 1 To InStr(Parts(X), "-") - 1
        If IsNumeric(Mid(Parts(X), Z, 1)) And Mid$(Parts(X), Z, 1) <> "0" Then
          Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z" & Chr$(1) & "]"))
          Exit For
        End If
      Next
      Numbers = Split(Replace(Parts(X), Letter, ""), "-")
      If Not Numbers(1) Like "*[!0-9" & Chr$(1) & "]*" Then Numbers(1) = Val(Replace(Numbers(1), Chr$(1), "0"))
      On Error GoTo SomethingIsNotRight
      For Z = Numbers(0) To Numbers(1) Step Sgn(-(CLng(Numbers(1)) > CLng(Numbers(0))) - 0.5)
        ExpandedSeries = ExpandedSeries & Delimiter & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & Delimiter & Parts(X)
    End If
  Next
  ExpandedSeries = Replace(Mid(ExpandedSeries, Len(Delimiter) + 1), Chr$(1), "")
  Exit Function
SomethingIsNotRight:
  ExpandedSeries = CVErr(xlErrValue)
End Function

After macro - results in columns D:F

	A​	B​	C​	D​	E​	F​
1​	C45-C50​	0.6​		C45​	C45-C50​	0.6​
2​	C99-C101​	0.5​		C46​		0.6​
3​				C47​		0.6​
4​				C48​		0.6​
5​				C49​		0.6​
6​				C50​		0.6​
7​				C99​	C99-C101​	0.5​
8​				C100​		0.5​
9​				C101​		0.5​
10​

<tbody>
</tbody>

Hope this helps

M.

 

[Peter_SSs]

Another option to try on a copy of your workbook.
This also puts results in columns D:F.

Sub MakeSeries()
  Dim bits As Variant, data As Variant, result As Variant
  Dim firstnum As Long, lastnum As Long, i As Long, j As Long, k As Long
  Dim prefix As String
  
  data = Range("A1").CurrentRegion.Value
  ReDim result(1 To Rows.Count, 1 To 3)
  k = 1
  For i = 1 To UBound(data)
    bits = Split(data(i, 1), "-")
    firstnum = StrReverse(Mid(Val(StrReverse(bits(0) & 9)), 2))
    lastnum = StrReverse(Mid(Val(StrReverse(bits(1) & 9)), 2))
    prefix = Replace(bits(0), firstnum, "")
    result(k, 2) = data(i, 1)
    For j = firstnum To lastnum
      result(k, 1) = prefix & j: result(k, 3) = data(i, 2)
      k = k + 1
    Next j
  Next i
  Range("D1:F1").Resize(k - 1).Value = result
End Sub

 

[sainathd]

I'll use an excellent function provided by Rick Rothstein - see
Generalized Series Expansions (e.g. AB5-AB9 becomes AB5, AB6, AB7, AB8, AB9)

Before Macro

	A​	B​	C​	D​	E​	F​
1​	C45-C50​	0.6​
2​	C99-C101​	0.5​
3​

<tbody>
</tbody>

My code
Function by Rick Rothstein

Sub aTest()
    Dim rCell As Range, spl As Variant, lin As Long
    
    lin = 1
    For Each rCell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        Cells(lin, "E") = rCell
        spl = Split(ExpandedSeries(rCell.Value), ", ")
        Range("D" & lin).Resize(UBound(spl) + 1) = Application.Transpose(spl)
        Range("F" & lin).Resize(UBound(spl) + 1) = rCell.Offset(, 1)
        lin = lin + UBound(spl) + 1
    Next rCell
End Sub

Function ExpandedSeries(ByVal S As String, Optional Delimiter As String = ", ") As Variant
  Dim X As Long, Y As Long, Z As Long
  Dim Letter As String, Numbers() As String, Parts() As String
  S = Chr$(1) & Replace(Replace(Replace(Replace(Application.Trim(Replace(S, ",", _
      " ")), " -", "-"), "- ", "-"), " ", " " & Chr$(1)), "-", "-" & Chr$(1))
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*-*" Then
      For Z = 1 To InStr(Parts(X), "-") - 1
        If IsNumeric(Mid(Parts(X), Z, 1)) And Mid$(Parts(X), Z, 1) <> "0" Then
          Letter = Left(Parts(X), Z + (Left(Parts(X), 1) Like "[A-Za-z" & Chr$(1) & "]"))
          Exit For
        End If
      Next
      Numbers = Split(Replace(Parts(X), Letter, ""), "-")
      If Not Numbers(1) Like "*[!0-9" & Chr$(1) & "]*" Then Numbers(1) = Val(Replace(Numbers(1), Chr$(1), "0"))
      On Error GoTo SomethingIsNotRight
      For Z = Numbers(0) To Numbers(1) Step Sgn(-(CLng(Numbers(1)) > CLng(Numbers(0))) - 0.5)
        ExpandedSeries = ExpandedSeries & Delimiter & Letter & Z
      Next
    Else
      ExpandedSeries = ExpandedSeries & Delimiter & Parts(X)
    End If
  Next
  ExpandedSeries = Replace(Mid(ExpandedSeries, Len(Delimiter) + 1), Chr$(1), "")
  Exit Function
SomethingIsNotRight:
  ExpandedSeries = CVErr(xlErrValue)
End Function

After macro - results in columns D:F

	A​	B​	C​	D​	E​	F​
1​	C45-C50​	0.6​		C45​	C45-C50​	0.6​
2​	C99-C101​	0.5​		C46​		0.6​
3​				C47​		0.6​
4​				C48​		0.6​
5​				C49​		0.6​
6​				C50​		0.6​
7​				C99​	C99-C101​	0.5​
8​				C100​		0.5​
9​				C101​		0.5​
10​

<tbody>
</tbody>

Hope this helps

M.

I worked like a charm, thanks.Also there is one more favor I'd like to ask.

I have series like C1,c2,c3,....c100,c101,c102,....c200,c201,c203....c301,c302,c303....etc
I wish to sort it however when I use regular sort by option I'll get c,c100,c101,c102..c2,c201.. you get the idea right?
But I want it to sort like c1,c2,c3,c4,c5,....c100,c101,c102....c201,c201...etc
Let me put it in table

C1	0.1
C100	0.2
C101	0.3
C2	0.4
C201	0.5
C201	0.6
C3	0.7
C301	0.8
C302	0.9
C303	1

<tbody>
</tbody>

expected

c1
c2
c3
c4
c5
c6
c100
c101
c102
c103

<tbody>
</tbody>

The values in COL B should correspond to it COL A... You think will it possible to do?

 

[Marcelo Branco]

This is a different question. Please, create a new thread.

M.