Excel looks at where the character last appears and returns the string formula

Do some explaining.

The code is as follows	Copy Code
LEN (A2) –len (Substitute (A2, "", "") gets the number of strings in a hollow lattice Substitute (A2, "", "-", STEP1) replaces the last space with the "-" Find ("-", Step2) finds the location of the last space Right (A2, LEN (A2) –step3) returns the string after the last space if (ISERROR ("", A2), A2, STEP4) The string does not handle without spaces, avoid the result of #value!

Using the above function, we look at a * asterisk position in the lookup string and remove

So, to find the asterisk and the question mark in the string, you must precede it with a symbol that you can find.

In the table above, each cell contains two * asterisks and contains three sets of numbers, but the length of each group is different, some are short. The question now is, how do you remove each number separated by the asterisk in each cell?

I. Analysis of the problem

To remove the three sets of numbers separated by two * asterisks, the key question is how to know and get the two * asterisks where they are located in the string.
Once we know the location of the two asterisk, we can use the Intercept function to separate the three sets of numbers.

Second, get two * asterisk position
Gets the position of a character in the string, you can use the search and SEARCHB functions to find the location.
The syntax for two functions is the same as follows:
SEARCH (Find_text,within_text,start_num)
SEARCHB (Find_text,within_text,start_num)
However, the difference between the two functions is that the former when looking, the length of a Chinese characters as a character, the latter is a byte to find, a Chinese character when two bytes, so, the length of a Chinese character is 2. In general, you should use the search function to find in a string that is mixed with a special character in Chinese and English.
Translate the above two functions into Chinese with the following syntax:
Search (the character to be found, the string containing the character, starting from the first position in the string)
SEARCHB (the character to be found, the string containing the character, starting from the first position in the string)

With the functional basics and theoretical instructions above, let's start looking at the two asterisk locations.

① the position of the first * asterisk
The position of the first asterisk, in 5th place, is obtained by using the function =search ("~*", A1).

② the position of the second * asterisk
The second asterisk position to find, it is more difficult, the formula a little tongue twister feeling.
In short, the formula is as follows: =

The code is as follows	Copy Code
SEARCH ("~*", A1,search ("~*", A1) +1)

Did you see that? The above formula is more complicated, but it is very well understood.
By using the formula =search ("~*", A1) to get the first position, then add 1 to the first position, starting from this position, you can find the position of the second asterisk, so the formula is the function that is slightly around the mouth in the figure above.

Third, extract numbers from a string with an asterisk
With a one or two base, now, we should be extracting the numbers.
① the number formula on the left of the first star in advance

The code is as follows	Copy Code
=left (A1,searchb ("~*", A1)-1)

② the formula to extract the middle number

The code is as follows	Copy Code
=mid (A1,searchb ("~*", A1) +1, (SEARCHB ("~*", A1,searchb ("~*", A1) +1))-searchb ("~*", A1)-1)