How to clean a file to remove special characters

Description

Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.

Instructions

Use this functionality in any worksheet information from which you want to remove nonprintable characters.

1. Find a blank cell off to the right of your data or in another tab. 
2. Type in your formula: =CLEAN and press the Tab key to begin selecting your data.
3. Select and highlight all cells that you want to clean. 
4. Press Enter to complete the formula.
5. Your data has been cleaned. 
6. Replace your old data with the new clean dataset by pasting as values over the old set. 
7. Save and reattempt to upload your file. 

Example 1

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Example 2

Another example of where this function can assist is when error messages occur as a result of uploads back into Catalyst, such as Hierarchy uploads (e.g. "Invalid name associated with IDs or Names cannot contain the Unicode Control character...")

Using Excel's CLEAN() function on the ID and Name columns will fix the above error.

More ways to clean your files

Misspelled words, stubborn trailing spaces, unwanted prefixes, improper cases, and nonprinting characters make a bad first impression. And that is not even a complete list of ways your data can get dirty. Roll up your sleeves. It is time for some major spring-cleaning of your worksheets with Microsoft Excel.

This article from Microsoft has a sound list of recommended ways to clean up your data if you run into issues: https://support.microsoft.com/en-us/office/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19 including...

1. The basics of cleaning your data.
2. Spell checking.
3. Removing duplicate rows.
4. Finding and replacing text.
5. Changing the case of text.
6. Removing spaces and nonprinting characters from text.
7. Fixing numbers and number signs.
8. Fixing dates and times.
9. Merging and splitting columns.
10. Transforming and rearranging columns and rows.
11. Reconciling table data by joining or matching. 
12. Third-party providers for cleaning data.