Cell contents separated by dash"-"

S

sainathd

New Member
Joined
Aug 29, 2017
Messages
16
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]colA[/TD]
[TD]ColB[/TD]
[/TR]
[TR]
[TD]C45-C50[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C99-C101[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Expected Result

[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[/TR]
[TR]
[TD]C45[/TD]
[TD]C45-C50[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C46[/TD]
[TD][/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C47[/TD]
[TD][/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C48[/TD]
[TD][/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C49[/TD]
[TD][/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C50[/TD]
[TD][/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C99




[/TD]
[TD]C99-C101[/TD]
[TD]0.5
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]C100 [/TD]
[TD] [/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]C101[/TD]
[TD][/TD]
[TD]0.5[/TD]
[/TR]
</tbody>[/TABLE]


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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
C45-C50​
[/TD]
[TD]
0.6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
C99-C101​
[/TD]
[TD]
0.5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My code
Function by Rick Rothstein
Code: 
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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
C45-C50​
[/TD]
[TD]
0.6​
[/TD]
[TD][/TD]
[TD]
C45​
[/TD]
[TD]
C45-C50​
[/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
C99-C101​
[/TD]
[TD]
0.5​
[/TD]
[TD][/TD]
[TD]
C46​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C47​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C48​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C49​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C50​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C99​
[/TD]
[TD]
C99-C101​
[/TD]
[TD]
0.5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C100​
[/TD]
[TD][/TD]
[TD]
0.5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C101​
[/TD]
[TD][/TD]
[TD]
0.5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

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

Code: 
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
 
Upvote 0
Marcelo Branco said:
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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
C45-C50​
[/TD]
[TD]
0.6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
C99-C101​
[/TD]
[TD]
0.5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My code
Function by Rick Rothstein
Code: 
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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
C45-C50​
[/TD]
[TD]
0.6​
[/TD]
[TD][/TD]
[TD]
C45​
[/TD]
[TD]
C45-C50​
[/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
C99-C101​
[/TD]
[TD]
0.5​
[/TD]
[TD][/TD]
[TD]
C46​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C47​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C48​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C49​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C50​
[/TD]
[TD][/TD]
[TD]
0.6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C99​
[/TD]
[TD]
C99-C101​
[/TD]
[TD]
0.5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C100​
[/TD]
[TD][/TD]
[TD]
0.5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C101​
[/TD]
[TD][/TD]
[TD]
0.5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

M.
Click to expand...


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


[TABLE="width: 500"]
<tbody>[TR]
[TD]C1[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]C100[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]C101[/TD]
[TD]0.3
[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]C201[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]C201[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]C301[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]C302[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]C303[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

expected

[TABLE="width: 500"]
<tbody>[TR]
[TD]c1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c103[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The values in COL B should correspond to it COL A... You think will it possible to do?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
172
SBExcel123
S
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
228
Thlama
T
G
  • Question Question
Filling cells with multiple " IF " & " THEN " in the formula
Replies
0
Views
259
Guy Boot
G
V
  • Question Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
406
kvsrinivasamurthy
K
P
  • Solved
Computing investment return formula
Replies
8
Views
662
polarwoc
P

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top