Text retrieval problem between 2 commas

[Cindi]

Hello I have a pattern in some data I have

the example I have is

115 DESERT SHORES DR,THERMAL,CA,92274,[color:red]33.407763,[color:green]-116.036240

The problem I am having is because I am trying to retrieve the GPS coordinates but they are not always the same length.

I need to get a formula to get the latitude which is up to the second "," in from the right up to the next "," going to the field Latitude and I tried right word but the "-" makes it a problem. So I need to get everything up to the first "," from the right too.

I just am not experienced enough to do it.

Could someone help me.

Thanks

Cin

Edited August 13, 2007 by Guest

[aholtzapfel]

this is the lat where test::text is your data:

Middle(

test::text;

Position ( test::text ; ","; 1; 4 ) + 1 ;

Position ( test::text ; ","; 1;5 ) - (Position ( test::text ; ","; 1; 4 ) + 1)

)

this is the Long:

Middle(

test::text;

Position ( test::text ; ","; 1; 5 ) + 1 ;

Length ( test::text ) - (Position ( test::text ; ","; 1; 5 ) + 1)

)

checks for the "," the +1 is so you don't include it and it calculates the Number of Char's to pull.

[Brian C]

It is a mistake to think that the total number of commas will remain constant from one address to the next. The approach should grab the last two values in the string regardless of the number of commas.

Example data string:

115 DESERT SHORES DR,THERMAL,CA,92274,33.407763,-116.036240

To get: -116.036240

Use:

MiddleValues(

Substitute ( data::address ; "," ; "¶" ) ;

valuecount(Substitute ( data::address ; "," ; "¶" )) ;

1)

To get: 33.407763

Use:

MiddleValues(

Substitute ( data::address ; "," ; "¶" ) ;

valuecount(Substitute ( data::address ; "," ; "¶" )) - 1 ;

1)

This example converts the address into a value list by changing the commas into carriage returns and then just grabs one of the last 2 values in the value list.

[aholtzapfel]

If your concerned about extra comma's (in a comma delimited list this would be bad but hey)

try this for Lat (it counts the commas first and uses the last 2)

Middle(

test::text;

Position ( test::text ; ","; 1; PatternCount ( test::text ;"," )-1 ) + 1 ;

Position ( test::text ; ","; 1;PatternCount ( test::text ;"," ) ) - (Position ( test::text ; ","; 1; PatternCount ( test::text ;"," )-1 ) + 1)

)

this is the Long:

Middle(

test::text;

Position ( test::text ; ","; 1;PatternCount ( test::text ;"," ) ) + 1 ;

Length ( test::text ) - (Position ( test::text ; ","; 1; PatternCount ( test::text ;"," )) + 1)

)