Trying to do String Edits...using delimiters and regex...any advice???

[UCBFireCenter]

I have a bit of a parsing problem, that I wondered if you all had any advice on.

So through various work I have whittled down a series of (address) data columns into one column that looks like this:

A CA 96094
95607-0628
L CA 96058
96076-1305
A TX 96067
...

My end result needs to isolate the 5-digit zipcode out of this column. The required 5 digits are either 1) to the immediate right of all the letters OR 2) are the 5 digits to the immediate left of the "-" and the 4-digit postal code.

I've looked carefully at this...and two things jump out:

1) I have two ready made delimiters: the <blank space> (i.e. "") between the letters and the <dash> "-" between the postal code and the zipcode.

2) I'm still learning VB programming (taking an intro class in it atm...just started 2 weeks ago)...but I know I could: a) write either a regular expression (using the regex/LISP engine in excel), or b) I could mess around with LEFT and RIGHT and InStr functions in VB.

Problem is I haven't quite figured out how to set this all up. I've got the skeleton in my head

//to return string values
Public Function String

LeftString("A CA 96094", " ")

RightString("96076-1305", "-")

//Need to write functions to use the built in functions of LEFT RIGHT for the functions LeftString and RightString


/...

Yeah...any directional advice...am I somewhere close to understanding what I need to resolve this??

many thanks!!

[pbaldy]

Please don't post the same question more than once:

https://www.accessforums.net/program...vice-4787.html