Excel Help
Wed, 01/25/2012 - 3:24pm
![]() |
![]() 15 years
|
I have long expressed as
dddmmss in column A
902246
and
lat
ddmmss in Column B
411844
What formula do I use to convert for Garmin CSV?
![]() |
![]() 15 years
|
I have long expressed as
dddmmss in column A
902246
and
lat
ddmmss in Column B
411844
What formula do I use to convert for Garmin CSV?
how many?
Bob,
How many do you need to convert? If it's just a couple, toodle over to www.itouchmap.com.
Illiterate? Write for free help.
Check out this MS KB article
http://support.microsoft.com/kb/213449
Striving to make the NYC Metro area project the best.
spokybob wrote: I have long
I have long expressed as
dddmmss in column A
902246
and
lat
ddmmss in Column B
411844
What formula do I use to convert for Garmin CSV?
Decimal value = Degrees + (Minutes/60) + (Seconds/3600)
Garmin Nuvi 2450
I could write a program...
Striving to make the NYC Metro area project the best.
TV Xmitters
I see people trying to get TV reception in campgrounds. I have a file that shows
long lat CallSign network,Ch#,RF channel,City, st.
I am in over my head on how to get the dddmmss into dd.xxxx.
The formula I have is dd:mm:ss*24. I can't get the numbers separated. I have 1822 locations with long/lat entries.
I was ok except some longitudes are xx. and others are xxx.
1490LMT 1450LMT 295w
Microsoft Help
I'm no Excel expert by far but here is a link to Microsoft Excel help for this.
http://support.microsoft.com/kb/213449
Hope this helps.
Harley BOOM GTS, Zumo 665, (2) Nuvi 765Ts, 1450LMT, 1350LM & others | 2019 Harley Ultra Limited Shrine - Peace Officer Dark Blue
give me a sample
One or 2 lines oughta do
Striving to make the NYC Metro area project the best.
separating the
I have long expressed as
dddmmss in column A
902246
and
lat
ddmmss in Column B
411844
What formula do I use to convert for Garmin CSV?
Decimal value = Degrees + (Minutes/60) + (Seconds/3600)
This formula is perfect. The problem is separating the DD, MM, SS. Are all of the lon's 7 digits and all of the lat's 6 digits in length?
Harley BOOM GTS, Zumo 665, (2) Nuvi 765Ts, 1450LMT, 1350LM & others | 2019 Harley Ultra Limited Shrine - Peace Officer Dark Blue
Excel formula
Try this to separate degrees, minutes, seconds
=ROUND(INT(A1/10000)+INT(MOD(A1,10000)/100)/60+MOD(A1,100)/3600,5)
Assumes value to convert is in cell A1.
Nuvi 350
Excel 2010
This worked for me
The 1st line is A1, B1
the 2nd line is A2, B2
453245 45.65833333
1083456 108.7222222
B1 =LEFT(A1,2)+(MID(A1,3,2)/60)+(RIGHT(A1,2)/360)
B2 =LEFT(A2,3)+(MID(A2,4,2)/60)+(RIGHT(A2,2)/360)
EDIT - This assumes all lat are 6 digit and all lon are 7 digit
Harley BOOM GTS, Zumo 665, (2) Nuvi 765Ts, 1450LMT, 1350LM & others | 2019 Harley Ultra Limited Shrine - Peace Officer Dark Blue
can we assume
Can we assume the seconds don't have decimals? An example would be 33 46 24.8. If that's the case another way would be to use the string manipulation functions - left, right and mid.
Degrees can be broken out with the formula if(iserr(find(".",cell,1)),left(cell,len(cell)-4),left(cell,len(cell)-6))
That would look at the value in cell, see if there is a period or decimal and if there isn't one take everything except the last 4 positions. If there is a decimal it strips the last 6.
To extract the minutes the formula would be the same up to left, where you would use mid(cell,len(cell)-4,2) and mid(cell,len(cell)-6,2)
Seconds are extracted by replacing the mid function with right(cell,2) and right(cell,4)
So really, there are multiple ways to skin that cat. Using the string functions requires you use 4 cells for each part of a coordinate, 3 for the functions and one with the source data.
Illiterate? Write for free help.
My Problem is
Excel will not let me force all long entries to be seven digits. They can be displayed in the column as seven digits, but the selected cell always drops the leading zero on longitudes that are less than 100 degrees.
1490LMT 1450LMT 295w
example
A1 735910 B1 404454
A47 1171705 B47 1171705
1490LMT 1450LMT 295w
Sample please?
Show me some data (please)...
My bad. I was expecting dd.mm'ss"
Striving to make the NYC Metro area project the best.
so far so good
Try this to separate degrees, minutes, seconds
=ROUND(INT(A1/10000)+INT(MOD(A1,10000)/100)/60+MOD(A1,100)/3600,5)
Assumes value to convert is in cell A1.
I'll do the whole column now.
1490LMT 1450LMT 295w
I have a nice file now.
=ROUND(INT(A1/10000)+INT(MOD(A1,10000)/100)/60+MOD(A1,100)/3600,5)
Worked great.
Then I changed the longitude values to minus.
Thanks to all and especially Ms Ass Kiss
1490LMT 1450LMT 295w
Nice!
=ROUND(INT(A1/10000)+INT(MOD(A1,10000)/100)/60+MOD(A1,100)/3600,5)
Worked great.
Then I changed the longitude values to minus.
Thanks to all and especially Ms Ass Kiss
Striving to make the NYC Metro area project the best.