create list of all used characters in a sheet

Is there a formula (probably not) or a function in vba, to create on a new sheet a list of every character that is used in a specific sheet?

I have a sheet with several columns of product content. So cells with very long descriptions of products.
Due to an incorrect previous upload in a system, some of the characters have been through an UTF-8 mixup. ÿ and Š and ý, etc...
The majority of them are relatively easily spotted because they are common in the descriptions.

But to finetune, I would like to make a list of every character present in the sheet.
That way I can easily spot all the strange characters and do a find and replace on them and thus quickly clean up the sheet.

Does anybody have an idea on how to create such a list?
It should extract all characters from all cells in a sheet, undouble and list them in a single column.

Untested but you could try something like this. In a secondary sheet add all the forbidden characters that you need to be removed. Then you can run this macro:

Please Login or Register  to view this content.

It would help if you upload sample workbook. Demonstrating your need.

Check yellow banner at top of the screen.

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Thanks for the offered help Paul, but I don't want a script to remove any characters, just list them in a new sheet.
They have to be replaced by other characters. But since I'm not sure which, I need to find the characters in the source sheet first.

So, to accomodate CK76 and all others....

Attached is an example.

The source sheet has some text and numeric values.
I need a script that scans through all the cells in the sheet and take any character it did not encounter before and put it as a result in the "List of Characters" sheet.
An example of the result of this script for this particular source sheet is sheet: "List of Characters".
The scan should be case sensitive.

Using Get & Transform.

1. Add named range "data" with following dynamic formula.

Formula:

Please Login or Register  to view this content.

2. Get & Transform Data -> Get Data -> From Other Sources -> Blank Query.

3. Once inside query editor go to "View" ribbon tool and click on "Advanced Editor"

4. Set following as Source.

Please Login or Register  to view this content.

5. Select all columns and right click -> Merge with no delimiter.

6. Add another blank query. This time reference the first query's [Merged] column as source with following transformation.

Please Login or Register  to view this content.

This will give list of distinct characters used in range.

7. Convert list to table and set column data type to text. If you wish, you can filter out single space from list. Load back to sheet.


See attached sample.

Note: If you add more data below. It will automatically add to list, if you refresh the table. But not if you add more columns. For that, you'll need to adjust dynamic named range.