Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    475
    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,894
    Thanks
    60
    Thanked 1,099 Times in 1,022 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,326
    Thanks
    3
    Thanked 215 Times in 198 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
    3,359
    Thanks
    162
    Thanked 623 Times in 591 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
    497
    Thanks
    11
    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
    3,359
    Thanks
    162
    Thanked 623 Times in 591 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
  •