EXTRACT DATA FROM FIELDS 1

[Neak1]

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!!!!

 

[MMAshwin]

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$,&quot; &quot;+statec(temp)+&quot; &quot <> 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 &quot;No State Found&quot;

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)=&quot;NY&quot;
Statename(2)=&quot;New York&quot;
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

 

[prex]

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

 

[MMAshwin]

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

 

[don01]

heres a thought that might be off the mark.
I'm in Australia and all our &quot;zip&quot; 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