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

RightString("A CA 96094", " ")

LeftString("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!!

[maximus]

can you do some thing write me an example like a string you have now and how it will look after formatting. It is always helpful to tell what you want to do rather then how you want to do it.

[UCBFireCenter]

Played around with regrex a bit. Either the package is not importing...or I don't know. It bombed.

So my current idea is to use the Split() function. First try with the "<space>" delimiter...

Public Function Zipedit() As String
'-- Examine the ZIP1 column and split it according to spaces

Dim ZIP1 As String

Dim fields() As String

' Split the string at the spaces and add each field
fields() = Split(ZIP1, " ", -1)
For i = 0 To UBound(fields)
List1.AddItem Trim$(fields(i))
Next
End Function

Another idea I had is use Replace() function to replace the spaces with "-" so I'm only working with one variable.

[UCBFireCenter]

A little fiddling with RIGHT LEFT and FIND solved this particular problem in excel...and just went forward with that.

=RIGHT(LEFT(A1,IF(ISERROR(FIND("-",A1,1)),100,FIND("-",A1,1)) -1),5)

However, I'm frustrated with this sub. It seems....I'm not initializing things right (when I try to run it it keeps asking what parameter to pass).

Any thoughts...anyone...? Wouldn't mind knowing what I'm missing out on...

[maximus]

I have done something for you. I have attached a simple mdb file with a single form called form10. Type the Code in TextBox0 and Click on the Command Button and the formatted strin is displayed in textBox3. The Code modifies string as follows:

A CA 96094=96094
95607-0628=95607


if this solves your problem mark the thread solved.

[maximus]

is you problem solved if yes mark this thread solved.

[UCBFireCenter]

is you problem solved if yes mark this thread solved.

It's solved...but I don't exactly understand what the solution was. I see you got the code running...but it seems different then what I expected it would be (also, I noticed it only works in "Form" mode).

Can I ask how did you do this?

[maximus]

It can work in a Form, Report, Query you name it if you know how to do it. Well the solution is simple. I do a loop that basically scans all the alphabets/numbers from the left one at a time. a counter Variable records the exact number of alphabets it scans. the loop is set to break of when it comes across a "-" so if a loop breaks before it scans all the characters is one condition and if it scans all is our another condition.

You have two type of numbers

1) A CA 96094
2) 95607-0628

In the first case you have not used "-" therefore the code completes the scanning cycle.
In the second case it breaks of after the 6th character.

Now comes a simple if block combined with Right$ and Left$ to get the desired effect.

I am attaching another mdb and I have written a function StringConvert for you. You can use it anywhere like SeperateString([PhoneNumber]) see query String Adjusted.

I felt that you had almost given up that somebody could solve your problem.

Mark this thread solved if this solves your problem. attaching a mdb

[maximus]

check the function

[UCBFireCenter]

Wow...it took me a bit (er...forever) to get it...but I think I do. Thanks for the help!