I am learning how to use formulas in spreadsheets, I do use libre office.

I need to sort out data in a quite huge messy spreadsheet.

Each column contains mixed data, the sheet is huge, dozens of columns and thousands of rows, if the spreadsheet does not contain errors each cell in a row either contains a different keyword or is empty, there should not be two cells in the same row containing the same keyword.

The problem to solve is to sort out all the data so to reach to have a new spreadsheet in which each cell marked with a given specific keyword is kept in the same position but placed in one column dedicated to that same keyword.

the kind of spreadsheet with mixed up cells to be sorted out

the data in the spreadsheet has to be fixed so to appear in this way

A formula that can be used to extract sorted out data from a cell is the following:

=IF(SEARCH("Text1";B2;1);B2;0)

The formula can be dragged to each cell below to hit the proper cell next to it. The result is correct.

The results are correct, but I do not know why the expected 0 is not printed, there is #VALUE! instead

The logic is very simple, if the cell contains the keyword or any other text that contains that keyword the result is the full content of that cell, otherwise the result is 0.

Here comes the first question, why do I get #VALUE! as a result for those cells that do not contain the keyword? I expected to get 0 instead, just as indicated in the formula, I tried to leave this filed empty and also to put the 0 result in quotes, the actual result is always the same, #VALUE!...

However, of course this formula extracts only the information contained in one column, so for each other column the process must be repeated.

In order to avoid to create a column with the formula for each column in the spreadsheet or anyway to process each column one by one and more importantly to have then to merge all the results to form one columns containing only cells with a given keyword I thought to use the same formula extending the parsing to each next cell in the row as follows:

=IF(SEARCH("text";B2;1);B2;IF(SEARCH("text";C2;1);C2;IF(SEARCH("text";D2;1);D2;0)))

The logic is very simple and should output in one go a column containing all the cells containing the keyword that are found in the row, check if the first cell in the row contains a word using the search function, if does then the result is the content of that cell, otherwise perform the next test, the next test is the same, check if the next cell contains a certain word using the search function, if does then the result is the content of that cell, otherwise proceed to the next test…. and so on until last test, if no test gave a true result then print 0 (but we get #VALUE!, OK I could live with that...).

In theory should work for a any number of cells, but in the practice does not at all, in fact does work only for the first IF test and cell indicated in the formula.

WHY?

The result using the extended version of the formula to parse N cells in sequence is the same obtained with the simple formula to parse only one cell

Finally, how do I resolve this problem using IF and Search?

Is there any other better approach and way to solve this kind of problems and sort out data in huge spreadsheets of this kind?

Thank you for any hint and help.