pnuts‭

In Office 365 and Excel 2019 there is a TEXTJOIN: =TEXTJOIN(",",,A1:A10) which can be written into the cell where the output is required. Assumes A1:A10 has been populated to suit.

posted 3y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

This is happening because of assumptions made by the software. "E notation" is a standard convention popular with scientists, mathematicians and engineers as a convenient way to represent and proce...

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

In this Q a user asked for a simple way to represent this data: - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1...

2 answers  ·  posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

Although spreadsheets were never really intended for text processing, I think a formula solution is possible (LibreOffice 5.4.3.2): For the first example above: =(LEN(A1)-LEN(SUBSTITUTE(A1,"XYZ",""...

posted 3y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

First, such formatting is conditional, so there is no need for the IF functions here (nor, normally, in general for Conditional Formatting [CF]). Formatting is triggered when the response to a form...

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

Regarding length, the correct answer used 43 characters, but one was a space serving no purpose, so say 42 for: =CONCATENATE(LOWER(MID(A1,1,1)),LOWER(B1)) Most obviously, the concatenating functi...

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

An as yet unanswered OpenOffice Calc - Highlighting the higest value in multiple columns posted by Rui on Super User from early this year asked whether a condition was possible to: fill with a c...

0 answers  ·  posted 2y ago by pnuts‭  ·  last activity 2y ago by pnuts‭

An easy way (in LibreOffice Calc 5.4.3.2) is not to attempt directly to overwrite what is already present but to create the desired results with a formula in a different column, then replace the co...

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

Assuming Check Box Form Controls in ColumnD, yes in OpenOffice Calc 4.1.4, though for some the process may be too tedious and entering say an x instead may be preferred. Unlike in Google Sheets for...

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

An as yet unanswered Q from Stack Overflow [SO] enquired: how to stop looping this formula where the formulae were: in B9 =16,50 in B10 =B9-B11 in B11 =B10/2 without changing ...

1 answer  ·  posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

Since the formulae have circular references (Err:522) the spreadsheet setting needs to be changed to enable iteration (Tools > Options… > LibreOffice Calc > Calculation, tick Iterations an...

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

Yes, several. Example outcomes from various inputs and functions are shown in the table below, with those compliant with the current requirement highlighted: CEILING CEILING.MATH CEILING.PRECISE

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

One part of an as yet unanswered Q on Super User asks whether conditional formatting in OpenOffice Calc can be used to highlight an entire row based on whether a checkmark in the row has been selec...

1 answer  ·  posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

An as yet unanswered question on Web Applications, INDIRECT cell reference - Can't copy cell as reference cell pattern remains same, from @Daniel Klose is: I have an Overview Sheet where I want to...

1 answer  ·  posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭

When in ColumnC B can be returned with =char(Column()+63) and since not inside double quotes this will automatically adjust to C in ColumnD. This way a string acceptable to INDIRECT can be construc...

posted 2y ago by pnuts‭  ·  last activity 2y ago by Alexei‭