55 10.1 Cleaning And Restructuring Data In Excel
Emese Felvegi; Noreen Brown; Barbara Lave; Julie Romey; Mary Schatz; Diane Shingledecker; and Robert McCarn
Before we can work with our data, we need to make sure it’s valid, accurate, and reliable. In the age of Big Data, companies may spend just as much or more on maintaining the health and cleaning their data as they spend on collecting or purchasing it in the first place. Consider the issues that can stem from missing or wrong values, duplicates, and typos. The validity, accuracy, and reliability of your calculations depend on your ability to keep your data up-to-date. Many estimates show that about 30% of your data may become inaccurate over time (JD Supra, 2019; Strategic DB, 2019) and even small data sets can be costly to clean, let alone files that are tens or hundreds of thousands of records deep – or much more if you are using large scale databases.
There are many data cleaning solutions out there for a wide range of file formats, data volumes, or budgets. However, there are many things we can accomplish using Excel functions and features so that you can process our data quickly and effectively. Instead of purchasing an application, assigning data cleaning to an employee, or hiring a service to scrub your data, for records under a million per sheet, Excel can save you a great deal of time and funds using a variety of functions and features. Table 10.1 shows you some important functions that can help you clean up your data.
| CLEAN | Removes all nonprintable characters from text. |
| TRIM | Removes all spaces from text except for single spaces between words. |
| CONCATENATE | Join two or more text strings into one string. |
| LEFT | Returns a string containing a specified number of characters from the left side of a string. |
| RIGHT | Returns a string containing a specified number of characters from the right side of a string. |
| MID | Returns a specific number of characters from a text string. |
| SEARCH | SEARCH returns the number of the character at which a specific character or text string is first found. |
| FIND and FINDB | Locate one text string within a second text string. |
| UPPER | Converts text to uppercase. |
| LOWER | Converts text to lowercase. |
| PROPER | Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. |
| TEXT | Change the way a number appears by applying formatting to it with format codes. |
| VALUE | Converts a text string that represents a number to a number. |
Table 10.1 A sample of text and data cleaning functions in Excel.
The following sections show the functions above in action. The Ch10_Data_File contains four sheets. The Documentation sheet notes the sources of our data. Text_FUNC sheet features a variety of common errors you may see in a data set, including line breaks in the wrong place, extra spaces or no spaces in between words, non-p