Long time no see, let's continue this series.
Today's main points are as follows:
- SUBSTITUTE function
- How to get a really useful ID number
- Further explanation of if function
SUBSTITUTE function
The SUBSTITUTE function is a function that can be replaced in Excel.
What do you mean?
If you need to change "handsome" into "handsome" in the following paragraph, it is best to use the SUBSTITUTE function.
#Original text: Jiang Chen of Fantasy Chenfeng is very handsome, especially handsome, super handsome. #Use function: =SUBSTITUTE ("Jiang Chen of Fantasy Chenfeng is very handsome, especially handsome, super handsome.", "handsome", "handsome") #Output results: Jiang Chen of Fantasy Chenfeng is very handsome, especially handsome, super handsome.
If the content is in cell A3, how to use it?
=SUBSTITUTE (A3, "handsome", "handsome")
In the SUBSTITUTE function, there are three parameters.
First: original content.
This is easy to explain. If you want to replace the contents of A4 cell, the first one is A4.
The second: the replaced content.
As in the example of "handsome".
Third: the replaced content.
As in the example, "handsome".
As for why we talk about this function, I can only say that it has something to do with the recent popularization work. I need to check the whereabouts of all school-age children in the school district. Using the county roster can quickly solve some problems. But!
Some of our schools export rosters with blank ID numbers and inexplicable symbols. As a result, the Vlookup function cannot be found. In order to solve this problem, we will begin the second section.
How to get a really useful ID number
Today, let's not talk about the 15 digit ID card number for the moment, but take the 18 digit ID card number as an example.
six hundred and fifty-four thousand and twenty-nine trillion and one hundred and ninety-nine billion seven hundred and nine million two hundred and sixty thousand and ten '654029199709260010 `654029199709260010
These are the ID numbers I saw. I don't know why, someone always helps to add these messy content.
Through the len function, it is clear that these guys are not 18 bits.
So, how can we get the 18 digit ID number we need directly?
Assume that the above ID numbers are A1, A2, A3, A4
Then, starting from B1, we can use this function to combine.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"'",""),"`","")
Perfect, these things are gone.
Further explanation of if function
A few days ago, I thought about how to handle if functions are nested at multiple levels?
Like the SUBSTITUTE function above, it can be nested together in multiple layers.
However, do you still remember how to use the IF function?
=If (your condition, the result returned if the condition is met, and the result returned if the condition is not met)
So, for example, if the student's score is above 60 points, he or she is qualified, above 75 points is good, and above 85 points is excellent. How to use if to deal with it directly?
Assuming that the content is in A3, how can we get it?
=IF (A3>=85, "Excellent", IF (A3>=75, "Good", IF (A3>=60, "Pass", "Fail"))
Here, we first judge whether A3 is greater than or equal to 85. If it is, it is excellent.
If it is not, that is, less than 85, check whether it is greater than or equal to 75. If it is, it returns OK.
If it is less than 75, check whether it is greater than or equal to 60. If it is qualified, it is a pass.
If it is less than 60, you will fail directly.
But here we need to note that these directions must be consistent.
Remember when we were Some fun formulas in Excel (3) Did you mention the SUMMPRODUCT function, INDIRECT function and RIGHT function in?
See you next time.
