收集在一个单元格中组合的列范围值，后跟逗号，并以 & 分隔符结尾

随着添加的 Do...循环，您可以获得所需的结果。

Sub GetPipeData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim collectedValues As String
    Dim addComma As Boolean

    ' Set the worksheet containing the values
    Set ws = ThisWorkbook.Sheets("INPUT")

    ' Find the last used row in column AM
    lastRow = ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row

    ' Initialize the collectedValues variable
    collectedValues = ""
    'addComma = False ' Initialize the flag to not add a comma

    ' Loop through the cells in column AM from row 4 to the last used row
    For Each cell In ws.Range("AM4:AM20")
        ' Check if the cell is not empty
        If Not IsEmpty(cell.value) Then
            ' If addComma is True, add a comma before the value
            If addComma Then
                collectedValues = collectedValues & ", "
            End If
            ' Add the value to the collectedValues variable
            collectedValues = collectedValues & cell.value
            ' Set addComma to True for the next iteration
            addComma = True
        End If
    Next cell
    'This code added
    pos = 1
    Do While InStr(pos + 1, collectedValues, ",") <> 0
        pos = InStr(pos + 1, collectedValues, ",")
    Loop
    If pos<>1 Then collectedValues = Left(collectedValues, pos - 1) & " &" & Mid(collectedValues, pos + 1)
    'Till here
    ' Print the collected values in cell BB4
    ws.Range("BB4").value = collectedValues
End Sub

或者你可以只使用一个工作表公式：

=LET(
    Φ,AM4:AM20,
    SUBSTITUTE(TEXTJOIN(", ",,Φ),", "," & ",MAX(1,SUM(N(Φ<>""))-1))
)