Split combo data in one row cell to multiple rows

[Prinz Jaymes]

Hello
I have some data that looks like this in Excel from two rows(one is header and followed by data): As you can see, the 3rd row has a combo set of information (all in ONE CELL)

Name School Class Scholarship_Pool Semester GPA
Matthew King St. Mark Sophomore DEANS_LIST_STH or MAYORS_OFFICE_SCH or PROGRAM_FOR_SCKILLED_KDS or SMT_SCHOLARSHIP_REWARD or TRIAL_PROGRAM_BOARDING_NBP WINTER 3.699


Can someone please help me with a VBA code that can place all the different names separated by "or" in Row 3 in different rows, but still maintain the advanced filters (name, school, class, etc..)

Name School Class Scholarship_Pool Semester GPA
Matthew King St. Mark Sophomore DEANS_LIST_STH WINTER 3.699
Matthew King St. Mark Sophomore MAYORS_OFFICE_SCH WINTER 3.699
Matthew King St. Mark Sophomore MAYORS_OFFICE_SCH WINTER 3.699
…..etc

 

[Luke M]

Trying to look for "keys" we can use to split up, is the section that gets split up:
a) Always italicized?
b) Always in all CAPS?
c) Always separated by "or"?
d) Always have the same text (ie, does it always have "DEADS_LIST_STH...BOARDING_NBP"?

 

[Prinz Jaymes]

Hi Luke
They are always in CAPS and separated by "or" ..
Names always look similar to answer d above.

 

[Luke M]

This macro might work then. Select the cell with text, and macro will write output into cells below.

Option Explicit

Sub SchoolList()
Dim rngStart As Range
Dim strOriginal As String
Const strSchools As String = "DEANS_LIST_STH or MAYORS_OFFICE_SCH or PROGRAM_FOR_SCKILLED_KDS or SMT_SCHOLARSHIP_REWARD or TRIAL_PROGRAM_BOARDING_NBP"
Dim splPair As Variant
Dim splList As Variant
Dim i As Long

'Setup our start point
Set rngStart = ActiveCell
strOriginal = rngStart.Value

'Split the string into sections
splPair = Split(strOriginal, strSchools)
splList = Split(strSchools, " or ")

'Loop through each part
Application.ScreenUpdating = False
For i = 0 To UBound(splList)
    rngStart.Offset(1 + i, 0).Value = splPair(0) & splList(i) & splPair(1)
Next i

Application.ScreenUpdating = True
End Sub

 

[Prinz Jaymes]

Luke ,
That was very clever. Thanks.
I have a hundred more rows with different names all bundled up like the example o showed. Can the code include an entire column?

This macro might work then. Select the cell with text, and macro will write output into cells below.

Option Explicit

Sub SchoolList()
Dim rngStart As Range
Dim strOriginal As String
Const strSchools As String = "DEANS_LIST_STH or MAYORS_OFFICE_SCH or PROGRAM_FOR_SCKILLED_KDS or SMT_SCHOLARSHIP_REWARD or TRIAL_PROGRAM_BOARDING_NBP"
Dim splPair As Variant
Dim splList As Variant
Dim i As Long

'Setup our start point
Set rngStart = ActiveCell
strOriginal = rngStart.Value

'Split the string into sections
splPair = Split(strOriginal, strSchools)
splList = Split(strSchools, " or ")

'Loop through each part
Application.ScreenUpdating = False
For i = 0 To UBound(splList)
    rngStart.Offset(1 + i, 0).Value = splPair(0) & splList(i) & splPair(1)
Next i

Application.ScreenUpdating = True
End Sub

Luke

 

[Luke M]

Need to make a few tweaks. This is now setup to look at range A2:A100 (adjust if needed), and outputs to col B (again, adjust if needed)

Sub SchoolList()
Dim strOriginal As String
Const strSchools As String = "DEANS_LIST_STH or MAYORS_OFFICE_SCH or PROGRAM_FOR_SCKILLED_KDS or SMT_SCHOLARSHIP_REWARD or TRIAL_PROGRAM_BOARDING_NBP"
Dim splPair As Variant
Dim splList As Variant
Dim i As Long
Dim recCount As Long

Application.ScreenUpdating = False
splList = Split(strSchools, " or ")

'Starting output row
recCount = 2

'Setup our starting range
For Each c In Range("A2:A100")
    strOriginal = c.Value
   
    'Split the string into sections
    splPair = Split(strOriginal, strSchools)
   
    'Loop through each part
    For i = 0 To UBound(splList)
        'Changed this to output to column B
        'Change letter if desired
        Cells(recCount, "B").Value = splPair(0) & splList(i) & splPair(1)
        recCount = recCount + 1
    Next i
Next c
Application.ScreenUpdating = True
End Sub