Qiang Qiang~ Hello everyone! I am Satellite Chan who is studying data analysis again~
Previously, Wei gave a brief introduction to the cleaning method of “dirty” data.
I have to say that it will be much easier to analyze the cleaned and sorted data.
This time, I brought it again 7 functions for data cleaningto help everyone improve the efficiency of organizing data!
Let’s take a look~
1. Clear
The first category is the most straightforward, which is to get rid of unnecessary data.
Text imported from other applications may contain characters that cannot be printed by the current operating system, so we canUse the CLEAN function to clear these non-printing characters.
As shown in the figure, when using the VLOOKUP function to find the performance of “Zhao Liu”, the result is wrong:
Use the LEN function to check the number of characters in column B and find that there is an extra invisible character in cell B5:
Clear it with the CLEAN function:
=VLOOKUP(E2,CLEAN(B2:C5),2,FALSE)
In this way, the VLOOKUP function can search normally!
The TRIM function removes all spaces in text (except single spaces between words).
If the text you import from a web page has a lot of spaces, then TRIM is the way to go!
=TRIM(A1)
All the spaces before words are cleared, but the spaces between words are preserved.
Use functions to clean data, saving time and effort~
2. Replace
Going back to the previous example, I used the VLOOKUP function to find employee performance, but an error value appeared because there was an invisible character in cell B5:
To prevent erroneous values from interfering with data analysis, you have to use the IFERROR function:Replaces erroneous values in the data with the given value.
=IFERROR(VLOOKUP(E2$B$2$C$5,2,FALSE)"")
REPLACE is also a replacement function, but its application scope is wider than IFERROR.
WillReplace a value with another given valuethat's what it does.
Generally we use it to code phone numbers, ID numbers or other personal information.
=REPLACE(C2,7,8,"****")
Use the replacement function to quickly process the data, so you don’t have to worry about privacy leaks~
3. Extraction
The third type of data cleaning function is the extraction class. The most commonly used ones are the three brothers left, middle and right, namely LEFT, MID and RIGHT.
Data extraction can also be understood as cleaning complicated data and leaving the parts we need.
▋01 Extract area code(Ask for place of birth)
=LEFT(C2,6)
▋02 Extract birthday
=MID(C2,7,8)
▋03 Extract file extension
=RIGHT(B2,LEN(B2)-FIND(".",B2))
4. Write at the end
Okay, today we learned 7 data cleaning functions in one breath!
❶ CLEAN
❷ TRIM
❸ IFERROR
❹ REPLACE
❺ LEFT
❻ RIGHT
❼MID
Everyone still remembers theseFunction usage scenarios? Try to remember~
Additional reading:The messy data in Excel that drives people crazy can be sorted out in just one minute!