Ignore blank cells on formula to join text from different cells

bmp_costa

New Member
Joined
Apr 10, 2013
Messages
6
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>
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
Welcome to the board.

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

Sheet1

*ABCD
1OneTwoThreeOne,Two,Three
2**ThreeThree

<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>

Spreadsheet Formulas
CellFormula
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
 

Gary's Student

Well-known Member
Joined
Aug 4, 2012
Messages
1,014
If you dislike macros:

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

bmp_costa

New Member
Joined
Apr 10, 2013
Messages
6
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.
ABCDE
1OneTwoblahThreeformula 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>
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
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?
 

bmp_costa

New Member
Joined
Apr 10, 2013
Messages
6
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
 

bmp_costa

New Member
Joined
Apr 10, 2013
Messages
6
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?
 

bmp_costa

New Member
Joined
Apr 10, 2013
Messages
6
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
 

Xzila

New Member
Joined
Mar 4, 2014
Messages
7
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.

=SUBSTITUTE(TRIM(JOIN(" ",{your range}))," ",", ")

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,360
Messages
5,444,010
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top