Formatting Issues

How to Use Excel Custom Number Formats and Convert Text to Numbers

Aamir Naveed Akram Oct 9, 2026 870 views

Question details

The user wants to understand how Excel custom number formats (such as 0, #, and underscores) work and how to resolve calculation errors caused by numbers being formatted or stored as text.

Understanding Excel Custom Number Formats and Fixing Data Type Issues

Product: Excel
Device & OS: not provided
Scenario: Applying custom numerical formats to spreadsheet data and troubleshooting addition formulas that fail because some values are treated as text.
Observed behavior: Custom formats successfully change the visual display of values, but some numbers still behave as text during arithmetic calculations, preventing formulas from working correctly.

Before you start

Before modifying your number formats, click on the problematic cells and check the formula bar to verify if the underlying data is stored as text or a real number. You can also use the ISNUMBER function to test this.

Solution 1Recommended

Convert Text to Numbers using the Text to Columns Feature

Use this method to quickly convert an entire column of text-formatted numbers into usable numeric values so your calculations work correctly.

Often, data imported from other sources or typed manually comes in as text. Excel cannot calculate text, so it must be converted to true numbers.

Select the target column

Highlight the column containing the text-formatted numbers that are causing calculation errors in your formulas.

Open the Text to Columns wizard

Navigate to the Data tab on the top ribbon menu and click on the 'Text to Columns' button.

Complete the conversion

In the wizard window that appears, simply click 'Finish' without changing any default settings. Excel will automatically evaluate and convert the text values into real numbers.

Convert Text to Numbers using the Text to Columns Feature

Quick Verification: You can verify the conversion was successful by highlighting the cells and checking if the 'Sum' automatically appears in the bottom status bar.

Solution 2

Fix Calculation Errors using Paste Special Multiply

This is a fast workaround to force Excel to evaluate text cells as numbers by multiplying them by the number 1.

Solution 3

Master Custom Number Format Placeholders (0, #, and _)

Learn how to use formatting codes to change the visual display of numbers and handle spacing without altering the underlying values.

Efficient Spreadsheet Tool

Format and Calculate Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides intuitive cell formatting and powerful data conversion tools. Easily customize number displays and seamlessly convert imported text data into workable numbers to ensure your formulas calculate perfectly.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your unformatted or text-formatted data.
2. Apply custom formats: Select your cells, right-click to choose 'Format Cells', and use the Custom category to apply formatting codes like 0, #, or placeholders.
3. Convert data types: To fix text-formatted numbers, select the data and use the 'Text to Columns' tool located under the Data tab to instantly convert them into calculable numeric values.

100% compatible with Microsoft Excel custom formatting codes and logic.One-click 'Text to Columns' feature to effortlessly fix calculation errors.Built-in smart tags to detect and instantly convert numbers stored as text.Free, lightweight, and features a familiar user interface with no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my numbers look correct but fail when I try to add them in Excel formulas?

Even if a cell is visually formatted as a number, the underlying data might be stored as text, which often happens when importing data from other software. Excel formulas cannot perform arithmetic on text. You must convert these cells into true numeric values using tools like Text to Columns or the VALUE function.

What is the difference between the 0 and # placeholders in custom formats?

The '0' placeholder forces Excel to display a digit even if it is an insignificant zero (for example, formatting '5' with the code '00' will display '05'). The '#' placeholder, on the other hand, only displays significant digits and drops any extra zeros.

How do I use an underscore in an Excel custom number format?

An underscore (_) in a custom format tells Excel to leave a blank space equal to the width of the character immediately following it. For example, typing '_)' in the format code leaves a space exactly the width of a closing parenthesis. This is frequently used in accounting formats to neatly align positive numbers with negative numbers that are enclosed in parentheses.

Document & File Issues