How can I use Conditional Split Transformation to split some columns in excel source to 4 different tables

Without an example of the data it is hard to say, i.e. the column whose contents you want to split.
potentially you could extract the data in a column to its unique entries as a set of columns.

Have you considered trying to use an Access wizard to build a table scheme using the excel data as the source?

I think I would import your excel file as is into a temporary table on SQL and then do what you are seeking in the second step directly on the SQL server which might be simpler.
i.e. from this temporary table, extract the unique identifier for the row and the column with multiple data in it.
then look at converting this second temporary table to convert it to
unique identifier, single element, columname
NOTE a temporary table with a name ##temptable is accessible from any session, #temporarytable is only accessible from a session that created it. and is deleted when the session is closed.

charindex, and extracting items......