Seperating same values into columns

Question

I would like to take a list like this:

and convert it to this, where the same values line up by column for each row record:

Answer 1

One way is to just populate the grid with a SEARCH formula.

I have added column headers which are used in the formula to determine the matches.

=IFERROR(IF(SEARCH(C$1,$B2)>0,C$1,""),"")

Put this formula into cell C3 and drag it over and down.

SEARCH will return the location, counting from the left, of the contents of C$1 within the string in cell $B2. SEARCH is not case-sensitive, so if you want a to be not equivalent to A, then use FIND instead.

Both SEARCH and FIND will return errors if not found, so the IFERROR captures that and returns "" instead.