formula to split data to their respective columns

Hi
Looking for a formula to split data drinks to their respective header columns

See attached of respected results

Thanks if any can assist

Try this in B2:

=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE("*"&$A2,"*",REPT(" ",250)),COLUMNS($A$2:A2)*250,250)),"ML",""),"L","")

Copy cross (C2, D2)

In E2:

=IF(RIGHT(A2,2)="ML","ML",IF(RIGHT(A2)="L","L",""))

something like this?

Try

C to D

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,"ML"," *ML"),"*",REPT(" ",99)),(COLUMNS($A:A)-1)*99+1,99))

in E

=IF(ISNUMBER(SEARCH("ML",A2)),"ML","L")

Phuocam,azumi,JohnTopleyAlKey thank you all for different approach.