[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

VB Sctipt to seperate cell data separate by semi-colons into separate rows,

I have an Excel spreadsheet (exported from an Access DB) with 2 columns and roughly 2000 rows.  The first column has a network username and the second column has all of the security groups that the username belongs to delimited by semi-colons. It currently looks like this:

Username     Security Groups
Admin            Domain Users; HL Users; LC VPN SSL Only
Support            Domain Users; HL EH Animal Control; HL EH Users; HL Users; LC VPN SSL Only

I need the data in the second Security Groups column to be separated into different rows and for each of those rows to still contain the first column with the Username.  I would like it to look like this:

Username     Security Groups
Admin           Domain Users
Admin           HL Users
Admin           LC VPN SSL Only
Support           Domain Users
Support           HL EH Animal Control
Support           HL EH Users
Support           HL Users
Support           LC VPN SSL Only

Can someone please either help me with a VB script that I can run within Excel or a query that I can run within Access to accomplish this?
Thank you in advance.
1 Solution
The subroutine that follows will create a new worksheet with the results you desire:
Sub SplitGroups()
Dim fromRow As Long
Dim fromSheet As Worksheet
Dim group As Variant
Dim groups() As String
Dim toRow As Long
Dim toSheet As Worksheet

    Set fromSheet = ActiveSheet
    Sheets.Add After:=Sheets(Sheets.Count)
    Set toSheet = ActiveSheet
    toSheet.Cells(1, 1) = fromSheet.Cells(1, 1)
    toSheet.Cells(1, 2) = fromSheet.Cells(1, 2)
    fromRow = 2
    toRow = 2
    Do While Not IsEmpty(fromSheet.Cells(fromRow, 1).Value)
        groups = Split(fromSheet.Cells(fromRow, 2).Value, ";")
        For Each group In groups
            toSheet.Cells(toRow, 1) = fromSheet.Cells(fromRow, 1)
            toSheet.Cells(toRow, 2) = Trim(group)
            toRow = toRow + 1
        Next group
        fromRow = fromRow + 1
End Sub

Open in new window

VBA Script:
Sub SplitContents()
Dim i As Long
Dim pos As Integer

i = 2
While (Cells(i, 1) <> "")
  pos = InStr(Cells(i, 2), ";")
  If (pos > 0) Then
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells(i, 1) = Cells(i + 1, 1)
    Cells(i, 2) = Left(Cells(i + 1, 2), pos - 1)
    Cells(i + 1, 2) = Mid(Cells(i + 1, 2), pos + 1)
  End If
  i = i + 1
End Sub

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now