CSV Data download Symbols

[Ravenous]

Hi All,

I am seeking an elegant and simple solution for an issue as my current resolve is long complex and not 100% accurate.

I have a csv raw data download that contains various symbols that Excel doesn’t seem to recognize and converts them to strange combinations.

I am current using a substitute function to change each symbol one by one but I know there must be some type of blanket change for this type of character set.

Current code is below with only some of the characters but there could be potentially hundreds. I have also attached the raw data.

Help please!

Please Login or Register  to view this content.

Thanks

[shg]

You don't need to loop:

Please Login or Register  to view this content.

[Ravenous]

Thanks shg this definitely simplifies the code but I was actually looking for a way to change the overall character set so that I don't have to convert the symbols.

Is there a way that this can be done? I think it has something to do with UTF-8 but I am not sure what this is exactly.

Many thanks.

[shg]

That could well be true (that the spurious symbols are multi-character encodings from UTF-8), but I think it needs to be addressed at the source, when the CSV is exported from wherever it comes from.

[Ravenous]

Thanks again, however this is not an option for me as I am not running the download and have no control over how it is set up.

Does anyone know a way that this can be achieved in Excel?

[Marc L]

Hi !

Yes ! one of the classic way is first to rename file to .txt and open it :
the import assistant will prompt, fill the file origin as 65001 Unicode (UTF-8) …

I have others way but can't work with this badly formated file !

[Leith Ross]

Hello Ravenous,

While working on this problem, I found that opening the csv file in Notepad displays the contents correctly. Your original file is not UTF-8, it is ANSI with embedded Unicode characters. In the word "Meredith's", the single quote is Unicode 2019. This can be entered manually in Excel by pressing ALT and holding it then pressing the digits 0146 on the numeric keypad. Numlock must be on for this to work correctly. Some word processors, like Word, will make this change automatically when you type a single quote character. Excel will not.

Now that the file can be opened in Notepad correctly means a macro could be written to open the csv in Notepad and then copy the text back to the worksheet. The copied text could be parsed manually using Text to Columns, or by the macro itself.

[Ravenous]

Figured it out...

It was as simple as importing the file as UTF-8 instead of copy and paste!

Appreciate everyones assistance.