Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to concatenate keeping number/date formatting in Excel?

You may have noticed that when we apply the CONCATENATE function in Excel, the date format (2014-01-03) and number format such as currency format ($734.7), percentage format (48.9%), decimal places (24.5000), etc. are removed and show as general numbers in the concatenation results. In this article we come up with several workarounds to concatenate data with keeping date or number formatting in Excel.


Concatenate with keeping date format in Excel

This section is talking about how to keep date format in the CONCATENATE function in Excel.

1. Select a blank cell you will output the concatenation result, and enter the formula =CONCATENATE(TEXT(A2, "yyyy-mm-dd")," ", B2) ( A2 is the cell with date you will concatenate, and B2 is another cell you will concatenate) into it, and press the Enter key.
doc concatenate retain date number formatting 2

2. Keep selecting the concatenation result cell, and drag its AutoFill handle to the range as you need. And then you will see the specified cells are concatenated into one cell with keeping the date format. See screenshot:

Note: This formula =CONCATENATE(TEXT(A2, "yyyy-mm-dd")," ", B2) will show dates of any date format as yyyy-mm-dd, such as 2014-01-03. For retaining other date format in the CONCATENATE function, you can change the yyyy-mm-dd to the certain date format code as you need. How to get date/number format code?

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

Batch combine multiple cells/rows/columns without losing data and date/number format in Excel

Normally when merging cells with the Merge feature in Excel, it will remove all cell contents except the first cell content. However, with Kutools for Excel's Combine (Rows & Columns) utility, you can easily batch combine multiple cells/rows/columns without losing data. What's more, this utility also supports remaining original date format and number formats in the combination results. Full Feature Free Trial 30-day!

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Concatenate with keeping number (currency/percentage/decimal places) format in Excel

This section is talking about keeping number format, such as currency format, percentage format, decimal places of numbers, etc. in the CONCATENATE function in Excel.

Tip: If you are familiar with format codes of numbers that you will concatenate, please click here.

1. First of all, we need to get the format code of specified numbers you will concatenate: right click the number cell and select the Format Cells from the right-clicking menu. See screenshot:

2. In the opening Format Cells dialog box, go to the Number tab, click the Custom in the Category box, copy the format code in the Type box, and close the Format Cells dialog box. See screenshot:
doc concatenate retain date number formatting 5

3. Then we will convert the number you will concatenate into text string. Select a blank cell (Cell E2 in our example), enter the formula =TEXT(B2,"$#,##0.00_);($#,##0.00)") (B2 is the currency cell you will concatenate) into it, and press the Enter key.
doc concatenate retain date number formatting 6
Notes:
(1) In above format, $#,##0.00_);($#,##0.00) is the format code we copied in step 2. And you can change the format code to your copied one.
(2) Sometimes the format code of currency you copied may be very complicated which causes formula errors. If so, please simply the format code such as $#,##0.00_);($#,##0.00).

4. If there are several kinds of numbers you will concatenate, please repeat Step 1-3 to convert these numbers to text strings.

5. After converting numbers with special number formats to text string, we can concentrate them by entering formula =CONCATENATE(A2, " ",E2, " ", F2) (A2, E2, and F2 are cells we will concatenate) into a blank cell (Cell G1 in our example), and press the Enter key. See screenshot:
doc concatenate retain date number formatting 7

6. Select the range E2:G2 and then drag the AutoFill Handle to the range as you need. And then you will see each row has been concatenated with keeping all kinds of number formats. See screenshot:
doc concatenate retain date number formatting 8
Note: If you are familiar with the format codes of numbers you will concatenate, you can apply the formula =CONCATENATE(A2, " ",TEXT(B2,"$#,##0.00_);($#,##0.00)"), " ", TEXT(C2,"0.00%" )) and replace the format codes of $#,##0.00_);($#,##0.00) and 0.00% based on your requirement.

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

Concatenate with keeping date and number format with Kutools for Excel

Cannot remember format codes of dates and numbers and feel tired to copy them repeatedly? Kutools for Excel’s Combine utility can help you concatenate data with keeping all kinds date formats and number formats by several clicks in Excel.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 60-day, no credit card required! Get It Now

1. Select the range you will concatenate, and click Kutools > Combine.
doc concatenate retain date number formatting 9

