Formatting Issues

How to Fix Excel Rejecting Custom Number Format with LY or SY

Maira Mehtab Sep 28, 2026 872 views

Question details

The user is attempting to create a custom number format to display measurements with linear yards or square yards, but the spreadsheet application rejects the input.

Product: Excel
Device & OS: not provided
Scenario: Applying custom number formats to numeric cells to visually append measurement units such as 'LY' (linear yards) or 'SY' (square yards).
Observed behavior: Excel rejects custom number formats like '0.00 LY' and '0.00 SY' because the letter 'Y' is inherently treated as a special character for date formatting.

Before you start

Before modifying your cell formats, ensure you have selected the correct range of cells containing the numeric values you wish to format.

Solution 1Recommended

Use Quotation Marks for Text Strings in Custom Formats

Enclose specific letters like 'LY' or 'SY' in double quotation marks so Excel recognizes them as standard text rather than specialized date formatting codes.

Spreadsheet applications reserve certain characters for specific formatting purposes. The letter 'Y' is recognized as part of a date format representing 'Year'. When you append 'LY' or 'SY' directly without quotes, the application attempts to read it as a date code, becomes confused by the context, and rejects the format entirely.

Select the target cells

Highlight the specific cells or column where you want to display the linear yards or square yards formatting.

Open the Format Cells dialog box

Right-click the selected cells and choose 'Format Cells' from the context menu, or press the 'Ctrl + 1' keyboard shortcut.

Navigate to the Custom category

Go to the 'Number' tab in the dialog box, and select 'Custom' from the list of categories on the left side.

Enter the corrected custom format

In the 'Type' text box, enter your desired number format with the text suffix enclosed in double quotation marks. For example, type exactly: 0.00 "LY" or 0.00 "SY".

Apply your new format

Click 'OK' to save the changes. Your numbers will now properly display the measurement units without triggering formatting errors.

Format Accepted: By wrapping the letters in quotation marks, the spreadsheet correctly identifies them as literal text strings, allowing the custom format to display seamlessly.

Advanced Cell Formatting with WPS Spreadsheet

Format Numbers Seamlessly in WPS Office

WPS Spreadsheet offers a highly compatible and intuitive interface for all your data formatting tasks. You can easily add custom text units like 'LY' or 'SY' to your numeric data using the exact same standard formatting rules you are already familiar with.

1. Open your workbook in WPS: Launch WPS Office and open the spreadsheet containing the measurements you want to format.
2. Highlight your data: Select the specific cells or entire columns that require the linear or square yards measurement units.
3. Access cell formatting options: Right-click the selection and choose 'Format Cells', then click on 'Custom' under the Number tab.
4. Apply the quoted text format: In the Type field, input 0.00 "LY" or 0.00 "SY" and click OK to instantly update your data display.

100% compatible with Microsoft Excel custom number formatting rulesIntuitive 'Format Cells' dialog for effortless data presentationLightweight application ensuring fast processing speed for large datasetsFree to download and use for all everyday spreadsheet calculations

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel reject certain letters in custom number formats?

Excel reserves specific letters as default system codes for date and time formats. For instance, 'Y' stands for Year, 'M' for Month or Minute, and 'D' for Day. If you use these letters without quotation marks in a standard number format, Excel attempts to apply date logic, which results in a rejected format.

Can I add custom text before a number in Excel?

Yes, you can add text before a number by placing the quoted text in front of the number placeholders in the custom format box. For example, typing "Total: " 0.00 will display 'Total: 15.50' in the cell.

Will adding text via custom formatting affect my spreadsheet formulas?

No. Custom number formats only alter how the data is visually presented on the screen. The underlying cell value remains strictly numeric, which means you can still perform normal mathematical calculations like SUM or AVERAGE on those formatted cells.

Document & File Issues