Multiple values in each cell

[Jenniferg980]

Hello all!

I have recently downloaded data collected from a questionnaire but I notice that I have several values in each cell. 

How can I ...

1. Convert each 'new line' of value to it's own cell?

2. Count how frequency of each value?

Ultimately I want to be able to use the data in a table to see which levels of English are most commonly taught but at the moment I can only match the exact cells and that doesn't work for me. 

Thanks in advance!

[#2]

@Jenniferg980 

Even if I think that a picture is worth a thousand words, it is unfortunately not true in the case of Excel.
It would be advantageous if you insert a file (without sensitive data), so you could get an answer much faster.
So you help those who want to help, don't need to guess where it should count and how you want it to end.
A win-win situation for everyone ... if a file is included.

So simply read from the picture this formula could help you, as I said it is a guess.

=COUNTIF(select area, "A1") etc.

Nikolino
I know that I don't know (Socrates)

[#3]

@NikolinoDEThanks for your reply. 

I have attached a file below. 

I think it is only counting the first value in each cell and not all of them? 

[#4]

@Jenniferg980 

FREQUENCY function

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019

https://support.microsoft.com/en-gb/office/frequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9?u...

The table is pivoted, so I can't help you because my knowledge about it is very poor (to put it mildly). A specialist like Mr Baklan has to work here to provide a quick and efficient solution :-).

Sorry that I took your time and couldn't give you a quick solution.

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)

[#5]

@Jenniferg980 I believe you need a Power Query solution for this. If you are open to such an approach, please have a look at the attached file.

The key element here is to split column B using a line feed as the separator. Then unpivot the table, and in the end, group a single column counting all occurrences of all levels.