# Ignore blank cells on formula to join text from different cells

#### bmp_costa

##### New Member
Hi everyone, I've got the following formula and can't find a way to ignore the blank cells if not data is entered. I'm trying to avoid having double comas when no data is in one of the cells. using formula (=CONCATENATE(A1, ", ",B1, ", ",C1)
 Column 1 Column 2 Column 3 Formula result One Two Three One, Two, Three Three , , Three

<tbody>
</tbody>

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the board.

Are you comfortable using a UDF? If so, please see the below...

Sheet1

 * A B C D 1 One Two Three One,Two,Three 2 * * Three Three

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:33px;"><col style="width:33px;"><col style="width:64px;"><col style="width:110px;"></colgroup><tbody>
</tbody>

 Cell Formula D1 =Concatenate_Range(A1:C1,",") D2 =Concatenate_Range(A2:C2,",")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Code:
``````Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)
Dim Cell As Range

Application.Volatile

For Each Cell In myrange
If Len(Cell.Value) > 0 Then
Concatenate_Range = Concatenate_Range & Cell & myDelimiter
Else: Concatenate_Range = Concatenate_Range
End If
Next Cell

If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter))

End Function``````

If you dislike macros:

=A1 & IF(AND(A1<>"",B1 & C1<>""),", ","") & B1 & IF(AND(B1<>"",C1<>""),", ","") & C1

Thanks Neil, that worked perfectly. Unfortunately I've just realised that I don't need a range i.e. (A1:C1) but only certain cells to be considered. My knowledge is good enough to use the formula and code you sent but not enough to figure this out for myself.
 A B C D E 1 One Two blah Three formula to join text only from A1, C1 and E1, ignoring blank and other cells even if these contain text as in D1. Result=One, Two, Three 2 3

<tbody>
</tbody>

Well the code I posted will ignore blanks, but what are the rules for other text strings you want ignored? If you only want the string equivalent of numbers ("one","two","three",etc) to be returned, what's the largest number you would expect?

Thanks Gary's Student, that works as well and it may save Neil some work with a new code. I can tweak this to do what I need. Thanks again

Hi Neil, thanks for the reply. Your code works fine for a range. There isn't actually a rule for the cells I want ignored and I have used "one", "two", "three" just as an example. the text can be just anything. I appreciate your help and I must say the code you sent first works fine for my initial question, but I will use the formula from Gary's Student as it fits what I need to do. Thanks so much for your help guys.
Well the code I posted will ignore blanks, but what are the rules for other text strings you want ignored? If you only want the string equivalent of numbers ("one","two","three",etc) to be returned, what's the largest number you would expect?

Thank you both, I know its messy but got it working with the following:=A1 & IF(AND(A1<>"",C1 & E1<>""),",","") & C1 & IF(AND(C1<>"",E1<>""),",","") & E1& IF(AND(E1<>"",H1<>""),",","") & H1& IF(AND(H1<>"",J1<>""),",","") & J1& IF(AND(J1<>"",L1<>""),",","") & L1& IF(AND(L1<>"",O1<>""),",","") & O1& IF(AND(O1<>"",Q1<>""),",","") & Q1& IF(AND(Q1<>"",S1<>""),",","") & S1& IF(AND(S1<>"",V1<>""),",","") & V1& IF(AND(V1<>"",X1<>""),",","") & X1& IF(AND(X1<>"",Z1<>""),",","") & Z1

If you want to keep it semi-simple and don't feel like making a UDF, ...recording macros, or self-writing any VBA, here is a elegant & scalable vanilla-formula.

1. The inner JOIN function works well by itself if you have no blanks, but when you do it still uses a delimiter, causing duplicates. In my example I used just white-space " " as a delimiter so that I can utilize the...
2. TRIM function to eliminate all excess spaces (defined as: a space can not be a preceding or ending character, but only space can be used as a deliminiter between non-space characters.
3. Finally use the SUBSTITUTE formula to convert all space " " delimiters back into comma-space delimiters ", "
4. Profit.

What is the VBA coding for JOIN() ?

Replies
7
Views
585
Replies
3
Views
97
Replies
3
Views
301
Replies
8
Views
195
Replies
4
Views
360

1,211,865
Messages
6,104,448
Members
447,909
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back