On Thu, 25 Sep 2008 10:55:00 -0700, Rafael Azevedo
wrote:

I have a similar question, but what I am looking for is:

I have several cells with comments and I would like to extract only an
alphanumeric part number that follows this format (2letters followed by 5
numbers):
##AAAAA
Example: ST12345

The problem is that I am trying to use SEARCH function but if there is any
word that contains ST will give me the wrong MID point.
Example of a comment:
"Change Request Main_20847 - ST11223 Machine Holder/ Blade Tip Grinder"

I am looking for extracting only ST11223 from the text above, but the word
"Request" also contains ST on it.

I can do that in MS Access, but I can't figure it out in Excel.

Thanks
Rafael Azevedo

It can be done in native Excel, but the formula would be quite complex.

Being "lazy", I have installed on my system Longre's free morefunc.xll add-in
(from http://xcell05.free.fr/

and would then use this formula:

=REGEX.MID(A1,"[A-Z]{2}\d{5}")

The "regular expression" pattern will extract the first substring in A1 that
consists of two capital letters followed by 5 digits.

One could add other parameters; or require two specific letters, or whatever.

If the substring should always be separated from the rest of the string, then:

=REGEX.MID(A1,"\b[A-Z]{2}\d{5}\b") might be more robust.

If your strings might be longer than 255 characters, this approach won't work
but I would just write the function as a VBA routine.
--ron