calulation question

[Ron F]

I need to extract the city portion out of an address text field. The field has data enters as such: 123 Main Street, Yourtown, NJ 07000. I was assuming the "Position" function would possibly be used with either LeftWords or Middle Words, but I cant seem to get it right. Does anyone have a solution that might help. Thanks, Ron

[MoonShadow]

You had the right idea. I used the commas to pull everything inbetween.

Let(

[

start = Position ( text; ", "; 1; 1 ) + 2 ;

end = Position ( text; ","; 1; 2 )

]

; Middle ( text; start; end - start )

)

BTW, never trust data extracts of this nature. Always visually scroll and verify the results. Because, when Users can enter something into one line, they can easily make mistakes. Example: If User had accidently typed two commas together after street (or there are two address lines before the city (with commas between each street line), the above calc will break.

[MoonShadow]

Okay. It might be safer to approach this from the right, ie, there may be greater chance that the State and Zip are consistent. This isn't as pretty but I believe it addresses the problem from the 'more consistent' side:

(valueless calculation has been removed). See below for new calculation.

Someone may pretty this up a bit, however ... I get the feeling it isn't optimum.

Update: Yeah, it isn't optimum because it's exactly the same as the above. Two commas in the street will still break it. The theory is correct (start from the right) ... but the reality is wrong.

[Ron F]

Works great thank you much.

Ron

[MoonShadow]

Ron, to start from the right I needed to identify how many total commas and then work backwards. This works no matter how many commas are in the line and also strips any leading or trailing spaces while still allowing two-word cities.

Let(

[

commas = PatternCount ( text; "," ) ;

start = Position ( text; ","; 1; commas - 1 ) + 1 ;

end = Position ( text; ","; 1; commas )

]

; Trim ( Middle ( text; start; end - start )

)

)