Thread: VBA code needed to help reduce series of characters

Hi!

I have the following code that I borrowed from another thread and tweaked only slightly.

Function ConcatRange(inputRange As Range, Optional delimiter As String, Optional delimiter2 As String) As String
    Dim oneCell As Range
    With inputRange
        If Not (Application.Intersect(.Parent.UsedRange, .Cells) Is Nothing) Then
            For Each oneCell In Application.Intersect(.Parent.UsedRange, .Cells)
                       
                If oneCell.Text <> vbNullString Then
                    ConcatRange = ConcatRange & delimiter & oneCell.Text
                End If
               If oneCell.Text = vbNullString Then
                   ConcatRange = ConcatRange & delimiter2 & oneCell.Text
               End If
               Next oneCell

            ConcatRange = Mid(ConcatRange, Len(delimiter) + 1)
            
        End If
    End With
End Function

Basically this works beautifully to concatenate a large amount of columns into one and recognize the blank cells as something (in this case a back slash). For my worksheet in a single row my cells either have a word in them or are blank cells between words. So when I apply the above code now I see my result looking something like (purely for example)...

/ ALL CAPS TOGETHER/// all lower case together////

Here, each back slash (according to the above code) represents one blank cell. What I ACTUALLY want to see is the following:

ALL CAPS TOGETHER/ all lower case together/

so that the words or 'phrases' are still separated by something (again, backslash) but only by ONE. Essentially, taking a series of blank cells and reading each series as a single backslash. Can anyone help?? I can not do these individually as my actual data I'm applying this to is several thousand columns.

Thanks in advance!

Maybe ...

Function ConcatRange(rCat As Range, Optional sSep As String = "") As String
    Dim cell        As Range
    Dim rInt        As Range

    Set rInt = Intersect(rCat.Worksheet.UsedRange, rCat)
    If Not rInt Is Nothing Then
        For Each cell In rInt
            If Len(cell.Text) Then
                ConcatRange = ConcatRange & cell.Text & sSep
            End If
        Next cell
    End If
End Function

Post a workbook with examples and expected results that cover the waterfront.

What logic did you use to decide that

could look like this/ here's another phrase/ Hi/

dosn't include a leading slash, but

/ALL CAPS TOGETHER /all lower case together /

does?

That wasn't my question; I was referring to your desired results.

Function ConcatRange(rCat As Range) As String
    Dim cell        As Range
    Dim rInt        As Range

    Set rInt = Intersect(rCat.Worksheet.UsedRange, rCat)
    If Not rInt Is Nothing Then
        For Each cell In rInt
            If Len(cell.Text) Then
                ConcatRange = ConcatRange & cell.Text & " "
            Else
                If Right(ConcatRange, 1) <> "/" Then ConcatRange = ConcatRange & "/"
            End If
        Next cell
    End If
    
    If Left(ConcatRange, 1) = "/" Then ConcatRange = Mid(ConcatRange, 2)
End Function

You're welcome, good luck.