Excel separate cell value

Question

How do I split the text so each option goes to the appropriate option cell(A,B,C.D,E).

Please note that the option text length may vary as well and some option might be missing. For example option B might be missing, leaving the option in the cell as ACDE, how can I get it to skip the empty option and go to the next.

Answer 1

I would tend to use 2 cells working together.

Typically, assuming that the string to parse is in C5, your cell first cell (D5 in my example) could contain:

=MID(C5,FIND("|",C5)+1,LEN(C5))

This cell would contain the remaining string to parse.

Your second cell could contain:

=LEFT(C5,LEN(C5)-LEN(D5)-1)

It would use the remaining string to parse as determined above and take the left part of it until the next delimiter.

If you do so, the last column will not work, so you will have to take care about manually.