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)[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column 1
[/TD]
[TD]Column 2
[/TD]
[TD]Column 3
[/TD]
[TD]Formula result
[/TD]
[/TR]
[TR]
[TD]One
[/TD]
[TD]Two
[/TD]
[TD]Three
[/TD]
[TD]One, Two, Three
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Three
[/TD]
[TD], , Three
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

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.

Please show us some rows to concatenate with real data as long as the desired result.

 

[Aladin Akyurek]

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.

Try TEXTJOIN if it's available on your Excel version.

 

[Fazila]

Try TEXTJOIN if it's available on your Excel version.

Unfortunately it isn't I tried following a post up thread

 

[Fazila]

Please show us some rows to concatenate with real data as long as the desired result.

Ok so the data would look something like this:

Column A Column C Column E
Media Music PE
SRA, 1029 OSO,0010 SLN, 5010
OSO/0010
SRA, 1029 JEN,5010

Hope that gives a gist of what the data is like. For a group of cells where there is only one entry I only want that entry so row 2 would return OSO/0010, however, for rows where there are 2 entries I need cell A3&" 3pm to 4pm/"cell E3&" 4pm to 5pm".

Hope this makes sense.

Thanks

 

[Aladin Akyurek]

Ok so the data would look something like this:

Column A Column C Column E
Media Music PE
SRA, 1029 OSO,0010 SLN, 5010
OSO/0010
SRA, 1029 JEN,5010

Hope that gives a gist of what the data is like. For a group of cells where there is only one entry I only want that entry so row 2 would return OSO/0010, however, for rows where there are 2 entries I need cell A3&" 3pm to 4pm/"cell E3&" 4pm to 5pm".

Hope this makes sense.

Thanks

Two points:

Are you saying that you TEXTJOIN is not available on your system?

The above sample is not clear about what you have exactly in A, in C, and in E.

Also, you should specify the outcome per record you posted as a value, not as a formula.

 

[Xzila]

The verbal description of your data set is pretty confusing.

On another note if you have a google account /gmail. Good Docs / Google Drive is free. There is also a JOIN formula there. ( I also think Google handling of arrays is superior to excel )
Also if you would like to clarify data sets, you can copy cells directly from google sheets into rich text editors ( like this one ), and it will paste with all of your formatting. That way you can show people your table instead of just describing it at length.

 

[Aladin Akyurek]

... I also think Google handling of arrays is superior to excel...

Care to elaborate on your claim?

 

[Xzila]

Aladin,

It's a bit off topic from this thread. Excel requires you to explicitly declare array sizes, and many of the formulas are incompatible with arrays. For instance if you want to do a VLOOKUP an item C1 in a range A1:B50 that is not sorted, but the item you are looking up is in column A from column B, in excel you the solution I usually see is to use a CHOOSE formula.

So for excel the formula would be: =VLOOKUP(C1,CHOOSE({1,2},B1:B50,A1:A50),2,FALSE).
Where in Google Sheets you can do: =VLOOKUP(C1,{B1:B50,A1:A50},2,FALSE)

•Google sheets has additional array typeformulas FILTER, QUERY, SPLIT which are all very powerful -- in excel you are required to do these through utilites ( usually under data tab ).
•The arrays in Google Sheets are dynamic so you don't have to explicitly select the range.
•The error codes in google sheets are more useful and tell you what the exact problem is. ( for example Array could not be expanded because it would overwrite the data in cell B47)
•The ARRAYFORMULA formula in google sheets works the same as excel as hitting ctrl shift enter, and it intuitively allows for many regular formulas to be generalized ( not all formulas, for instance index match doesn't work well, but VLOOKUP doess )
• You can embed array formulas in the header and no longer worry about it. For instance if you have an invoice sheet that lists #OfUnits in column A, and Price in Column B, you can embed an array formula in Column C's header and it will automatically multiply the results from the first two columns ={"Total";FILTER(A2:A*B2:B,A2:A<>"",B2:B<>"")