Combining values in three columns into one

[Rick22]

Hi, I need help in combining three values in three columns into one number in a single column.

I'm using coordinate system in Deg Min Sec.XX format in columns A B and C.
I need to see the whole figure as one coordinate and real number in the DDMMSS.XX format.

Thanks

[Mark W.]

See the Excel Help Index topic for the CONCATENATE worksheet function or use the concatenation operator (&).

[Rick22]

Thanks that seems to work.

However, I run into another problem.
Minutes of latitute or longitude are 0-60 value but if I have a 4 minute in column B, I get 74423.22
I would like for each field to automatically put a 0 in front of the min or secs as necessary to fill in the format of DD:MM:SS
so I'd get 0740423.22 then finally I'll show it as 074Degreesymbol 04' 23.22"

[Mark W.]

On 2002-04-17 12:44, Rick22 wrote:
Thanks that seems to work.

However, I run into another problem.
Minutes of latitute or longitude are 0-60 value but if I have a 4 minute in column B, I get 74423.22
I would like for each field to automatically put a 0 in front of the min or secs as necessary to fill in the format of DD:MM:SS
so I'd get 0740423.22 then finally I'll show it as 074Degreesymbol 04' 23.22"

Consider this...

if A1 contains 4 then =TEXT(A1,"00") produces "04"

if A1 contains 45 then =TEXT(A1,"00") produces "45"

Do you see how you might make use of this?

So if A1:C1 contains {74,4,23.22} then the formula...

=TEXT(A1,"000°")&TEXT(B1," 00'")&TEXT(C1," #0.0#")&""""

...produces 074° 04' 23.22"

[ This Message was edited by: Mark W. on 2002-04-17 13:02 ]

[IML]

also of possible interest:
=char(176) will return a degree sign
=char(34) will return a double quote