Postcodes - Return only TEXT before numbers

[PG2015]

Hi,

I have a number of short postcodes ie

S43
GU10
ME8

etc etc

I want a run query to return only first characters before the numbers ie..
S
GU
ME

Any ideas please? :banghead:

 

[vbaInet]

Write a function to loop through each letter. Some aircode:

strPostCode = "S43"

For x = 1 to Len(strPostCode)
    If Mid(...) Like "[a-zA-Z]" Then
        ... concatenate here ...
    Else
        Exit For
    End If
Next

You can also use the Asc() function in place of the Like check.

Then use that function in your query.

 

[namliam]

Use the instr and Left (possibly mid) functions...

I.e. instr(1,"ABCDEFGH...", left("GU10",1) )

Will return 7 indicating a character there

Or you can reverse it

i.e. instr(1,"12345...", left("GU10",1) )
Will return 0 indicating it isnt a number (thus has to be a character)

 

[PG2015]

Thanks everyone for your help..

I think I have cheated - not sure its good but seems to work

SHORTPOSTCODE: (Left([postcode],1) & IIf(IsNumeric(Right(Left([postcode],2),1))=False,Right(Left([postcode],2),1),""))

 

[vbaInet]

Or:

SHORTPOSTCODE: Left([Field], IIf(IsNumeric(Mid([Field], 2)), 1, 2))

... however, I'm not sure if all UK postcodes (I'm guessing this is what you're working with) have either 1 or 2 letters in the first part that was why I mentioned looping through.