CONCATENATE Issue

[lal12]

Hi, new here. Tried searchign the topic but can't find what I'm looking for. I'm trying to concatenate a string of words across 14 cells. Some cells are blank though. Is there a way to skip the blank cells. Sorry if this is basic but need the help.

Thanks!

Should end up looking like this: House, Milk, Blanket
Looks like this now: , , , House, Milk, , , Blanket

Using this as the formula

=CONCATENATE(F2,", ",G2,", ",H2,", ",I2,", ",J2,", ",K2,", ",L2,", ",M2,", ",N2,", ",O2,", ",P2,", ",Q2,", ",R2,", ",S2)

 

[MrKowz]

Welcome to the forums!

Try out this CONCATRANGE() UDF. To use it, Press Alt+F11 to load the VBA Editor, Go to Insert>Module, Copy/Paste this code into the window that loads up. Then go to your worksheet, and use the formula:

=CONCATRANGE(F2:S2,", ")

Public Function Concatrange(rng As Range, Optional delimiter As String = "")
   Dim rRng As Range
   
   For Each rRng In rng
      If rRng.Value <> "" Then
         Concatrange = Concatrange & rRng.Value & delimiter
      End If
   Next rRng
   
   If Len(Concatrange) > 0 Then
      Concatrange = left(Concatrange, Len(Concatrange) - Len(delimiter))
   Else
      Concatrange = ""
   End If
   
End Function

 

[lal12]

AWESOME! thank you so much, that just saved me at least an hour of work, unreal