I checked the proposed formula in 7.5.3.2 and 25.2.3.2 - it should work. Is it an array formula? If you edit the formula in the Function Wizard, is the Array checkbox checked?
Strange, very strange. If the error was in the range sizes, the error would be #N/A.
Let’s try two ways to solve the problem at once - let’s try to make the formula work and let’s try to read the original CSV file so that we don’t have to fish out these cadences in different columns, get them all in column D.
I’ll tell you about the formula, and you’ll try to adjust it according to your data.
The main trick is performed by the nested IF() function - it looks for the word cadence in each of the cells of the rectangular range. It is assumed that if a cell contains this word, then only this word, no spaces in front or behind, no other text. (If this is not the case, then the comparison - the first parameter of the IF() function - will need to be done differently).
If the cell contains anything other than the word cadence, then the IF() function returns an empty string. It’s simple, it’s clear. But if the word is found, then the value from the next cell is returned. And it’s done quite simply - the second range (in the screenshot it’s red) is exactly the same size as the range in which we’re looking for the word - blue - but shifted to the right by one column.
As a result of entering such a function IF() as an array formula (otherwise it will not work - after all, its parameters are arrays of values), we will get a rectangular array of the same size as the original ranges, filled with either empty strings or the found values.
Now we simply give this array to the TEXTJOIN() function and ask it to throw away empty values (second parameter 1), and what remains to be concatenated into a long string through the separator from the first parameter. We enter all this using Ctrl+Shift+Enter (or using the Function Wizard and force the Array checkbox). This should work.
We could limit the ranges to one row, something like
=TEXTJOIN(";";1;IF(C2:G2="cadence";D2:H2;"")) and copy that formula down - then we would have all the cadence values in one column - each value in the same row where it was found.
You didn’t say what values are in the cells next to the word cadence - strings? dates? integers?
Now let’s try to read the CSV file correctly. When we open it in Calc, we should see the Text Import settings window. I’m sure that if you correctly set the flags in this window, the file will be read as a neat table, the data will not be scattered across different columns. I know that you have been using the office for many years, but even veterans make mistakes. If it doesn’t work, then give a screenshot from this window, we’ll try to find the cause of the import error.
