"The twists and turns" Excel skill hundred example 14. Example of a classic combination of string intercept functions

Raw data:

We can see that these URLs have different lengths, different suffixes, different hierarchies, and how quickly we get the following results:

Logical explanation: Just get the Last "/" and "." The number between, if the middle level of the same, we can use the way of the symbol, if the suffix is the same or the number is the same length, we can ctrl+f replace, and then intercept fixed length, if the data are many, can not guarantee that the median number is the same length, how should we handle it?

The following function is used to handle the string:

1. First find the last "/" to intercept

=mid (A2,find ("*", Substitute (A2, "/", "*", (LEN (A2)-len (Substitute (A2, "/", ""))))) +1,len (A2))

2. This can be done at this point, and here again we use the function for processing:

=mid (B2,1,find (".", B2)-1)

So we get the data we want, just to be able to see the way it works, and a function when it's actually used.

=mid (Mid (A2,find ("*", Substitute (A2, "/", "*", (LEN (A2)-len (Substitute (A2, "/", "")))) +1,len (A2)), 1,find (".", MID (A2 , FIND ("*", Substitute (A2, "/", "*", (LEN (A2)-len (Substitute (A2, "/", "")))))) (+1,len (A2))))-1)

The character handlers in Excel also have find (), right (), left (), Mid (), Search (), substitute (), etc., which can be combined according to the actual need.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

"The twists and turns" Excel skill hundred example 14. Example of a classic combination of string intercept functions