Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Posts by 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.
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",""...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
A couple of year ago a user of Super User reported difficulties with summing values purporting to be Euros imported to LibreOffice Calc in CSV format. In the Q the user does not mention the locale...
One way is to create a lookup table (sorted ascending) and take advantage of VLOOKUP's is_sorted parameter to opt for "the nearest match (less than or equal to the search key)". Say a table like so...
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 ...
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...
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
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...
Given ColumnA contains a Text header (A1), an allowed maximum number (A2), fifty tickboxes (A3:A62) and a formula in A63 of: =countif(A3:A62,TRUE) how can I block application of more than the a...
I believe the answer at the moment is "No" but a very simple formula in LibreOffice 5.4.3.2 can achieve the desired result: =LEN(SUBSTITUTE(B1;0;"")) where B1 contains: =BASE(A1;2;8) as valid and...
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 ...
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...
Yes, only the sixth to eleventh characters are required and these may be extracted with the MID function. MID is a string function that returns Text but a double unary operator should trigger Sheet...
A self-answered question - How to get DATEVALUE to recognize RSS 2 pubDate (RFC822)? - written by spcsLrg on Web Applications Stack Exchange offers this formula: =DATEVALUE(TRIM(REGEXREPLACE(A1,"^...
A Q on Stack Overflow asks how to merge two columns where each row has only one populated cell. The example was from (on the left) to (on the right): a|b a 0| ...
