Simple question about textjoin

English
#1

Hey,

I bet my inquiry is pretty easy to solve, but I can´t figure it out.

I have rows A, B, C. Row A and B contains Text(or not) and I have merged both A and B together into Row C, with the following Formula: =TEXTJOIN(" , "; ;A3;B3).

This is nice but I want my formula only to textjoin when ROW A AND B are filled with Text. As soon as Row B is empty, I want Textjoin to ignore A and B and not write anything into Row C.

As you can see in my screenshot I want that C4 and C6 do not return any value, because row B is empty.

image description

image description

OR

If this ain´t that easy to accomplish I could also imagine it , if the output in row C stays(even with empty B cells) , but as soon as something is in Cell B it is formatted in Bold and RED, and so forth.
I tried integrating the style function on top of the textjoin function but have not been able to get it working.

Thank you in advance for your help

#2

Use (in Row 3 e.g.) =IF(COUNTIF(A3:B3;"=")=0;TEXTJOIN(",";1;A3:B3);"")
if both cells of the range must contain text, and
=IF(B3<>"=";TEXTJOIN(",";1;A3:B3);"")
if you want to cancel the output even if only cvolumn B is empty in the respective row.
BTW: For ranges of only 2 cells you don’t need TEXTJOIN().
There are alternatives.

Formulas don’t write anything anywhere, but return a result to the cell they were called from.
In case you want to see nothing in column C, you should return the empty string. The cell will, however, then answer TRIUE if asked for ISTEXT().

Note: COUTIF(A3:B3;"=") returns the number of cells in the range A3:B3 being empty (BLANK) or having got the empty string returned by a formula.

What you describe as a kind of workaround is often needed anyway, and then accomplished by ConditionalFormatting.

#3

Thanks for the answers they worked on my example I gave you, but I did forget that in my actual
File the rows are not adjacent, so checking a range does not really work since there are other cells with text included.

Can it be that textjoin is only for ranges? I tried (A3;F3), (A3&F3) and some other things, but I can´t get it to work on non-adjacent cells.

#4

TEXTJOIN accepts a sequence of ranges (or expressions), and a single cell is accepted as a range.
Therefore your first attempt should have worked. About the reasons for its failing I can’t tell anything without seeing the complete formula, and not informed about how it failed.
The second attempt is wrong in a fundamental way. It would concatenate the referenced string-values, not the references. If you want to continue working with spreadsheets, you will need to learn about the concepts. A first glance at [Tutorial] Ten concepts that every Calc user should know (View topic) • Apache OpenOffice Community Forum might be helpful - but not sufficient, of course.
“Trial and Error” may be a strategy in a few cases, but it will reduce to “Error” if applied without a plan.
A fundamental concept often not sufficiently regarded is the functionally convenient design of sheets. Don’t fragment cell ranges you need to evaluate together, e.g. (if possible at all, of course).

#5

Yes, the formula works perfectly, but I made a mistake when asking you. because between A3 and F3 are other text cells with other information. so my result cell stays mostly empty, except for the time when A3-F3 are filled with text. Then it shows all content from cells A to F. I will keep this formula, but it does not work for my purpose of checking two cells far aside from another.

#6

The COUNTIF() part needs a contiguos range.
But you can (e.g.) use =IF(F3<>"=";TEXTJOIN(",";1;A3;F3);"")
or =IF(AND(A3<>"";F3<>"=");TEXTJOIN(",";1;A3;F3);""). This only, of course, if it’s definitely impossible to change the sheet’s design in a way placing the strings needing to be passed to TEXTJOIN() adjacent.

#7

Thx, I will try it out later when I got the time. When I started my Sheet it was never intended to do a full format, just for Information purposes. So I could always change the structure(and yesterday I played with that thought), but I thought that this would be a good possibility to get back into the functions game and learn something. And it would be more work recutting and pasting everything with updating the functions, etc.

#8

Well, I tried it but can´t seem to get it to work, the result is always a complete other cell than expected.

I tried it with B5 and F5 as my text is in those cells, but the result is always that the content from B3 is shown? When I go one Cell down (X6) with my function, my result is that of cell B4, and so forth.

I am confused. could it be that it matters if you reference the cells from another page, in the same sheet? my result cell is X5, do I need to work with $? Keme´s Formula with the simple IF and Trim gets the same result. It always shows cells above the wished result and also ONLY what is written in B5. Content of F5 gets never shown in any of the two formulas.

#9

Please ignore my last comment. BOTH of your formulas work in my sheet. I made a mistake, but figured it out.
Now since I can and will use all 3 Formulas mentioned here I want to give all 3 of you the correct answer, but that does not seem to be possible.

#10

Don’t worry. Simply accept one of the answers you judge to be correct, to show other users with a question expressed in similar wording that there is an accepted answer to find in this thread.

#11

Hallo

=IF(OR(A3="";B3="");""&T(STYLE("Standard"));TEXTJOIN(", ";1;A3:B3)&T(STYLE("your_style_name")))

#12

If possible I’d always avoid the STYLE() function and use conditional formatting instead; which here is easy as the result string is either an empty string or not.

Reasons are that

  • STYLE() adds additional computation overhead to the formula, which may be completely unnecessary if the formula cell is not currently visible, whereas conditional formatting is computed only for visible cells (or scrolling into view).
  • STYLE() is not part of the ODF OpenFormula (ODFF) specification but a LibreOffice/OOo extension; other spreadsheet implementations mostly (if at all) don’t support it and it can’t be exported to other file formats. Conditional formatting is supported at least by some.

In fact STYLE() was introduced (IIRC even before conditional formatting existed in its current form) to allow formula expressions with volatile asynchronous Add-In functions to alter the cell style as soon as the async function’s result is available. Every other use today is misuse.

#13

Even if I can´t use the Formula I still thank you because it shows me a good example of the style function use which I may need in the future :wink:

#14

You don’t say much about how to handle a situation where first cell is blank, so I assume that this will never happen or it is otherwise not relevant.

TEXTJOIN() is useful when you have a large set of values to combine into a delimited list. The function can take a combination of single cells and continuous ranges, as needed. When the result is always two entries, a simple concatenation is just as good.

Also, a cell appearing blank may contain a space (or six), so use TRIM() to “normalize” content. (Some users will “blank” a cell by pressing spacebar. Some other data sources will export empty data as spaces.)

I believe that this formula is fairly robust: =IF(TRIM(B3)="";"";A3&","&B3)
Adjust to your real data layout.

#15

The first cell always has content and will never be blank. Later when I get time I will try this simple IF function. I tried with the concatenation formula but I am lacking experience on how to get it to work for my purposes.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.