Re: [libreoffice-users] Re: [Calc] Convert GPS coords from DMS to decimals?

=Wanted to see if could make a time formula that worked without have to reenter the data in 
column a to a time format. Uses regex to convert to time format. First set uses original 
combined value. Second set uses values split in B and C to make D and E simpler.
Just interesting. Know I sometimes have data that is created by other systems, and having 
to manually reenter data might be an issue?


Another option without having to reformat the original text in column A
assuming that it is create by something else in that format.
Re-entering all the data to time format might be an issue.


Time format is definitely the simplist option.


With Original type data in A41 and A420315'090E 4312'814N
0005'12O 4259'12N</div>
In Cells D41 to E42 Get the Values
D41: 3.2750;
E41: 43.4261111111111
D42: -0.086666666666667
E42:42.9866666666667


Formula D41: Uses REGEX to convert data to time format value, and if ends with other 
than E negates it.
=VALUE(REGEX(REGEX(LEFT(A41,FIND(,A41)-1),['],:,g),[&quot;NSEWO],,g)*24)*(IF(MID(A41,FIND(&quot;,A41)-1,1)=E,1,-1))


Forumula E41:
=VALUE(REGEX(REGEX(MID(A41,FIND(,A41)+1,20),['],:,g),;[NSEWO],,g)*24)*(IF(RIGHT(A41,1)=N,1,-1))


Same formula for D42 and E42


In A45 and A46 put same values as input.In Columns B and C split the values upFormula B45
=LEFT(A45,FIND(,A45)-1)Formula B46
=LEFT(A46,FIND(,A46)-1)Formula C45
=MID(A45,FIND(,A45)+1,20)Formula C46
=MID(A46,FIND(,A46)+1,20)
Formula in D45
=VALUE(REGEX(REGEX(B45,['],:uot;,g),[NSEWO],,guot;)*24)*(IF(RIGHT(B45,1)=E,1,-1))
Formula in E45
=VALUE(REGEX(REGEX(C45,['],:uot;,g),[NSEWO],,guot;)*24)*(IF(RIGHT(C45,1)=N,1,-1))




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy