Help using the Clean function in Excel

[rexxturbo]

Hi

I am trying to use a Python script to parse my spreadsheet for a project.

I am receiving an error : UnicodeEncodeError: 'ascii' codec can't encode character u'\u2019' in position 67: ordinal not in range(128)

So I started to use the Clean function in excel to clean the data of any unwanted or nonprintable characters.

The issue I am having is after the data is cleaned I am unable to move it to a new spreadsheet. I get a #Ref! (invalid cell reference error) or the data just disappears depending on the cell I am trying to copy.

I am hoping someone has had experience with this.

Thanks

 

[Scott Huish]

I'm in the process of learning Python but that character is this: Unicode Character 'RIGHT SINGLE QUOTATION MARK' (U+2019)
Perhaps, you could replace that character with a standard quote character (ASCII: 39)

 

[rexxturbo]

Hi Scott,

Thanks for the reply. i actually don't know python at all, so I am trying to fix what I think is hidden characters in my spreadsheet so the python script works.

 

[Scott Huish]

CLEAN shouldn't remove that character, there is a specific set of characters it removes. See here: https://support.office.com/en-us/article/CLEAN-function-26F3D7C5-475F-4A9C-90E5-4B8BA987BA41

Perhaps, you could run the following VBA macro on your file:

Sub test()
Cells.Replace ChrW(2019), "'"
End Sub