Trimming data in a Microsoft Excel spreadsheet
When importing data from external sources, the data may sometimes be wrongly formatted. How can we format the data properly? Using the following excel functions, you will be able to accurately format the data for data analysis.
The primary excel function that is used to trim data in a Microsoft Excel spreadsheet is =TRIM(Cell Name). TRIM is used to remove extra spaces from lines of text and is really useful to quickly correct the data formatting. However, a disadvantage of using =TRIM(Cell Name) is that it can only remove extra spaces, but not other non-printable characters.
The next excel function is =CLEAN, which is used to remove all 32 non-printable characters including extra spaces from the lines of text. The majority of these 32 non-printable characters originate from low-level programming applications and commonly appear when you import data from such applications.
The third excel function is =SUBSTITUTE, which as the name suggests, is used to substitute characters for other characters. This function is equally important, as it’s main purpose is to change a non-printable character to a space. To overcome the problem that =TRIM can only be used to remove extra spaces, you can use the functions =SUBSTITUTE together with =TRIM, so as to remove all extra spaces and non-printable characters from your line of data.
Next, you can use the excel function =VALUE, to convert a text that represents a number to a number. For example, for the amount $9999, we can use =VALUE($9,999) to convert the amount $9999 to become the value 9999.
Another excel function that you can use is the =PROPER function enables you to capitalize the first letter in each sentence and all the rest of the letters will be lowercase. This function is useful for proper capitalization of text in a dataset.
Other common Microsoft Excel functions include:
Two of the most used functions in excel are VLOOKUP and HLOOKUP. The purpose is VLOOKUP to look for a value in a column of a spreadsheet while the purpose of HLOOKUP is to look for a value in a row of a spreadsheet. We use VLOOKUP by entering this function: =VLOOKUP(A9, A1-C5, 3, False). The first field A9 represents the field where you input what you are searching for. The second field A1-C5 represents the lookup array. The third field 3 represents that the function will be searching in the third column of the lookup array. The last field False indicates that it is an exact match, while True indicates that the result will be approximate.
Another commonly used excel function is concatenate, which means the combining of data from different columns in a spreadsheet. An example of how this works is if =CONCATENATE(A3,” “,B3,” “,C3) would be =(John C. Trump) where the column A contains a person’s first name, column B contains a person’s middle name, and column C contains a person’s last name. Also take note to include the quotation marks along with a space in the middle which helps to space the content out.