Finding an ISIN code

[Nick28]

Hi

I am trying to write a code that can recognise an ISIN code, these are IDs for securities (like a SEDOL). They always start with 2 letters and are succeded by 10 numbers, the code loops through a block of text and the ISIN will be in a specific column. I just need to know how the code could identify an ISIN code in a specified column:

For intRow1 = intLastRow1 To 1 Step -1
Rows(intRow1).Select
If Cells(intRow1, 2).Value = "ISIN" Then
Cells(intRow1, 1).Select
Selection.EntireRow.Copy

Example of ISIN

FR0000000000
GB1111111111
XS2222222222

Any ideas

Thanks

 

[Andrew Poulsom]

Welcome to the Board.

Try:

If Cells(intRow1, 2).Value Like "[A-Z][A-Z]##########" Then

 

[Nick28]

Also should mention that the code needs to identify the extact type of the string (2 letters and 10 numbers like):

"[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

If it just looks for 12 characters then it will extract other unwanted infomation

Cheers

 

[Nick28]

Hi Andrew

just got your reply - will give a try - thanks alot

Nick

 

[Colin Legg]

I am trying to write a code that can recognise an ISIN code, these are IDs for securities (like a SEDOL). They always start with 2 letters and are succeded by 10 numbers, the code loops through a block of text and the ISIN will be in a specific column.

Just in case it may be a problem for you, I should mention that ISINs do not always follow that format.

They are always 12 characters in length. T
They always begin with two alphabetical characters, and the last character is always a number.

However, character positions 3 through to 11 are alphanumeric - they are not necessarily numbers.

 

[Nick28]

You are right - thanks for pointing this out. I guess I could have an issue with that

Cheers

 

[Nick28]

Andrew - tried the code and it worked (just need brackets)

If Cells(intRow1, 2).Value Like ("[A-Z][A-Z]##########") Then

But as Colin pointed out characters 3-11 are alphanumeric and 12 is numeric so tried this

If Cells(intRow1, 2).Value Like ("[A-Z][A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9]") Then

which worked great.

Thanks to all for your time and help on this.

Nick