Extracting unknown number of characters 2

[smibarb]

I am trying to clean up a database where many components of address were entered into one field. The field contains CITY PROVINCE POSTAL CODE. The city may be any number of characters, the province is always 2 letters and the postal code 6. I was able to break out the province and postal code using Left function. But I cannot figure out how to extract the City, which may be any number of characters. I want to "trim off" the last 10 characters of the field, but the functions that I know about always give me what was "trimmed off" not what was "left behind".

 

[lbass]

Are the fields separated by spaces? If so, you could use:

stringvar x := split({table.string}," ");
if ubound(x) = 5 then
x[1]+" "+x[2]+" "+ x[3] else
if ubound(x) = 4 then
x[1]+" "+x[2] else
if ubound(x) = 3 then
x[1]

This would allow for up to three components to the city name, e.g., West New Bedford.

-LB

 

[smibarb]

This sounds exactly what I need, but I am not an advanced user and this function is new to me. I cannot find further explanation in Crystal Help. When I attempt to create the formula field using:

stringvar x := split({Submitter_Rev_1.Address3}," ");
if ubound(x) = 5 then
x[1]+" "+x[2]+" "+ x[3] else
if ubound(x) = 4 then
x[1]+" "+x[2] else
if ubound(x) = 3 then
x[1]

and check the formula, I get a message "This array must be subscripted. For example: Array ."

This is Greek to me.

 

[dgillz]

Change the first line of code to:

stringvar [red]array[/red] x:=split({Submitter_Rev_1.Address3}," ");

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[smibarb]

Perfect! Thanks to both of you.

 

[dgillz]

I love it when LB does 99% of the work and I get the star

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Sorry, that's what happens when you don't double check answers! Thanks, Don, for catching that.

-LB