Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Combining values in three columns into one

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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