Leave no trailing comma at the end of the result if there's only one value at the end to be concatenated

Question

My current code takes selected columns and concatenates them dividing each value with a comma, inserting a column to the right of the sheet. However whenever there's a row where there's only one value it keeps leaving a trailing comma at the end. It doesn't leave a comma at the end of the result if there are multiple values, only when there's one, like for example:

multiple values: "text1,text2,text3" single value: "text1,"

What I need: "text1"

Code:

    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

Answer 1

Since the result string consists of text followed by the comma as per this code

                        concatenatedString = concatenatedString & Format(cell.Value, "mm/dd/yyyy") & ","
                    Else
                        ' Append the cell value to the string
                        concatenatedString = concatenatedString & cell.Value & ","

you only need to check the length of the string is greater than zero and remove the trailing comma!

            ' Remove the trailing comma if there's only one non-empty cell
            If Len(concatenatedString) > 0 Then
                        concatenatedString = Left(concatenatedString, Len(concatenatedString) - 1)
            End If

Take it easy!

The root cause of the trail comma is in this line:

            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

I don't want to dive into it. Just apply the additional check:

            resultColumn.Cells(rowIndex + area.Row - 1, 1) = _
                resultColumn.Cells(rowIndex + area.Row - 1, 1) _
                & IIf(resultColumn.Cells(rowIndex + area.Row - 1, 1) = "" Or Len(concatenatedString) = 0, "", ",") _
                & concatenatedString