Formatting Issues

How to Remove Day or Date Text Across Multiple Excel Worksheets

Rana Garcia Oct 1, 2026 868 views

Question details

The user needs to remove specific day or date text from cells across multiple worksheets (e.g., 24 sheets) while ensuring that the distinct date values on each individual worksheet are preserved.

How to Remove Day or Date Text Across Multiple Excel Worksheets

Product: Spreadsheet Software
Device & OS: not provided
Scenario: Editing date labels across a workbook containing multiple sheets, where the user wants to strip out particular text and later append a specific year (like 2024).
Observed behavior: When worksheets are grouped, editing a cell causes the exact same text to be mirrored across all selected sheets, inadvertently overwriting the unique data contained in each individual sheet.

Before you start

Before making bulk changes across multiple worksheets, always save a backup copy of your workbook to prevent accidental data overwriting.

Solution 1Recommended

Ungroup Sheets and Use Find and Replace

Ungroup your worksheets to prevent data mirroring, then use the Find and Replace feature to safely remove the text across the entire workbook.

The primary reason data is overwritten across worksheets is that the sheets are grouped together. When sheets are grouped, any edit made to one sheet is identically applied to all of them. To safely remove text while preserving unique values, you must first ungroup the sheets and utilize the workbook-level Find and Replace tool.

Ungroup worksheets

Right-click any worksheet tab at the bottom of your screen and select 'Ungroup Sheets'. This ensures that any direct cell edits won't be copied to other sheets.

Open Find and Replace

Press 'Ctrl + H' on your keyboard to open the Find and Replace dialog box.

Enter the text to remove

In the 'Find what' field, type the specific 'Day/Date' text you wish to remove. Leave the 'Replace with' field completely blank.

Set scope to the entire workbook

Click the 'Options' button in the dialog box, then change the 'Within' dropdown menu from 'Sheet' to 'Workbook'.

Execute the replacement

Click 'Replace All'. This will delete the specified text across all sheets while leaving your unique dates intact. You can then manually add the year (e.g., 2024) to the cells if required.

Check Your Formatting: After removing the text, ensure your remaining cell values are recognized as dates by checking the Number Format dropdown in the Home tab.

Solution 2

Use a VBA Macro for Bulk Processing

Use a VBA script to automatically loop through and modify each worksheet individually, which avoids the grouping issue entirely.

Advanced Editing Tool

Easily Manage Bulk Data Across Worksheets with WPS Office

WPS Spreadsheet offers an intuitive and powerful Find and Replace feature that works seamlessly across multiple worksheets, allowing you to edit text strings without losing your unique cell data.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your multi-sheet spreadsheet file.
2. Access Find and Replace: Press 'Ctrl + H' to instantly open the Find and Replace tool.
3. Configure your search: Type the text you want to remove in the 'Find' box and leave the 'Replace' box empty.
4. Apply across all sheets: Click 'Options', set the search scope to 'Workbook', and hit 'Replace All'.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Advanced Find and Replace options for easy workbook-wide bulk editing.Free, lightweight, and features a familiar tabbed user interface.Supports complex VBA macros for advanced data processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why does editing one grouped sheet change the values on all sheets?

When worksheets are grouped, any data entry or edit made in the active sheet is automatically mirrored to the exact same cell in all grouped sheets. This overwrites unique data in those cells, which is why it is critical to ungroup sheets before making individual edits.

How do I quickly ungroup all worksheets?

Simply right-click any of the selected worksheet tabs at the bottom of your screen and choose 'Ungroup Sheets' from the context menu. Alternatively, clicking any unselected sheet tab will also ungroup them automatically.

Can I add a specific year to multiple cells after removing the day text?

Yes. After using Find and Replace to remove the unwanted text, you can either format the remaining data as Dates (and apply a custom format like 'mm/dd/yyyy') or use a formula in an adjacent column, such as `=[CellReference]&" 2024"`, to append the year.

Document & File Issues