extract the first characters from a postcode

[Swifey]

Is there a formula to extract the fist characters from a postcode?

for example:

AL5 5SH = AL
B22 2SH = B
and so on....

Thanks for your help

 

[Joe4]

You can create your own function, something like this:

Function RemoveChar(myString As String) As String

    Dim i As Long
    Dim myLen As Long
    myLen = Len(myString)
    If myLen > 0 Then
        For i = 1 To myLen
            If IsNumeric(Mid(myString, i, 1)) Then Exit For
        Next i
        RemoveChar = Right(myString, myLen - i + 1)
    End If
                
End Function

 

[Swifey]

Hi I have created and added your code to a module - I then used the expression builder in my query to select the new function but I get an error message stating "Undefined function 'RemoveChar' in expression.

I have never used this method before can you please advise me were I am going wrong.

Many Thanks

 

[Norie]

Why not just try this?

FirstPart:Left([Postcode], (Instr([Postcode], " ")-1)

 

[Swifey]

Thanks for your input - Unfortunately its bringing back the numbers. I only want to pull back the first Characters from the post code ie

E1 1JP = E
EE1 1JP = EE
B1 1JP = B
BP11 1JP = BP

and so on..............

Many Thanks

 

[bat17]

Here is a modification of jmisky code

Function RemoveChar(myString As String) As String

    Dim i As Long
    Dim myLen As Long
    myLen = Len(myString)
    If myLen > 0 Then
        For i = 1 To myLen
            If IsNumeric(Mid(myString, i, 1)) Then Exit For
        Next i
        RemoveChar = Left(myString, i - 1)
    End If
                
End Function

The error message you got with the orignal try is becasue you saved the module with the same name as the function.
rename the module modRemoveChar or something


HTH

Peter

 

[Norie]

The error message you got with the orignal try is becasue you saved the module with the same name as the function.

It could also be because the code wasn't compiled especially if using later versions of Access.

To compile the code goto Debug when in module design view and select compile.

I've just spent about 10 minutes trying to get the function to work with Access 2000 and that was what was causing the problem.

 

[Swifey]

Absolutley fantastic I cant thank you enough.