specific decimal degrees conversion to deg/min/sec values

[mirmad]

Hi all,
I am looking for an advise for conversion of decimal degrees into a deg/min/sec format. Have searched the forum, but only partial solution was found.
Description:
I have 2 columns with decimal degrees and letter identifying North, South, East West:
Example:
Lat: 26.075000N
Long: 53.958333E

I would like to convert these two values into one value with this format:
260429N0535929E

So I need to have 2 digits for degrees in Latitude, 3 digits for degrees in longitude and always have 2 digits in minutes and seconds, i.e. if result is just 4 minutes, I need to receive 04 iso 4.
Further complication is that I need to merge the lat/long into one value - entries exist in 2 columns /lat and long/, but I want to have result in just one.

I know this could take more time to solve, while I am a beginner with excel it may take a week for me to find out. So if anyone has a formula for this already I will be really thankful for sharing it with me. Any help is very appreciated.

Thanks to all!

 

[XOR LX]

Hi.

Isn't 0.075° precisely 4' 30", not 4' 29"", as you give?

Also, I don't agree that 0.958333° is 59' 29"".

Can you clarify your calculations?

Regards

 

[mirmad]

Hi XOR LX,
thanks for replying, you are correct. I was using an online convertor from fcc.gov page and it gives me 26° 4' 29.9994" for the lat and 53° 57' 29.9988" for long. Sorry for not listing it, yes the result should be rounded to full seconds.
The result should be 260430N0535930E
Does that make sense for you?
Thanks again!

 

[XOR LX]

So can you provide half a dozen or so varied examples with expected results?

Regards

 

[mirmad]

Sure,
so
1. lat 38.045019N long 43.541567E should result in 380242N0433230E
2. lat 36.900675N long 45.711416E should result in 365402N0454241E
3. lat 30.453608N long 49.998076E should result in 302713N0495953E
4. lat 29.671982N long 50.434013E should result in 294019N0502602E
5. lat 32.404037N long 48.884150E should result in 322414N0485303E
6. lat 1.335482N long 7.253233E should result in 012008N0071512E

 

[XOR LX]

To test:

=TEXT(INT(LEFT(A1,LEN(A1)-1)),"00")&TEXT(INT(MOD(LEFT(A1,LEN(A1)-1),1)*60),"00")&TEXT(MOD(MOD(LEFT(A1,LEN(A1)-1),1)*60,1)*60,"00")&RIGHT(A1)&TEXT(INT(LEFT(B1,LEN(B1)-1)),"000")&TEXT(INT(MOD(LEFT(B1,LEN(B1)-1),1)*60),"00")&TEXT(MOD(MOD(LEFT(B1,LEN(B1)-1),1)*60,1)*60,"00")&RIGHT(B1)

Regards

 

[FormR]

Just another possible option:

Excel 2012
	A	B	C
1	Lat	long	Formula
2	38.045019N	43.541567E	380242N0433230E
3	36.900675N	45.711416E	365402N0454241E
4	30.453608N	49.998076E	302713N0495953E
5	29.671982N	50.434013E	294019N0502602E
6	32.404037N	48.884150E	322415N0485303E
7	1.335482N	7.253233E	012008N0071512E
Sheet1

Cell Formulas
Range	Formula
C2	=TEXT(LEFT(A2,LEN(A2)-1)/24,"[hh]mmss")&RIGHT(A2,1)&TEXT(LEFT(B2,LEN(B2)-1)/24,"\0[hh]mmss")&RIGHT(B2,1)

 

[mirmad]

Hi guys,
thanks so much to both of you, I really appreciate it. I will retest now.
have a great day, both of you!
Brgds

 

[FormR]

On second thoughts my suggestion should be amended to:

=TEXT(LEFT(A2,LEN(A2)-1)/24,"[hh]mmss")&RIGHT(A2,1)&TEXT(LEFT(B2,LEN(B2)-1)/24,"[hhh]mmss")&RIGHT(B2,1)

thanks so much to both of you, I really appreciate it. I will retest now.

Glad we could help, good luck with the testing and welcome to the forum