Hi Ron,

wow thats impressive!

Thank you for opening up the world of RegEx to me, I can certainly utilise
it and create little programs that will help my team greatly!

Still couldn't get Ardus's code to compile though. I even used createObject
and linked to vbscript, and it then recognised the RegExp command, but it
still didn't like the MatchCollection command. Any Ideas?

Thank you for your help.

Bhupinder

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 01:51:01 -0700, Bhupinder Rayat
wrote:

Hi All,

I have text similar to the following, which is an in-house language.
----------------------------------------------
field b831 B831 write AASLQ0300000l;

PCPACIMTAAABl [B29 ]

field B7 b7 ;
field a8 @latestdate("PCP2EHSEAAAAh", jEnd);
field b8 @if(a8 <jStart, @latest("PCP2EHSEAAAAh", jEnd),
@avg("PCP2EHSEAAAAh", jStart, jEnd));
--------------------------------------------------------

I want to look through this text and copy out any 13 character codes that
are present (e.g. "PCPACIMTAAABl" ,2 "PCP2EHSEAAAAh").

These codes all share the following characteristics,

1) they are all 13 characters in length
2) the last character in the code is always either a "l", "h" or a "c".
3) they contrain no spaces
4) the first 12 characters are always in CAPS (followed by a lower "l", "h"
or a "c".

Any help at all will be much appreciated. If you need more explanation,
please ask and I will be happily explain things further.

Regards,

Bhupinder.

This can be done fairly simply with regular expressions. If your total string
lengths are <= 255, then download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr/

Use the formula:

=REGEX.MID(A1,"\b\w{12}(1|h|c)\b")

If there could be multiple matching codes in the same string, there is an
optional third argument in the function to select the instance (and it returns
a null string if there is none).

If your string lengths might be greater than 255, you can use Microsoft
VBScript Regular Expressions and write a UDF to do the same thing.


--ron