Thanks:  0
Likes:  0

# Thread: Combining values in three columns into one

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

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

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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•