Filtering out the most used sentence

Hi,

I have an excel which has 399878 rows and 1 column. In here i want to highlight the top 100 most used sentences. The real problem in here is several rows contains special characters mixed up with the sentences. For eg.

Row1: I have a problem in excel and need to resolve.
Row2: ##I have a problem in excel and need to resolve.
Row3: I have a problem in excel and need to <48><89>resolve.
Row4: <77??>I have a problem in excel ** and need to ??? resolve.

in above the special characters should not be considered and only the sentence "I have a problem in excel and need to resolve." should only be considered while filtering. So from 399878 rows, not taking the special characters i need the top 100 sentence used. Can any one pleaseeeee help?

Manoj

Thanks Amy, now i see the B1 column without the special characters. but how can i filter out the top most 100 used sentences from that. if the formula gets too complicated please dont bother about the special characters. the main task is to filter out the top 100 sentences available. can you please help me

use a pivot table

Thanks a lot Amy!!! This is really working in a good way. I faced another problem. is see a sentence 28 times and another 32 times repeating. so in there i need only one 28 and one 32 to appear in the final result. Can you please help on how to filter this.

It'd be easier to use a pivot table

Pivot table: i Saw it and was confusing me, can you tell me the procedure to do on what i am asking for?

Sorry Amy, it isnt working!! an example on what i am getting:

Text1: amy (it gets repeated 90 times, so in column B 90 is appearing 90 times)

Text2: kyle (it repeats 50 times, in column B 50 is appearing 50 times)

Text3: Manoj (it repeats 10 times, so 10 is appearing 10 times)

If i give number filter in column B as top 2 90&50 should appear, what in real life happens is only 90 is appearing, and another problem is 90 is not appearing in a single row instead it is showing 90 as 90 times, it should actually appear only once and so the next 50 and 10.

Select your data, insert pivottable, drag the sentences column into rows and count of sentences into values, you can then rank descending by count of sentences