如果我没理解错的话...
运行潜艇之前的数据是这样的：

运行子程序后（预期结果）：

Sub test()
Dim rg As Range: Dim cell As Range
Dim cnt As Integer

With ActiveSheet
Set rg = .Range("A4", .Range("A" & Rows.Count).End(xlUp))
End With

rg(1, 2).Resize(1, 2).EntireColumn.Insert

For Each cell In rg
    cnt = Len(cell.Value) - Len(Replace(cell.Value, ",", ""))
    With cell.Resize(1, 3)
        If cnt = 0 Then .Value = Split(",," & cell.Value, ",")
        If cnt = 1 Then .Value = Split(Replace(cell.Value, ",", ",,"), ",")
        If cnt = 2 Then .Value = Split(cell.Value, ",")
    End With
Next

Range("A3").Value = "Participant Last Name"
Range("B3").Value = "Participant First Name"
Range("C3").Value = "Participant Number"

End Sub

rg变量是列A中从第4行到最后一行的数据范围，然后插入两列，然后循环到rg中的每个单元格。
在循环中，它填充每个循环单元格。resize（1，3）使用逗号分隔单元格值的数组。它首先检查循环单元格值中是否没有逗号，然后在该值前面添加两个逗号，然后将其拆分。如果循环单元格值中有一个逗号，则将其替换为两个逗号，然后将其拆分。如果循环单元格值中有两个逗号，则它只是拆分循环单元格值。

从列表中插入两个空白列，编辑下面的列范围，然后尝试

Sub separate_values()
    Dim rng As Range
    Dim cell As Range
    Dim arr() As String
    Dim i As Long, lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & lastRow) ' Range of cells to check
    
    For Each cell In rng
    If cell.Value <> "" Then
        If InStr(1, cell.Value, ",") = 0 Then
            Cells(cell.Row, 3) = cell.Value
            Cells(cell.Row, 1) = ""
        Else
            arr = split(cell.Value, ",") ' Split cell value by comma and add to array
        
            For i = UBound(arr) To 0 Step -1
                 If UBound(arr) = 2 Then
                 Cells(cell.Row, i + 1) = arr(i)
                 Else
                 Cells(cell.Row, i + 2) = arr(i)
                 Cells(cell.Row, 1) = ""
                 End If
            Next i
           End If
           End If
    Next cell
End Sub