Looking for a better way to Parse out data from alphanumeric field

[Tokyo Alex]

Dear all,

I need to parse out the text from a mixed alphanumeric field.

I recieve ship-to addresses from a client, which do not always conform to
the standard I need. Specifically they occasionally drop the postcode. I
need to grab the first series of characters in the address, up to but not
including the first numeric. This will allow me to match the address to a
table of addresses and postcodes.

Fortunately, due to the Japanese way of writing addresses, I can guarantee
that it will be *near* the form "Prefecture City Town Division" (maybe with
or without spaces) where "Division" will be the first numeric.

I currently use the following code to achieve this:
'Code Fragment
For intPos = 1 To Len(strAddress)
If Mid(strAddress, intPos, 1) Like "*[1-9]*" Then
strAddress = Left(strAddress, intPos - 1)
Exit For
End If
Next
'End Code Fragment

I am wondering if there is a way to achieve this without looping through
each character? Something like InStr but using wildcards, for example.

If anyone has any ideas, I'd be very interested to hear them.

Thanks,
Alex.

 

[BruceM via AccessMonster.com]

It sounds as if Division is the first and last numeric value. If so, perhaps
you could test the rightmost character to see if it is numeric:

If IsNumeric(Right(strAddress,1) Then
...

Dear all,

I need to parse out the text from a mixed alphanumeric field.

I recieve ship-to addresses from a client, which do not always conform to
the standard I need. Specifically they occasionally drop the postcode. I
need to grab the first series of characters in the address, up to but not
including the first numeric. This will allow me to match the address to a
table of addresses and postcodes.

Fortunately, due to the Japanese way of writing addresses, I can guarantee
that it will be *near* the form "Prefecture City Town Division" (maybe with
or without spaces) where "Division" will be the first numeric.

I currently use the following code to achieve this:
'Code Fragment
For intPos = 1 To Len(strAddress)
If Mid(strAddress, intPos, 1) Like "*[1-9]*" Then
strAddress = Left(strAddress, intPos - 1)
Exit For
End If
Next
'End Code Fragment

I am wondering if there is a way to achieve this without looping through
each character? Something like InStr but using wildcards, for example.

If anyone has any ideas, I'd be very interested to hear them.

Thanks,
Alex.