Validating account numbers using IF, OR, etc

[jaredino]

Hi,

I have a list of account numbers in the following variations:

AAA123456ISA
AAA123456ISAB
AAA123456IF
AAA123456IFB
AAA123456SIPP
AAA123456SIPPB
BBB123456ISA
BBB123456ISAB
BBB123456IF
BBB123456IFB
BBB123456SIPP
BBB123456SIPPB

Basically, I need to be able to extract account numbers from my computer system and check that they have been set up in the correct format. Since there are thousands of account numbers I obviously wanted an automated process to do this. At the moment, I am using the following formula:

=IF(OR(RIGHT(A2, 3)="ISA", RIGHT(A2, 4)="ISAB", RIGHT(A2, 2)="IF", RIGHT(A2, 3)="IFB", RIGHT(A2, 4)="SIPP", RIGHT(A2, 5)="SIPPB"), "", "ERROR")

which is rather clumsy. It is basically checking the last 3,4, or 5 digits of the account number and returning an error message if an incorrect format has been input. However this doesn't check the prefix of the account number (AAA or BBB) or if there are any other errors, for example:

AAA123456-ISA (note, the dash)

Please can anyone help me with a more thorough and efficient way of validating these account numbers?

Thanks

[Leith Ross]

Hello jaredino,

Welcome to the Forum!

The macro below will validate the account numbers based on your formula and if the prefix is three capital letters. There is button on the worksheet of the attached workbook to run the macro on a sample of the data you posted.

Experience has taught me this macro will need to modified to work with your original. People seldom post real account numbers or provide a large enough sample for testing. Give it try and let me know the results.

Sub CheckAccountNumbers()

' Thread:  http://www.excelforum.com/excel-programming/798347-validating-account-numbers-using-if-or-etc.html
' Poster:  jaredino
' Written: October 26, 2011
' Author:  Leith Ross

    Dim Cell As Range
    Dim RegExp As Object
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Sheet1")
        Set Rng = Wks.Range("A2")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
            
            Rng.Offset(0, 1).ClearContents
            
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Pattern = "[A-Z]{3}\d+(IF[B]?|ISA[B]?|SIPP[B]?)$"
            
            For Each Cell In Rng
                If RegExp.Test(Cell) = False Then Cell.Offset(0, 1) = "Invalid"
            Next Cell
            
End Sub

[Alf]

Question, should not the line

RegExp.Pattern = "[A-Z]{3}\d+(IF[b]?|ISA[b]?|SIPP[b]?)$"

include IFB, ISAB and SIPPB ?

Alf

[Leith Ross]

Hello Alf,

Here is how the pattern breaks down:

[A-Z]{3} Must start with any combination of 3 capital letters.

\d+ There must be 1 or more digits following the letters.

(IF[b]?|ISA[b]?|SIPP[b]?)$
This checks if the following combinations of letters follow the digits.

IF, IFB, ISA, ISAB, ISPP, ISPPB

[snb]

You didn't specify exactly the requirements for the accountnumbers.
So I deduced:

Sub snb()
 sn = Columns(1).SpecialCells(2).Resize(, 2)
    
 For j = 1 To UBound(sn)
  sn(j, 2) = IIf(Left(sn(j, 1), 3) = UCase(Left(sn(j, 1), 3)) And Mid(sn(j, 1), 4, 6) = Format(Val(Mid(sn(j, 1), 4, 6)), "0") And InStr("|ISAB|IFB|SIPPB", Mid(sn(j, 1), 10)) > 0, "", "Err")
    Next
    
 Columns(1).SpecialCells(2).Resize(, 2) = sn
End Sub

[Alf]

Hi Leith

Thanks for the break down of you macro.

Alf

[jaredino]

Genius - thanks, that's great. The only thing it doesn't check for is if there are 6 numbers between the prefix and suffix. Is this also possible?

Also I'd like to add other columns to my spreadsheet but at the moment the macro is affecting all columns - how do I just have it run on the account number column? I really need to learn how to use Macros!!

Thanks again for you help.

[Leith Ross]

Hello jaredino,

It is an easy change to make. Only one line needs to modified. The line with the Regular Expression Pattern:

  ' Use this pattern if the account number must have 6 digits - no more, no less
    RegExp.Pattern = "[A-Z]{3}\d{6}(IF[b]?|ISA[b]?|SIPP[b]?)$"

  ' Use this pattern if the account number must have a minimum and maximum number of digits
  ' This example is for 3 to 6 digits.
    RegExp.Pattern = "[A-Z]{3}\d{3,6}(IF[b]?|ISA[b]?|SIPP[b]?)$"

[jaredino]

Thanks, that's great!