# Excel Help

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?

--
1490LMT 1450LMT 295w

### how many?

 Bob, How many do you need to convert? If it's just a couple, toodle over to www.itouchmap.com.
--

### Check out this MS KB article

--
Striving to make the NYC Metro area project the best.

### spokybob wrote: I have long

 spokybob wrote: 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

 jfossy wrote: spokybob wrote: 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.
--

### 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

--
Striving to make the NYC Metro area project the best.

### so far so good

 msaskis wrote: 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!

 spokybob wrote: =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.