Skip to main content

How to concatenate keeping number/date formatting in Excel?

Author: Kelly Last Modified: 2020-05-09

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. 


Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with 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 - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

1. Select the range you will concatenate, and click Kutools > Merge & SplitCombine Rows, Columns or Cells without Losing Data.
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: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (29)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Yes! It’s working now. Thanks so much Amanda Lee
This comment was minimized by the moderator on the site
Amanda Lee I’ve tried that, it’s not helping.
This comment was minimized by the moderator on the site
Hi Joseph,

Nothing happened after you ran the code? Did you check if the sheet name and range name are right?
I just tried and the code works properly on my sheet.

Amanda
This comment was minimized by the moderator on the site
Can anyone please check why this code is not working?

Sub MergeFormatCell()
'Updateby Extendoffice
Dim xSRg As Range
Dim xDRg As Range
Dim xRgEachRow As Range
Dim xRgEach As Range
Dim xRgVal As String
Dim I As Integer
Dim xRgLen As Integer
Dim xSRgRows As Integer
Dim xAddress As String
On Error Resume Next
'xAddress = ActiveWindow.RangeSelection.Address
'Set xSRg = Application.InputBox("Please select cell columns to concatenate:", "KuTools For Excel", xAddress, , , , , 8)
'If xSRg Is Nothing Then Exit Sub
'Set xDRg = Application.InputBox("Please select cells to output the result:", "KuTools For Excel", , , , , , 8)
'If xDRg Is Nothing Then Exit Sub
Set xSRg = ActiveWorkbook.Sheets("Person List").Range("J2:Z142").Value
xSRgRows = xSRg.Rows.Count
Set xDRg = ActiveWorkbook.Sheets("Person List").Range("G2:G125").Value
Set xDRg = xDRg(1)
For I = 1 To xSRgRows
xRgLen = 1
With xDRg.Offset(I - 1)
.Value = vbNullString
.ClearFormats
Set xRgEachRow = xSRg(1).Offset(I - 1).Resize(1, xSRg.Columns.Count)
For Each xRgEach In xRgEachRow
.Value = .Value & Trim(xRgEach.Value) & " "
Next
For Each xRgEach In xRgEachRow
xRgVal = xRgEach.Value
With .Characters(xRgLen, Len(Trim(xRgVal))).Font
.Name = xRgEach.Font.Name
.FontStyle = xRgEach.Font.FontStyle
.Size = xRgEach.Font.Size
.Strikethrough = xRgEach.Font.Strikethrough
.Superscript = xRgEach.Font.Superscript
.Subscript = xRgEach.Font.Subscript
.OutlineFont = xRgEach.Font.OutlineFont
.Shadow = xRgEach.Font.Shadow
.Underline = xRgEach.Font.Underline
.ColorIndex = xRgEach.Font.ColorIndex
End With
xRgLen = xRgLen + Len(Trim(xRgVal)) + 1
Next
End With
Next I
End Sub
This comment was minimized by the moderator on the site
Hi there,

In the snippet below, you should delete ".Value"
Set xSRg = ActiveWorkbook.Sheets("Person List").Range("J2:Z142").Value
xSRgRows = xSRg.Rows.Count
Set xDRg = ActiveWorkbook.Sheets("Person List").Range("G2:G125").Value

Amanda
This comment was minimized by the moderator on the site
I am trying to update the month of a new company excel sheet and this is the issues I came across (=CONCATENATE(“Month 1 -“,TEXT($H$19,”d mmm yyyy”)) how do fix this
This comment was minimized by the moderator on the site
Hi there, you used the wrong quotation marks, please copy the formula below: =CONCATENATE("Month 1 - ",TEXT($H$19,"d mmm yyyy"))
Amanda
This comment was minimized by the moderator on the site
I have name in A column and in B column i have given today formula -1 i.e yesterday dateKnow i want every date to update name from A column and yesterday date
This comment was minimized by the moderator on the site
means? can explain bit more?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations