Function Problems

How to Combine a Date and Name in Excel Without Serial Numbers

Phi Hung VoPhi Hung Vo Oct 22, 2026 873 views

Question details

The user needs to merge a date cell and a name cell into a single cell without the date changing into a raw numerical format.

How to Combine a Date and Name in Excel Without Serial Numbers
Product
Excel
Device & OS
not provided
Scenario
Combining data from multiple cells containing different data types (dates and text) for reporting, labeling, or data organization.
Observed behavior
When concatenating a date and text, Excel automatically strips the date format and displays its underlying sequential serial number (e.g., 45000).
Before you start

Identify the exact cells containing your date and text values, and decide on the specific visual layout (like MM/DD/YYYY) you want the final date to display.

Solution 1Recommended

Use the Ampersand (&) Operator with the TEXT Function

This is the most direct and flexible method to concatenate text strings while forcing Excel to respect a specific date format.

By wrapping the cell reference containing the date in the TEXT function, you instruct Excel to convert the numerical serial date into a formatted text string before merging it with other text. The ampersand (&) acts as a bridge linking the values together.

1
Select the destination cell

Click on the blank cell where you want the combined date and name to appear.

2
Enter the combination formula

Type the formula `=TEXT(Q1, "MM/DD/YYYY") & " " & R1` into the formula bar. Replace Q1 with your date cell and R1 with your name cell.

3
Apply and autofill

Press Enter to execute the formula. Click the cell again, grab the small square fill handle in the bottom-right corner, and drag it down to apply the formula to the rest of your list.

Use the Ampersand (&) Operator with the TEXT Function
Customizing the Date Format: You can replace "MM/DD/YYYY" in the formula with any valid format code, such as "DD-MMM-YYYY" for a format like 01-Jan-2023.
Seamless Spreadsheet Editing

Easily Combine Data and Apply Formulas with WPS Spreadsheet

WPS Office provides a highly compatible, easy-to-use spreadsheet tool that fully supports standard formulas like TEXT and concatenation operators. You can efficiently manage data formatting without complex setups.

  1. 1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing your dates and names.
  2. 2. Select the target cell: Click on the cell where you want the combined text to be generated.
  3. 3. Input the TEXT formula: Enter the formula `=TEXT(A1, "MM/DD/YYYY") & " " & B1` and customize the cell references.
  4. 4. Merge the data: Press Enter to instantly merge the date and name without serial number errors.
100% compatible with Microsoft Excel formulas and date formatsBuilt-in comprehensive function library for text and date manipulationLightweight, fast, and completely free to use for daily tasks
QA img-9

Frequently Asked Questions

Why does Excel turn my date into a 5-digit number when combining cells?

Excel stores dates as sequential serial numbers for calculation purposes. When you combine a date with text, Excel strips the visual date formatting and displays the underlying serial number unless you explicitly use the TEXT function to lock the format.

How do I add a comma between the date and the name?

You can insert punctuation by placing it inside the quotation marks within your formula. To add a comma and a space, modify the joining segment of the formula to look like this: `=TEXT(Q1, "MM/DD/YYYY") & ", " & R1`.

Can I put the name before the date in the merged cell?

Yes, simply swap the order of the cell references in your formula. For example, to list the name first, use `=R1 & " - " & TEXT(Q1, "MM/DD/YYYY")`.