In the latest version of Excel, there are two function formulas related to text extraction, that is, TEXTAFTER and TEXTBEFORE, according to the English words, AFTER and BEFORE, they are two corresponding formulas
One is text post-extraction and the other is text pre-extraction
类似于RIGHT和LEFT
So we only need to learn one, and the other can learn as well
SHARE TEXTAFTER FORMULA TIPS TODAY
1. Formula composition
It consists of 6 parameters
=TEXTAFTER(text,delimiter,instance_num,match_mode,match_end,if_not_found)
The first parameter is the text
The second parameter is the separator
The third parameter is to start the search after the first few delimiters
The fourth parameter is whether it is case-sensitive
Parameter 5 whether to use the end of the text as a separator
parameter 6, if the returned value is not matched
It looks abstract, but we can understand it with examples
2. Find the content behind the separator
For the data on the left, we want to extract the market, we just need to enter the formula:
=TEXTAFTER(A2,"-")
3. Multiple characters match
As follows, we only want to extract the area, if we also use the above formula, it will only extract after the first character by default
If we want to extract after the 2nd character, we need to enter the 3rd parameter, and the formula we enter is:
=TEXTAFTER(A2,"-",2)
Or, for our third parameter, we can enter -1, which represents the first area to carry from right to left (sometimes, counting from left to right, the position of each carry is different, and we only want to extract the last carry, we can use this):
4. Case-sensitive matching
For example, we need to extract the number to the right of the letter g
The formula entered on the left = TEXTAFTER(A2,"g"), which is case-sensitive by default
If we don't want case-sensitive matching
You can enter 1 in the 4th parameter
The formula to be entered is:
=TEXTAFTER(A2,"g",,1)
5. Whether to use the end of the text as a separator
When the 5th parameter is not filled, if it does not match, then it will get an error value
IF YOU MATCH THE END, THEN WHEN YOU USE TEXTAFTER, IT WILL RESULT IN A BLANK SPACE
IF YOU'RE USING TEXTBEFTER, IT'S THE TEXT ITSELF
6. The result returned when the match is not reached
The sixth parameter indicates what result will be returned if the match is not made
For example, we enter the formula:
=TEXTAFTER(A2,"g",,1,,"无")
Have you learned about this formula? Try it out!