How to Concatenate Cells and keeping Date Format in Excel
This post will guide you how to concatenate cells and keeping data format in Excel. How do I concatenate cells and retain the certain Date format with a formula in Excel 2010/2013/2016.
Concatenate Cells and Keeping Date Format
Assuming that you have a list of data in range A1:B4, which contain product names and dates. And you want to concatenate product name and date value into one cell and keeping a certain date format. How to do it. You can use a formula based on the CONCATENATE function and the TEXT function to achieve the result. Like this:
=CONCATENATE(A1,” “,TEXT(B1,”m/dd/yyyy”))
Type this formula into a blank cell and press Enter key. And then drag the AutoFill handle over to other cells to apply this formula.
Note:
The “Format Code” can be used in the excel Text function are shown in the below table.
| Format Code | Description | Examples |
| 0 | only display digits in its place
#.00 – Forces the function to display two decimal places |
=Text(34.234,”$##.00″)
result: $34.23 |
| # | Display the placeholder | =Text(4.527,”#.##)
result: 4.53 |
| . | the position of Decimal Point | =Text(342.2,”0.00″)
result: 342.20 |
| d | Day of the month or day of week d- one or two digit number (1-31) dd- two digit number (01-31) ddd-abbreviated day of week (Mon to Sun) dddd-full name of day of week(Monday to Sunnday) |
=Text(TODAY(),”DDDD”)
result: Monday |
| m | The Month of the Year
m- one or two digit number mm-two digit number mmm-abbreviated month(Jan to Dec) mmmm-full name of month(January to December)) |
=Text(TODAY(),”MM/DD/YY”)
result:11/06/17 |
| y | year yy-two digit representation of year(e.g.01,17) yyyy-four digit representation of year(e.g. 2001,2017) |
=Text(TODAY(),”MM/DD/YY”)
result:11/06/17 |
| h | Hour
h-one or two digit number (e.g. 1,23) hh-two digit number (e.g. 01,23) |
=Text(14:16,”hh:mm”)
result: 14:16 |
| m | Minute
m-one or two digit representation (e.g. 1,59) mm-two digit representation (e.g. 01,59) |
=Text(14:16,”hh:mm”)
result: 14:16 |
| s | Second
s-one or two digit representation (e.g. 1,59) ss=two digit representation (e.g. 01,59) |
Related Functions
-
- Excel Text function
The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
- Excel Text function
