Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find UK Postcode Formats in Excel Data 2

Status
Not open for further replies.
idbr

idbr

MIS
May 1, 2003
247
GB
Hi,

I'm guessing the # wildcard won't work in the Excel find method? It won't for me anyway...

I need to be able to look for a uk postcode format, which could be any of the following:

??# #??
?# #??
??## #??
?## #??
??#? #??

I'd like to do it this way:

Code: 
Public Function test_find(strWhat As String) As Boolean

Dim c
Dim strFirstAddress As String
Dim i As Integer

i = 0

    Set c = Cells.Find(what:=strWhat, _
                LookIn:=xlValues)

    If Not c Is Nothing Then
    
        i = i + 1
        
        strFirstAddress = c.Address
        
        Do While Not ((c Is Nothing And c.Address <> strFirstAddress) Or i > 2)
        
            Set c = Cells.FindNext(c)
            
            i = i + 1
            
        Loop
        
    End If

        If i >= 2 Then
        
            test_find = True
            
            Exit Function
        
        Else
        
            test_find = False
            
            Exit Function
            
        End If


End Function

The purpose is that I have a large number of Excel workbooks to investigate, to see if they hold address data. I plan to loop through the books, run the above, feeding the function one of the above formats in sequence. If I find more than three instances in total of any format set a flag to true:

Code: 
Function Got_Postcodes() As Boolean

Dim a as integer

a = 0

