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.

--
Illiterate? Write for free help.

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

smile

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

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

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 smile

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

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