Forumla to decifer digits other than letters and numbers

T

trafficzombie

Board Regular
Joined
Sep 9, 2011
Messages
63
I am writing a code and have hit a wall with it

I am trying to write a code to decifer if there is anything other than a letter and or number in a cell, the digits are UK post codes (zip codes)

For example

M1 3RW
M26 4GD
TN10 3ER

The first part of the post code can be anything from 2 to 4 digits, the 2nd part always being 3.

What I am having sometimes is there are other digits added by mistake or not space between the two parts, which causes issues when uploading the data and brings an error report on the IT system I use.

For example

M13RW
M26 4GD*
TN10 3ER&

I have written the following code (based on the data being in cell A1 - and running down the A column)

=LEFT(TRIM(A1),3)&" "&MID(TRIM(SUBSTITUTE(A1," ","")),4,3)

But what is happening is with the above post codes above with this forumla is as follows:

M13 RW
M26 4GD (this one is perfect)
TN1 03E

Hope this makes sense and if anyone can help, that would be great

Thanks in advance
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Press ALT + F11 to open the Visual Basic Editor. Select Module from the Insert menu and paste into the white space on the right

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 - 2
        If Mid(strInput, Len(strInput) - 2, 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 - 2
        If Mid(strInput, Len(strInput) - 2, 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

Press ALT + Q to close the VBE.

Then you can use a formula like

=ISUKPOSTCODE(A1)
 
Upvote 0
that has work perfectly for postcodes in lower case and the ones without a space

The issue is with ones that have an extra digit

For example M1 3XR*

Is is saying this is valid, which as you can see is not the case
 
Upvote 0
With a small adjustment and very lightly tested.

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
strInput = Left(strInput, 7)
'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 = strInput
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 - 2
        If Mid(strInput, Len(strInput) - 2, 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 - 2
        If Mid(strInput, Len(strInput) - 2, 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
 
Upvote 0
That has unfortunatly made all of them invalid :(

Ones with out a space in upper case it is saying invalid - the ones in lower case without a space it is changing them to upper case and putting a space in.

Nghtmare this one I think
 
Upvote 0
Try this

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 And Len(strInput) <= 7 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
        ElseIf Len(strInput) > 7 Then
        IsUKPostCode = "Too Long"
        Exit Function
        End If
        'Check for and correct substituted O (alpha) for 0 (numeric) at position len - 2
        If Mid(strInput, Len(strInput) - 2, 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 - 2
        If Mid(strInput, Len(strInput) - 2, 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

AndyTampa
  • Solved
How can I validate all characters in a string are only letters or numbers
2
Replies
11
Views
6K
AndyTampa
AndyTampa
A
  • Question Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
2K
akshay27
A
Mister_J
  • Question Question
Help fixing a Multi-Filtering Macro That Requires Letters and Numbers.
Replies
5
Views
911
Fluff
Fluff
M
  • Question Question
More complicated than a simple, if greater than/but less than, formula
Replies
2
Views
1K
MarketTimer
M
J
  • Question Question
Finding number after a specific character search
Replies
0
Views
279
johnhuckv
J

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,920
Latest member
jaspers

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top