Split values in column in a excel sheet by delimiter, but split identical values to the same new column

Question

So I have an excel sheet with a column that contains multiple values separated by the pipe character. I need to split those values to their own column, which works with the "Text to Columns" function.

But is there a way to move identical values from different rows to the same column? Empty cells might be required, because not all rows contain the the same number of values in the original column.

Its propably best to show it with this example:

I have this:

row	column_1
1	value_1|value_2|value_3|value_4
2	value_2|value_3|value_4
3	value_1|value_2|value_4
4	value_1
5
6	value_3|value_4
7	value_1|value_3|value_4

I need this:

row	column_1	column_2	column_3	column_4
1	value_1	value_2	value_3	value_4
2		value_2	value_3	value_4
3	value_1	value_2		value_4
4	value_1
5
6			value_3	value_4
7	value_1		value_3	value_4

The values contain the same string regardless of the row. So value_1 in row 1 is exactly the same as value_1 in row 3.

Answer 1

I found a workaround solution that can help produce the following output (the text in blue is a dummy variable)

In this case I have used a CONTAINS logic in the columns C-F, asking myself if the text in blue is found within column B, for each specific column. For example for cell C3: does cell B3 contain the text "value_1"? If yes, add the text to the cell, if not, leave the cell empty.

The formula I've used is shown belowm(can be dragged to all cells)

This workaround needs you to manually add the text you would be looking for in each column --> this is what I added in blue. I know this is a manual step but it only needs to be added once!