I have a table with a field labeled

[jack350]

I have a table with a field labeled "description". In this field are 10 digit numbers I want to extract and put in their own field in the same table so that I can link another table to it.

for example

field 1:
Sacramento, CA 95210C11

I want to extract just the "95210C11" part

and put it into

field 2:
95210C11

As you noticed, this string contains alpha-numeric characters.

Any tips on how extract just that string and put it into a new field? Thanks.

 

[Darrylles]

Hiya,

Yep, but you've first got to identify the 'rules' of the text string.

E.g. are your 10 digits ALWAYS (and I mean ALWAYS) at the end of the string?

If so, then the VB function RIGHT(yourstring,10) will give you your 10 digits.

You can use LEFT, RIGHT, MID etc etc, but you must first identify a 'pattern' that ALWAYS occurs - such as your 10 digits ALWAYS occuring at the end of the string.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[jack350]

The pattern is, the 10-digit string always appears AFTER the 2nd "space".

 

[chadrj]

It depends on whether the part your wanting to extract is the same length on every record and in the same location on every record. For example, in your sample data, you have an 8 characer string at the end of the field. To get the last 8 characters you could use the following formula in a query field2: =right([description],8). Let me know if my assumption is correct.

 

[jack350]

The pattern is, the string i want appears after the second space. There can be other characters after the 10-digit string I want, but those can be discarded.

for example,
field 1:
Sacramento, CA 95210C11 United States

I want to extract just the "95210C11" part

 

[mpastore]

If you want to play it safe, use space before the string as key:

?trim(mid("Sacramento, CA 95210C11",instrrev("Sacramento, CA 95210C11"," &quot))

or in a query:

select trim(mid([myaddress],instrrev([myaddress]," &quot)) as parse_addr from mytable


Mike Pastore

Hats off to (Roy) Harper

 

[mpastore]

Sorry, I had not read your lst post when I posetd my logic.

Try this. Paste this function into a module


Function getZip(ps As String) As String
' sample call:
' ?getZip("Sacramento, CA 95210C11 United States&quot
Dim sParm As String
ReDim aaa(1) As String
aaa = Split(ps, " &quot
getZip = aaa(2)
End Function


Now you can call it from within a query:

select getZip([myaddress]) as parse_addr from mytable

Of course the assumption here is that you have exactly 1 space between city, state, and zip.

Good luck




Mike Pastore

Hats off to (Roy) Harper