Positioning in a String

[Guest]

I have a string field that contains "city, state zip" information. The
state is always 2 characters and the zip is always 5 characters so I can use
InstrRev and extract the state and zip. I know how to use Instr to find the
position of the first comma, what I need to know is how to specify positions
1 minus (comma position minus 1) in order to extract the city.

Would someone please let me know the command or set of commands to extract
characters from a string by specifying the character position?

thanks in advance for your help!

 

[Rick Brandt]

I have a string field that contains "city, state zip" information.
The state is always 2 characters and the zip is always 5 characters
so I can use InstrRev and extract the state and zip. I know how to
use Instr to find the position of the first comma, what I need to
know is how to specify positions 1 minus (comma position minus 1) in
order to extract the city.

Would someone please let me know the command or set of commands to
extract characters from a string by specifying the character position?

thanks in advance for your help!

=Left(FieldName, InStr(FieldName, ",") - 1)

 

[Larry Daugherty]

I typically start parsing and extracting from one end of the string or
the other. Once I have the rightmost 5 characters out to a Zip
variable I shorten the string variable by 6 (the 5 chars of zip and
one preceding space)...

The more certain you are that you know the exact format of the string
you can eliminate a lot of testing for matches or Not a Space... For
example; in the code below if you KNOW that there is always exactly
one space between the state abbreviation and the postal code then when
those two lines are done the rightmost two characters are the state
code. So it goes..

ZipVar=Right$(MyStringVariable), 5
MyStringVariable=Left$(Len(MyStringVariable)-6)

HTH