Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Concatenate Date and Text

    Hi There

    I am trying to combine data from in two different Columns into one. One column is a date and the other is text. When I do this using =CONCATENATE(F2," ",J2) the result is 42066 Smith when I want 03/03/2014 Smith

    What am I doing wrong?

    Kerry

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Dates are actually numbers so you need to convert the date to text before concatenation. You could do something like this:
    =concatenate(day(F2),"/",month(F2),"/",year(F2)," ",J2)

    cheers, Paul

  3. The Following 2 Users Say Thank You to Paul T For This Useful Post:

    danielr2 (2014-12-07),kerryg (2014-11-17)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can also use the TEXT function:
    =CONCATENATE(TEXT(F2,"dd/mm/yyyy")," ",J2)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    kerryg (2014-11-17)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    ..why waste time typing CONCATENATE when you can just use

    =TEXT(F2,"dd/mm/yyyy ")&J2

    Note: I have included the 'space' separator within the TEXT format to be used.
    For example, if you wanted the date, then a space, then the character ; and then another space and then the name, you would use
    =TEXT(F2,"dd/mm/yyyy ; ")&J2

    zeddy

  7. #5
    4 Star Lounger SpywareDr's Avatar
    Join Date
    Dec 2009
    Location
    Riviera Beach, Maryland, USA
    Posts
    490
    Thanks
    10
    Thanked 52 Times in 43 Posts
    Also

    =TEXT(F2,"mm/dd/yy")&" "&J2

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Also

    =TEXT(F2,"dddd mm/dd/yy ")&J2

    zeddy

  9. #7
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    One of my first attempts at contributing. Would a User Defined Function be appropriate?

    Public Function DText(dDate As Date, sRange As String) As String
    DText = dDate & " " & sRange
    End Function

    Enter the formula =DText(F2,J2) into the cell

    Alex

  10. The Following User Says Thank You to Alouso For This Useful Post:

    kerryg (2014-11-20)

Posting Permissions

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