Ignore blank cells on formula to join text from different cells

[bmp_costa]

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>

 

[Xzila]

Actually I'm a little embarrassed (this being only my second post on Mr Excel). The formula I gave is only valid for google sheets ( web app ).

That said I did a little reasearch on this specific problem. I few ( still manual ) tricks that people used were:

● using vba try a function As string:

Function JoinRangeDelimiter(r As Range, delimiter As String) As String

JoinRangeDelimiter = JOIN(Application.WorksheetFunction.Transpose([r]), delimiter)


End Function


● using existing excel formulas and functions -- just copy and paste the formula downward.

	A	B
1	apple	=B15
2	organge	=IF(ISBLANK(B16),C15,C15&", "&B16)
3	strawberry	=IF(ISBLANK(B17),C16,C16&", "&B17)
4	tomato	=IF(ISBLANK(B18),C17,C17&", "&B18)
5	kiwi	=IF(ISBLANK(B19),C18,C18&", "&B19)

<colgroup><col style="width: 46px"><col width="120"><col width="226"></colgroup><tbody>
</tbody>

 

[philbruton]

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

I'm trying to follow this to link a string of text in cells B1 to S1 on a sheet.
It works but makes a few errors, there are missing commas in places.
This is what i'm using..
=B1 & IF(AND(B1<>"",C1<>""),",","") & C1 & IF(AND(C1<>"",D1<>""),",","") & D1 & IF(AND(D1<>"",E1<>""),",","") & E1 & IF(AND(E1<>"",F1<>""),",","") & F1 & IF(AND(F1<>"",G1<>""),",","") & G1 & IF(AND(G1<>"",H1<>""),",","") & H1 & IF(AND(H1<>"",I1<>""),",","") & I1 & IF(AND(I1<>"",J1<>""),",","") & J1 & IF(AND(J1<>"",K1<>""),",","") & K1 & IF(AND(K1<>"",L1<>""),",","") & L1 & IF(AND(L1<>"",M1<>""),",","") & M1 & IF(AND(M1<>"",N1<>""),",","") & N1 & IF(AND(N1<>"",O1<>""),",","") & O1 & IF(AND(O1<>"",P1<>""),",","") & P1 & IF(AND(P1<>"",Q1<>""),",","") & Q1 & IF(AND(Q1<>"",R1<>""),",","") & R1 & IF(AND(R1<>"",S1<>""),",","") & S1

Any ideas where the error is?
It only occurs when some cells between B1 and G1 have no data.
Many thanks in advance

Phil Bruton

 

[Xzila]

Hey Phil,

It has to do with the way you grouped the conditions in your If statements. If you evaluate it yourself on pen and paper you'll find that when, for instance, C1 is blank, that they first two if statements are FALSE & therefore do not produce the commas you would like to see between your cells B1 & D1.

If you remove the 1st condition of each and formula your result turns out better.

=B1 & IF(C1<>"",",","") & C1 & IF(D1<>"",",","") & D1 & IF(E1<>"",",","") & E1 & IF(F1<>"",",","") & F1 & IF(G1<>"",",","") & G1 & IF(H1<>"",",","") & H1 & IF(I1<>"",",","") & I1 & IF(J1<>"",",","") & J1 & IF(K1<>"",",","") & K1 & IF(L1<>"",",","") & L1 & IF(M1<>"",",","") & M1 & IF(N1<>"",",","") & N1 & IF(O1<>"",",","") & O1 & IF(P1<>"",",","") & P1 & IF(Q1<>"",",","") & Q1 & IF(R1<>"",",","") & R1 & IF(S1<>"",",","") & S1

It still tries to combine Empty cells even though they have no string, so a slight improvement on this particular formula you have made is:

=IF(B1<>"",B1,"")&IF(C1<>"",","&C1,"")&IF(D1<>"",","&D1,"")&IF(E1<>"",","&E1,"")&IF(F1<>"",","&F1,"")&IF(G1<>"",","&G1,"")&IF(H1<>"",","&H1,"")&IF(I1<>"",","&I1,"")&IF(J1<>"",","&J1,"")&IF(K1<>"",","&K1,"")&IF(L1<>"",","&L1,"")&IF(M1<>"",","&M1,"")&IF(N1<>"",","&N1,"")&IF(O1<>"",","&O1,"")&IF(P1<>"",","&P1,"")&IF(Q1<>"",","&Q1,"")&IF(R1<>"",","&R1,"")&IF(S1<>"",","&S1,"")

Lastly the 2016 versions of excel, and later, have a formula for this, finally!

=TEXTJOIN(",",TRUE,B1:S1)

 

[philbruton]

Thanks Xzila, appreciate the help

Phil

 

[Fazila]

Still trying to figure this out I tried using an If function which works fine but if all cells are blank and only the last cell has a value I get \JEN/5010 is there any way to remove the "" if there is no value preceding?

The formula I am using is =TRIM(IF(AV4<>"",AV4,"")&IF(BE4<>"","/"&BE4,"")&IF(BH4<>"","/"&BH4,""))

Thanks

 

[Xzila]

Hey Fazila,

Here is a solution to this problem using only substitute formulas, it resolves the problem with having extra delimiters or preceding delimiters using the trim formula.

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B1&"$DELIMITER$"&C1&"$DELIMITER$"&D1&"$DELIMITER$"&E1&"$DELIMITER$"&F1&"$DELIMITER$"&G1&"$DELIMITER$"&H1&"$DELIMITER$"&I1&"$DELIMITER$"&J1&"$DELIMITER$"&K1&"$DELIMITER$"&L1&"$DELIMITER$"&M1&"$DELIMITER$"&N1&"$DELIMITER$"&O1&"$DELIMITER$"&P1&"$DELIMITER$"&Q1&"$DELIMITER$"&R1&"$DELIMITER$"&S1," ","$SPACE$"),"$DELIMITER$"," "))," ",[B][COLOR=#ff0000]","[/COLOR][/B]),"$SPACE$"," ")

For your formula you can try:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(AV4&"$DELIMITER$"&BE4&"$DELIMITER$"&BH4," ","$SPACE$"),"$DELIMITER$"," "))," ",[B][COLOR=#ff0000]"/"[/COLOR][/B]),"$SPACE$"," ")

 

[Xzila]

CLARIFICATION ON MY LAST POST:

Replace the red text with your preferred delimiter

Update range to match the text you want to join.
There are two parts to the formula
(1) The middle part that has the range you want. For example if you want to join B4:D4 use: B4&"$DELIMITER$"&C4&"$DELIMITER$"&D4
(2) We wrap the middle part with the same substitute formulas regardless of what range you use
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( middle part ," ","$SPACE$"),"$DELIMITER$"," "))," ",","),"$SPACE$"," ")

 

[István Hirsch]

Try this:

Excel Workbook
	A	B	C	D
1	one	two	three	one, two, three
2			three	three
Sheet1

 

[Fazila]

That worked great Xzila thanks though I ended up using


=L234&IF(AND(L234<>"",F234<>""),"/","")&F234

So just to add a bit of a complication is there a way of adding text "3pm to 4pm" before the "/" and "4pm to 5pm" at the end of the text? I've used a Vlookup to substitute but that has meant adding extra columns was wondering if there was an easier way to do this.

Thanks again

 

[Fazila]

Thanks Istvan unfortunately that adds a "" between all spaces so I get

SRA,/1029/SLN,/5010 where I would just want SRA, 1029 3pm to 4pm/SLN 5010 4pm to 5pm.