Occasionally you may run into an error when attempting to upload files to Catalyst: "Invalid Name Associated With ID(s) or Name(s) cannot contain the Unicode Control character (see list below)". Excel has functionality to help you with this.
This article describes the formula syntax and usage of the CLEAN function in Microsoft Excel, which should help fix invalid name or ID error messages for your upload sheets.
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.
Use this functionality in any worksheet information from which you want to remove nonprintable characters.
- Find a blank cell off to the right of your data or in another tab.
- Type in your formula: =CLEAN and press the Tab key to begin selecting your data.
- Select and highlight all cells that you want to clean.
- Press Enter to complete the formula.
- Your data has been cleaned.
- Replace your old data with the new clean dataset by pasting as values over the old set.
- Save and reattempt to upload your file.
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.
Removes the nonprintable characters CHAR(9) and CHAR(10) from the text string in cell A2.
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...
- The basics of cleaning your data.
- Spell checking.
- Removing duplicate rows.
- Finding and replacing text.
- Changing the case of text.
- Removing spaces and nonprinting characters from text.
- Fixing numbers and number signs.
- Fixing dates and times.
- Merging and splitting columns.
- Transforming and rearranging columns and rows.
- Reconciling table data by joining or matching.
- Third-party providers for cleaning data.