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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.