Excel: How to analyze data in a table that contains multivalue cells

I am working in a science project right now about insects, and I have been logging information about the insects I have been finding along. Right now, I realize that it was a bad decision to register the name of all the insects that I been finding per each observation. I am not allowed to provide to much information because it is confidential, but I am going to add a similar example of my case in the following table:

Something like the above table. In my actual table, I have cells with 5 or 6 diferent insects. The thing is:

• How can I search for all the different values? I mean, I want to create a table that contains all the different values and how many of them are... Something like the following table:

(There are at least 100 different insects and some of them just appear once, so it is impossible for me to search all the different names manually.

Furthermore, I was thinking about converting my table to a long structure. Something like the following;

Instead of this:

I want this:

I was thinking that this arrangement should be better than the one that I have now. I hope someone can help me with this issue. Thanks so much.

I tried the above, but I did´t got it. That's the reasons I asking for help.

To answer 'How can I search for all the different values?', the below formula will create a unique list of the insect families (where the insect families are in range B2:B100)

=UNIQUE(TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B100),"|",", ",TRUE))

You will then be able to use a COUNTIF() formula to find how many tests contain each family.