a = test_find(??# #??)

if a < 3 Then 
   a = a + test_find("?# #??")
end if

if a < 3 Then 
   a = a + test_find("??## #??")
end if

etc...

If a > 2 Then

   Got_Postcodes = True

Else

   Got_Postcodes = False

End If

End Function

It won't work though :-(

Any advice would be appreciated.

Thanks, Iain
 
Sort by date Sort by votes
Ok, posted the old code. Here's the correct version:

Code: 
Public Function test_find(strWhat As String) As Integer

Dim c
Dim strFirstAddress As String
Dim i As Integer

i = 0

    Set c = Cells.Find(what:=strWhat, _
                LookIn:=xlValues)

    If Not c Is Nothing Then
    
        i = i + 1
        
        strFirstAddress = c.Address
        
        Do While Not ((c Is Nothing And c.Address <> strFirstAddress) Or i > 2)
        
            Set c = Cells.FindNext(c)
            
            i = i + 1
            
        Loop
        
    End If

        If i >= 3 Then
        
            test_find = i
            
            Exit Function
        
        Else
        
            test_find = i
            
            Exit Function
            
        End If

End Function
 
Upvote 0 Downvote
If you know anything about Regular Expressions (RegEx) in VBScript, there is a pattern you can use to test for postcodes...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
I'm afraid regular expressions is all Greek to me Geoff... Any advice on a starting point??

Tx, Iain
 
Upvote 0 Downvote
Not too hot on 'em myself but got this pattern from someone a while ago. The folowing code will spit out a YES/NO answer for if it has found a UK postcode:

Code: 
Function UKPostCode(str As String)
Dim RgExp As Variant, objMatches As Variant, obj As Variant
Dim i As Long
Set RgExp = CreateObject("VBScript.RegExp")
UKPostCode = ""
With RgExp
    .Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
                & "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
                & "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
                & "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
                & "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
                & "\d(?:\d|[A-Z])? \d[A-Z]{2})"

    If .test(str) = True Then
        UKPostCode = "YES"
    Else
        UKPostCode = "NO"
    End If
End With
End Function

Essentially, you should be able to loop this, testing bits of data until you get a YES result - in which case a postcode has been found

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Thanks Geoff, after a lot of 'bashing head against wall' managed to get this working nicely in a test process. I'm still working on a full routine, will post that here when done in case it helps anyone.

Thanks again, Iain

 
Upvote 0 Downvote
That would be great if you could - be a very useful code snippet I would've thought - looking forward to seeing what you end up with....[thumbsup]

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Upvote 0 Downvote
Interesting, have just found a glitch myself, MBT18 9SJ evaluates to true.

Might see if I can amend the regex so it works every time...

Will get back to you in a couple of centuries, lol :-D

Iain
 
Upvote 0 Downvote
Hi there,

Didn't manage to get the regex thing sorted, being honest, I'm still not sure I understand exactly what's going on in there %-).

I have though come up with a validation function that also suggests a replacment for some common postcode entry errors, so am posting here in case anyone finds it useful. I've also popped a copy into the FAQ section.

Code: 
Public Function IsUKPostCode(strInput As String)

'Uses a regular expression to validate the format of a postcode.
'May require WindowsScripting 5.6 - downloadable from microsoft.com

Dim RgExp As Variant

'Create the regular expression object
Set RgExp = CreateObject("VBScript.RegExp")

'Clear the function value
IsUKPostCode = ""

'Check we have value to test
If strInput = "" Then

    IsUKPostCode = "Not Supplied"
    
    Exit Function

End If

    'This is the ridiculously complex expression that validates the postcode
    RgExp.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
                & "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
                & "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
                & "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
                & "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
                & "\d(?:\d|[A-Z])? \d[A-Z]{2})"
    
    'Does the fed in string match the pattern?
    If RgExp.test(strInput) = True Then
    
        IsUKPostCode = "Valid"
        
    Else
    
        '------------------------------
        'Try to make a correct postcode
        '------------------------------
         
        'Despace & uppercase
        strInput = UCase(Replace(strInput, " ", ""))
         
        'Clean out any redundant characters - whilst most of these don't make sense
        'I've seen them all in postcodes before!
        strInput = Replace(strInput, "_", "")
        strInput = Replace(strInput, ",", "")
        strInput = Replace(strInput, "+", "")
        strInput = Replace(strInput, "-", "")
        strInput = Replace(strInput, ":", "")
        strInput = Replace(strInput, "=", "")
        strInput = Replace(strInput, "/", "")
        strInput = Replace(strInput, "*", "")
        strInput = Replace(strInput, "?", "")
         
        '---------------------------------------------------------------------------
        'Check the string length again to make sure we've not got a "???" type entry
        '---------------------------------------------------------------------------
        
        If Len(strInput) = 0 Then
        
            IsUKPostCode = "Not Supplied"
            Exit Function
            
        ElseIf IsNumeric(strInput) Then
        
            IsUKPostCode = "All Numbers"
            Exit Function
            
        ElseIf Len(strInput) < 6 Then
        
            IsUKPostCode = "Too Short"
            Exit Function
                    
        End If
         
        'Check for and correct substituted O (alpha) for 0 (numeric) at position len - 3
        If Mid(strInput, Len(strInput) - 3, 1) = "O" Then strInput = _
        Left(strInput, Len(strInput) - 3) & "0" & Right(strInput, 2)
        
        'Check for and correct substituted 0 (numeric) for O (alpha) at position 1 or 2
        If Mid(strInput, 2, 1) = "0" Then strInput = _
        Left(strInput, 1) & "O" & Right(strInput, Len(strInput) - 2)
        
        If Left(strInput, 1) = "0" Then strInput = _
        "O" & Right(strInput, Len(strInput) - 1)
        
        'Check for and correct substituted lowercase l for 1 at position len - 3
        If Mid(strInput, Len(strInput) - 3, 1) = "l" Then strInput = _
        Left(strInput, Len(strInput) - 3) & "1" & Right(strInput, 2)
        
        'Check for and correct substituted lowercase l for 1 at position 3
        If Mid(strInput, 3, 1) = "l" Then strInput = _
        Left(strInput, 2) & "1" & Right(strInput, Len(strInput) - 3)
        
        'Check for and correct substituted S for 5 at position len - 3
        If Mid(strInput, Len(strInput) - 3, 1) = "S" Then strInput = _
        Left(strInput, Len(strInput) - 3) & "5" & Right(strInput, 2)
         
        'Two possible lengths for a valid UK postcode
        Select Case Len(strInput)
                 
        Case 6
             
            If RgExp.test(Left(strInput, 3) & " " & Right(strInput, 3)) = True Then
                 
                 'Format should be ?## #?? or ??# #??
                 IsUKPostCode = Left(strInput, 3) & " " & Right(strInput, 3)
                 
            Else
            
            IsUKPostCode = "Invalid"
                 
            End If
         
        Case 7
        
             If RgExp.test(Left(strInput, 4) & " " & Right(strInput, 3)) = True Then
             
                 'Format is ??## #?? or ?#?# #??
                 IsUKPostCode = Left(strInput, 4) & " " & Right(strInput, 3)
                 
            Else
            
                IsUKPostCode = "Invalid"
         
            End If
            
        Case Else
        
            IsUKPostCode = "Invalid"
             
        End Select
        
    End If
    


End Function

Enjoy, Iain
 
Upvote 0 Downvote
thanks for posting that

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
No probs Geoff, thanks for the help.

Just realised btw that this line needs substituting:

'Check for and correct substituted O (alpha) for 0 (numeric) at position len - 2[/color blue]
If Mid(strInput, Len(strInput) - 2[/color blue], 1) = "O" Then strInput = _
Left(strInput, Len(strInput) - 3) & "0" & Right(strInput, 2)

Regards, Iain
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Data Type in ADODB.Recordset
Replies
6
Views
1K
strongm
strongm
Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
1K
remeng
remeng
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
551
strongm
strongm
IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
512
Andrzejek
Andrzejek
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top