Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 6372 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted (edited)

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 by Guest
Posted

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.

Posted

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.

Posted

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)

)

This topic is 6372 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.