Latitude and Longitude Value Formatting with 3 Splitters ( : : ) Only.

[mba_110]

I have following data for Latitude and Longitude with its coordinates and want to transform the value (LT and LG) as per below.

for example

S.no >383> 19.159314 to 21 : 8 : N and 77.313188 to 75 : 16 : E rather than a long coordinate numbers.

These numbers are extracted from Geography from excel formula.

Mirza Bahadur Ali.xlsx
	B	C	D	E	F	G	H
5	S.no	District	State	Latitude	LT	Longitude	LG
Country & City

Mirza Bahadur Ali.xlsx
	B	C	D	E	F	G	H
388	383	Nanded	MAHARASHTRA	19.15931400		77.31318800
Country & City

Cell Formulas
Range	Formula
B388	B388	=B387+1

I hope there will be some method or formatting to achieve this results.

 

[Anthony47]

Maybe in F2:

=LET(rLat,E2,cLat,ABS(rLat),INT(cLat)&":"&ROUND(MOD(cLat,1)*60,0)&IF(rLat>0,":N",":S"))

and in H2

=LET(rLat,G2,cLat,ABS(rLat),INT(cLat)&":"&ROUND(MOD(cLat,1)*60,0)&IF(rLat>0,":E",":W"))

 

[mba_110]

Maybe in F2:

=LET(rLat,E2,cLat,ABS(rLat),INT(cLat)&":"&ROUND(MOD(cLat,1)*60,0)&IF(rLat>0,":N",":S"))

and in H2

=LET(rLat,G2,cLat,ABS(rLat),INT(cLat)&":"&ROUND(MOD(cLat,1)*60,0)&IF(rLat>0,":E",":W"))

Thanks for you answer its working, but i have to modify the method based on conditions and calculation of birth as per Astrology, not sure about the coordinates given by Geographic of excel where it exactly point on city, anyhow i will manage to do that but your formula will surely help me to get the desire format for coordinates.