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 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y 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 | PER...

2 answers  ·  posted 4y ago by pnuts‭  ·  edited 2y ago by Wicket‭

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 4y ago by pnuts‭  ·  last activity 4y ago by Alexei‭

A Q on Stack Exchange from a very long time ago included: I have first names in one column and second names in another, I want to create a third column that contains the first character from the...

1 answer  ·  posted 4y ago by pnuts‭  ·  edited 2y ago by ArtOfCode‭

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 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y ago by Alexei‭

Some while ago a user of Web Applications asked after a function to avoid "an if/else just to handle negative numbers properly". Clarification provided was: Presently it round based on absolute...

1 answer  ·  posted 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y ago by Alexei‭

Perhaps (if a process of several relatively familiar steps is easy to remember) but additional formulae may be appropriate, a little extension to the source data required and some formatting. Howev...

posted 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y 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 4y ago by pnuts‭  ·  last activity 4y ago by Alexei‭