Function Problems

How to Preserve Date Values When Using TRIM in Excel

Amos GikundaAmos Gikunda Nov 10, 2026 869 views

Question details

The user needs to use the TRIM function on cells containing dates without losing the underlying date serial numbers and visual formatting.

How to Preserve Date Values When Using TRIM in Excel
Product
Excel
Device & OS
not provided
Scenario
Cleaning up extra spaces in date cells using the TRIM function, which unexpectedly alters the date display.
Observed behavior
The TRIM function outputs the result as plain text, causing the date to lose its formatting and preventing it from being treated as a numeric date value.
Before you start

Before applying the formula, ensure you have an empty column adjacent to your date data where you can input the new TRIM formula and safely apply the required date formatting.

Solution 1Recommended

Use the Double Unary Operator with the TRIM Function

This method converts the text output of the TRIM function back into a numeric value, allowing Excel to recognize and format it as a proper date.

Excel stores dates as sequential serial numbers. Because the TRIM function is designed as a text function, it turns this numeric serial number into a plain text string. By adding a double unary operator (--) before the TRIM function, you force Excel to convert that text string back into a standard numeric value.

1
Enter the modified TRIM formula

Select an empty cell next to your date. Type the formula =--TRIM(A1), making sure to replace 'A1' with the actual cell reference of your date.

2
Apply the formula

Press the Enter key. The result will likely appear as a 5-digit number, which is Excel's underlying date serial number.

3
Format the cell as a Date

Select the cell containing your new formula. Navigate to the 'Home' tab on the top ribbon, locate the 'Number Format' dropdown menu, and select either 'Short Date' or 'Long Date' to restore the visual date appearance.

Use the Double Unary Operator with the TRIM Function
Alternative Function: If you prefer not to use the double unary operator (--), you can achieve the exact same result by wrapping the TRIM function inside a VALUE function, like this: =VALUE(TRIM(A1)).
Easily Manage Data with WPS Spreadsheet

Clean and Format Dates Seamlessly in WPS Spreadsheet

WPS Spreadsheet handles text and date functions exactly like Microsoft Excel, making complex data cleaning tasks—such as trimming extra spaces from dates—straightforward and effortless.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing the dates you need to clean.
  2. 2. Apply the formula: In a blank column, type =--TRIM(A1) or =VALUE(TRIM(A1)) and press Enter.
  3. 3. Access cell formatting: Right-click the newly populated cell and select 'Format Cells' from the context menu.
  4. 4. Set the Date format: Navigate to the 'Number' tab, click on 'Date' in the category list, choose your preferred date style, and click 'OK'.
Fully compatible with Microsoft Excel formulas, including TRIM, VALUE, and date serial numbers.Quickly apply Date formatting with an intuitive and familiar user interface.Lightweight software that processes large datasets without lag or freezing.Free basic features for everyday data management and spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the TRIM function change my date into a 5-digit number?

Excel stores all dates as 5-digit serial numbers starting from January 1, 1900. When you apply text functions like TRIM or remove a cell's formatting, Excel simply reveals this underlying raw serial number instead of the formatted date.

Can I use the VALUE function instead of the double minus sign?

Yes. The formula =VALUE(TRIM(A1)) works exactly the same way as =--TRIM(A1). Both methods instruct the spreadsheet to convert the resulting text string back into a numeric value that can be formatted as a date.

How do I remove extra spaces from a date without using any formulas?

You can use the Find and Replace feature. Press Ctrl + H to open the dialog box, type a single space in the 'Find what' field, leave the 'Replace with' field completely empty, and click 'Replace All'. This directly removes spaces from the original cells while preserving their native date format.