如果末尾只有一个值要连接，则在结果末尾不保留逗号 [duplicate]

问：

我当前的代码采用选定的列并将它们连接起来，用逗号分隔每个值，在工作表的右侧插入一列。但是，每当有一行只有一个值时，它就会在末尾留下一个尾随逗号。如果有多个值，它不会在结果末尾留下逗号，只有当有一个值时，例如：

多个值：“text1，text2，text3” 单个值：“text1”，

我需要什么：“text1”

法典：

    Sub ConcatenateSelectedRanges()
    Dim selectedRange As Range
    Dim cell As Range
    Dim concatenatedString As String
    Dim resultColumn As Range
    Dim lastColumn As Long
    Dim rowIndex As Long
    Dim area As Range
    Dim isDateResponse As VbMsgBoxResult
    
    ' Check if a range is selected
    On Error Resume Next
    Set selectedRange = Selection
    On Error GoTo 0
    
    If selectedRange Is Nothing Then
        MsgBox "Please select a range to concatenate.", vbExclamation
        Exit Sub
    End If
    
    ' Prompt the user if the content in the selected range is a date
    isDateResponse = MsgBox("Is the content in the selected range a date?" & vbCrLf & _
                            "Selected Range: " & selectedRange.Address, vbYesNo + vbQuestion, "Date Check")
    
    If isDateResponse = vbCancel Then
        Exit Sub ' Exit if the user cancels the prompt
    End If
    
    ' Find the first available empty column to the right of the selected range
    On Error Resume Next
    Set resultColumn = selectedRange.Offset(0, selectedRange.Columns.Count + 1).Resize(, 1)
    On Error GoTo 0
    
    If resultColumn Is Nothing Then
        ' If no available empty column is found, insert a new column at the end
        lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set resultColumn = Columns(lastColumn + 1).EntireColumn
        resultColumn.Insert
        ' Resize the resultColumn to match the height of the selected range
        Set resultColumn = resultColumn.Resize(selectedRange.Rows.Count)
    End If
    
    ' Loop through each area within the selection
    For Each area In selectedRange.Areas
        ' Loop through each row in the current area
        For rowIndex = 1 To area.Rows.Count
            ' Reset the concatenated string for each row
            concatenatedString = ""
            
            ' Loop through each cell in the current row
            For Each cell In area.Rows(rowIndex).Cells
                If cell.Value <> "" Then ' Ignore blank cells
                    If isDateResponse = vbYes Then
                        ' Append the formatted date to the string
                        concatenatedString = concatenatedString & Format(cell.Value, "mm/dd/yyyy") & ","
                    Else
                        ' Append the cell value to the string
                        concatenatedString = concatenatedString & cell.Value & ","
                    End If
                End If
            Next cell
            
            ' Remove the trailing comma if there's only one non-empty cell
            If Len(concatenatedString) > 0 Then
                If Len(concatenatedString) > 1 Then ' Check if there's more than one character
                    If Right(concatenatedString, 1) = "," Then
                        concatenatedString = Left(concatenatedString, Len(concatenatedString) - 1)
                    End If
                End If
            End If
            
            ' Display the concatenated string in the corresponding row of the new column
            resultColumn.Cells(rowIndex + area.Rows(1).Row - 1, 1).Value = _
                resultColumn.Cells(rowIndex + area.Rows(1).Row - 1, 1).Value & IIf(resultColumn.Cells(rowIndex + area.Rows(1).Row - 1, 1).Value = "", "", ",") & concatenatedString
        Next rowIndex
    Next area
End Sub