Extract letters only from Text Field 1

[rickyshah]

Hi All

I have Postcode as a text field and need only the first part of the postcode excluding the number (to later help link in with another table), but its a bit tricky as the first letters of a postcode can range from 1-2 characters:

eg:
Present: Desired Result:
W1V 9DE W
BS8 1EY BS
B2 1YR B
CV6 2EL CV
RH11 0PF RH
PA39 6HL PA

Any help would be most appreciated.

Thanks

 

[Remou]

Hi
Maybe:
[tt]ss = "BS8 1EY"
strPC = IIf(IsNumeric(Mid(ss, 2, 1)), Left(ss, 1), Left(ss, 2))[/tt]

 

[rickyshah]

This seems to have worked, thanks alot!

 

[rickyshah]

Hi All

The query has got a little trickier, what do I need to do if I need the following output from the postcode field. Basically need to add back the number but drop off any ending with letters

eg:
Present: Desired Result:
W1V 9DE W1
SW1E 1EY SW1
B2 1YR B2
CV6 2EL CV6
RH1A 0PF RH1
PA3B 6HL PA3

Thanks in advance!

 

[Remou]

Will the break point always be a space? If so:
[tt]strPC = Mid(ss, 1, InStr(ss, " "))[/tt]

 

[PHV]

And what about this ?
strPC = Trim(Left(ss, IIf(IsNumeric(Mid(ss, 2, 1), 2, 3)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

PHV
Very neat, as always. Is there a missing bracket?
strPC = Trim(Left(ss, IIf(IsNumeric(Mid(ss, 2, 1)), 2, 3)))

 

[PHV]

OOps, sorry for the typo. Good catch Remou !
strPC = Trim(Left(ss, IIf(IsNumeric(Mid(ss, 2, 1)[highlight])[/highlight], 2, 3)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886