field 1= 1
i want it to concatenate to 1/1/2020
How do I do that?
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 as below screenshot shown. In this article we come up with several workarounds to concatenate data with keeping date or number formatting in Excel.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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.
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?
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.
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:
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.
(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:
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:
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.
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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Select the range you will concatenate, and click Kutools > Combine.
2. In the opening Combine Columns or Rows dialog box, please do as follows:
(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.