ExcelBanter - Find a specific formatted string in a cell

On Wed, 16 Apr 2008 12:57:01 -0700, KCK wrote:

I am trying to determine if a cell has a text string with a specific format.
Example: SO#8058416, PO#LW10179863 Rev2.a
I need to determine if the cell has a string with the format AlphaApha
followed immediately by 8 digits. In the example this would find the
LW10179863.
Thanks for any help you can provide.

Fairly simple with a UDF.

So long as your strings will never be longer than 255 characters, I would
recommend you download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/

Then use this "regular expression" formula:

=REGEX.MID(A1,"[A-Za-z]{2}\d{8}")

The Pattern in the function does the extraction:
----------------------------
Extract 2 leters followed by 8 digits

[A-Za-z]{2}\d{8}

Match a single character present in the list below «[A-Za-z]{2}»
Exactly 2 times «{2}»
A character in the range between “A” and “Z” «A-Z» or
A character in the range between “a” and “z” «a-z»
Match a single digit 0..9 «\d{8}»
Exactly 8 times «{8}»
--------------------------------------
--ron