2. In the opening Combine Columns or Rows dialog box, please do as follows:
doc concatenate retain date number formatting 10
(1) Check the Combine columns option in the To combine selected cells according to following options section;
(2) Check one separator option in the Specify a separator section (we check the Space option in our example);
(3) Specify the place you will put the concatenation result into. In our example we specify the Left Cell;
(4) In the Options section, we check the Delete contents of combined cells option;
(5) Check the Use formatted values option at the bottom of this dialog box.

3. Click the Ok button to applying this utility. And then you will see each row has been concatenated into their first cell with keeping all kinds of date formats and number formats. See screenshot:
doc concatenate retain date number formatting 12


Demo: concatenate keeping number/date format in Excel

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Armando · 3 months ago
    I have 789 records concatenated and I need to establish the frequency of the data, but I don't know how to get the class rank.
  • To post as a guest, your comment is unpublished.
    NYC74 · 4 months ago
    concatenate but keep long date?
    • To post as a guest, your comment is unpublished.
      kellytte · 4 months ago
      Hi NYC74,
      For example, you need to concatenate the cell values in Cell A1 and B1, and B1 contains the date you will show as long date format in the concatenation result. Now you can use this formula:
      =CONCATENATE(A1, " ", TEXT(B1,"dddd,mmmm dd, yyyy"))
  • To post as a guest, your comment is unpublished.
    mary florence · 6 months ago
    i am trying to concatenate three fields into a date field. i.e

    field 1= 1

    field 2=1

    field 3=2020

    i want it to concatenate to 1/1/2020

    How do I do that?
  • To post as a guest, your comment is unpublished.
    Susan P. Jett · 10 months ago
    Ok I am trying to Concatenate two cells from a different sheet and one is a date. When I concatenate it the new cell displays the date as numbers. I need it to display as a date. For example: I am trying to merge cell #1: November 5, 2017 with cell #2: Homecoming and what i get is something like: 447689 Homecoming. Remember I am getting the info from another sheet within my workbook, so here is my formula: =CONCATENATE('Home Page'!F3," ",'Home Page'!F6)
    Which gives me the 4447689 Homecoming but I want it to say: November 5, 2017 Homecoming. Can you tell me what I need to add to my formula?
    • To post as a guest, your comment is unpublished.
      kellytte · 9 months ago
      Hi Susan,

      Try this formula =CONCATENATE(TEXT('Home Page'!F3, "MMMM D, YYYY")," ", 'Home Page'!F6)
  • To post as a guest, your comment is unpublished.
    Eve Hermes · 1 years ago
    Hello. Can you help me? i've been in this trouble for some times.
    So i have "Oxford" in cell A1 and "02 January 2019" in cell B1 and i want to merge these two cells separated with a comma and single space. I've tried =A1&", "&text(B1,"[$-421]dd mmmm yyyy;@") and =concatenate(A1,", ",text(B1,"[$-421]dd mmmm yyyy;@")) but none of them worked. Do you have any idea what was wrong with my formula?
    Thanks alot!
    • To post as a guest, your comment is unpublished.
      kellytte · 11 months ago
      Hi Eve,
      Try these formulas:
      =A1&", "&TEXT(B1,"DD MMMM YYYY") OR =CONCATENATE(A1,", ",TEXT(B1,"DD MMMM YYYY"))
  • To post as a guest, your comment is unpublished.
    Kent · 1 years ago
    Exactly what I needed.
  • To post as a guest, your comment is unpublished.
    Beate · 2 years ago
    Perfect, exactly what I needed. Thank you for the excellent summary.
  • To post as a guest, your comment is unpublished.
    Rajesh · 2 years ago
    Thank you. Nice Article
  • To post as a guest, your comment is unpublished.
    Per · 2 years ago
    Hi,

    The date get messed up for me. What do i do wrong?
    I have column A with a date and column B with an extra number that i want to add on the end.
    Ex. 2017-07-10-1
    I use this formula
    =CONCATENATE(TEXT(A2; "yyyy-mm-dd");"-"; B2)
    But the result comes out as
    yyyy-00-10-1

    Where do i go wrong?
    • To post as a guest, your comment is unpublished.
      Jony · 1 years ago
      probably you have a different language in excel, check the custom format cell to identify how is correct
      on my PC for example is dd-mm-jjjj not dd-mm-yyyy like in English
      • To post as a guest, your comment is unpublished.
        Thiru · 1 years ago
        use an operator after =CONCATENATE(TEXT(A2; "yyyy-mm-dd"))&"-"& B2&";"