Concatenate in Excel

System
System Posts: 100,534 🤖 Admin 🤖
edited 12:08PM in AAT member discussion
Hello

I am trying to copy the contents of two cells into one in Excel using the concatenate option. It's all going well except the date format. I have tried various formatting options for the date including text and general, but it always copies into the new cell in numerical form - eg 23/4/06 becomes 39856.

Can anyone tell me how to resolve this?

Thank you

Sheelagh

Comments

  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Concatenate in Excel

    What have you got the new cell formatted as?

    Surely it wants to be as a date - then you can decide which type ie 02/11/07 or 2/11/07 or even 11/02/07 etc.

    Sorry if I'm missing something here! :?
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Concatenate in Excel

    Hi
    Sorry, I didn't explain that very well, but your comment about formatting has prompted me to go back and try again.
    I have one column of dates, and a column containing text, and I need to merge them into one cell.
    I have now worked out that the resulting cell needs to be text, and the cell with the date needs to be formatted as a "text date".
    I am receiving the spreadsheet with the dates in a date format. This sounds really daft, but I can't work out how to change the dates to "text date format".
    The best I can do is insert a new column, format it as text, then retype all the dates into the new column. There surely must be a better way.

    Any further help greatfully received.



    Thank you for your help

    Sheelagh
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Concatenate in Excel

    You could hide the inserted column with text date format and then concatenate the hidden cells rather than the visible ones.

    You can reference the hidden column rather than manually type in the dates again.

    Would that work?
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Concatenate in Excel

    I've sorted this now, so here is the answer, in case anyone ever needs it.

    I was going about this the wrong way - I was trying to format cells A and B, then merge into C. What I need to do is leave the formatting in A and B alone, and format cell C.

    In this example, cell A1 contains a date in any date format, and cell B1 contains text.

    Cell C contains the formula
    =TEXT(A6,"dd/mm/yy")&B6


    Thank you for helping me with the thought process.

    Sheelagh

Categories

Privacy Policy