Understanding/using RegExp for string processing in VBA

[JackDanIce]

Hi,

Imported data (csv file) has a column of strings and I need to extract 11 characters after a particular value, as long as those 11 characters are alpha-numeric, i.e. A-Z, a-z and 0-9 (no special characters).

The string possibly contains an ISIN code value preceded by the term "ANY." (constant in every string).

Example with required extraction in blue:

RMDS_IDN_EM::IDN_RDF.ANY.AT000034269=TE.NaE

(I already have a check function to verify it's an ISIN and generate a 12th digit check digit)

However, the column can contain false ISIN values (in red) such as:

RMDS_IDN_EM::IDN_RDF.ANY.ITEEU3Y=TE.NaE

I can already extract the 11 characters using a combination of MID and FIND and currently my code eliminates any extraction that contains the character "=".


I'd like to learn how Reg Exp object can be used to extract this value, I suspect it will shorten the code as well for the matching part.

Can you suggest the required code please?

Thank you in advance,
Jack

Alternatively, every string contains one and only one instance of "=", so another test might be extract all the characters between "ANY." and "=" (discard row if length <> 11 or contains symbols not alpha-numeric)

 

[István Hirsch]

OK, thanks.