Concatenate non-blank cells

[tone640]

Hi, I've tried a few different solutions to this one, but not quite there.

I have 3 cells that I need to concatenate with a comma, but only want to include them if they are not blank. Any one (or two) could be blank, and there could be two word entries.

The closest I have is:
Flat, A, 27, Bailey, St

Which I need to come out as:
Flat A, 27, Bailey St

using the formula:
=SUBSTITUTE(TRIM(BV6 & " " & BW6 & " " & BX6), " ", ", ")

Can this be done without a UDF? What if there are more than 3 cells?

Thanks in advance.

[Cutter]

Try using * (or some other character that won't be found in your data) to substitute for.

Like this:
=SUBSTITUTE(TRIM(BV6 & "*" & BW6 & "*" & BX6), "*", ", ")

[tone640]

Nice one, and to omit any cells that are blank at the start of the range, would you use IF?

E.g. I get , , Bailey St if there is no 'Flat A' and '27':

=IF(BV6&BW6="",BX6,IF(BV6="",SUBSTITUTE(TRIM(BW6 & "*" & BX6), "*", ", "),SUBSTITUTE(TRIM(BV6 & "*" & BW6 & "*" & BX6), "*", ", ")))

Edit2: This still didn't quite work, so found a UDF to use:

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
    Dim Cell As Range, Area As Variant
    For Each Area In CellRanges
        If TypeName(Area) = "Range" Then
            For Each Cell In Area
                If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
            Next
        Else
            ConCat = ConCat & Delimiter & Area
        End If
    Next
    ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

=ConCat(", ",A3:A9,C5,"I'm text")

[Cutter]

If you still want (or prefer) a formula solution, try:

=SUBSTITUTE(IF(BV6="","",BV6&", ")&IF(BW6="","",BW6&", ")&IF(BX6="","",BX6&", "),", ","",COUNTIF(BV6:BX6,"?*"))

or

=SUBSTITUTE(BV6&REPT(", ",BV6<>"")&BW6&REPT(", ",BW6<>"")&BX6&REPT(", ",BX6<>""),", ","",COUNTIF(BV6:BX6,"?*"))

[tone640]

Many thanks for your help - I'll give this a go.