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.
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...
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",""...
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...
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 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...
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...
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...
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 ...
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...
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
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...
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 ...
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...
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...
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,"^...
If I have some text in a cell, how can I find the number of times another piece of text appears in it? For example, suppose A1 contains Peter Piper picked a peck of pickled peppers.. pick occurs 2...
