×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

EXTRACT DATA FROM FIELDS

EXTRACT DATA FROM FIELDS

EXTRACT DATA FROM FIELDS

(OP)
I HAVE A FIELD CALLED ADDRESS.  THIS ADDRESS FIELD HAS CITY, STATE AND ZIP.  THIS FIELD IS SEPARATED BY SPACES.  WHAT I NEED TO DO IS EXTRACT THE STATE WHICH IS IN THE MIDDLE OF THE FIELD.  IT IS A 2 POSITION STATE CODE.

PLEASE HELP!!!!

RE: EXTRACT DATA FROM FIELDS

Hi,

You don't say what program you are getting the data from.
However lets say you have the data in a string called
Address$ and Address$="Long Beach CA 1234".

There are a number of ways to extract the state address, some more difficult than others.

Because the length of the city is not known the start position of the state code is also not known. You can also not search for the preceeding space because in the example shown Long Beach has a preceeding space before the word Beach.
However if the zip code must be entered and its length is fixed then this can be used.

State$=mid$(Address$,len (address$)-(ZL+3),2)

where ZL is the zip code length and the 3 because thats the precceding space before the zip code + the two character state code.
But like I said you need to make sure the zip code is entered and the code is the correct length.

A more elaborate method is the following

First dimension an array with all the state codes

Dim States(No of States) as string

Then define each element of the array with the state code

Statec(1) = "NY"
statec(2) = "CA"
etc

then have a for and next loop to search the string for the precence of the state code eg.

State$="No State Found"
StateN$="No State Found"
For temp = 1 to No of States
if instr(Address$," "+statec(temp)+" ") <> 0 then
State$=statec(temp)
StateN$=statename$(temp)    'See text Below
Endif
Next Temp

This searches the string for <SPACE>State Code<SPACE> for each of the states you have in your array. If no state is found then State$ equals "No State Found"

This method has the advantage of being independent of the state codes position in the string and the length of the string. and providing you have no 2 letter cities in the states it should work.
Another advantage is you coud have another array with the expaned names in it for the same postion eg
Statecode(2)="NY"
Statename(2)="New York"
That way after you have found the state you could see the full name of the state in the application you need to extract the data for.

I hope this makes sense to you.
If not and you have any problems then feel free to reply.

Regards

RE: EXTRACT DATA FROM FIELDS

There's a simpler approach using the function split that returns an array with all the words in your string.
Once you get this you can check that the last one is a correct representation of a ZIP (to ensure a ZIP indeed exists in the string: not necessary if you are sure of this), then take the preceding one, check for consistency with a state code (2 uppercode letters) and return it.


prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design

RE: EXTRACT DATA FROM FIELDS

Hi,

Ref the previous post and the split function.
What if someone types in the zip code with a space in the middle?.
There would have to be some check to stop this other wise the precceding one item in the array would produce the first half of the zip code.

regards

RE: EXTRACT DATA FROM FIELDS

heres a thought that might be off the mark.
I'm in Australia and all our "zip" codes (called post codes here) are state orientated.
ie if it is in Victoria the first digit is 3xxx and so on. Does this hold true for US zip codes?? You could then, using laffalots neat finder then check against a portion of the zip code. (not many extra lines)

might not work but it's a thought.
regards
Don

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources