Formatting Issues

How to Format Decimal Places in Excel Formulas That Add Text

Phi Hung Vo Oct 1, 2026 869 views

Question details

The user needs to control the number of decimal places displayed when a formula combines a calculated number with a text string.

How to Format Decimal Places in Excel Formulas That Add Text

Product: Excel
Device & OS: not provided
Scenario: Concatenating calculated numeric values with descriptive text strings (such as 'Hours' or '%') within a single cell formula.
Observed behavior: Standard cell number formatting fails to apply; the formula instead returns the calculated number with full mathematical precision alongside the text.

Before you start

Identify the specific formula where you are combining numbers and text, and decide exactly how many decimal places you want the final number to display.

Solution 1Recommended

Use the TEXT Function to Format Numbers Before Concatenation

Apply the TEXT function to define the numeric format explicitly within your formula before joining it with text.

When a number is concatenated with text in Excel, the entire result becomes a text string. Because it is no longer recognized as a standalone number, standard cell formatting rules from the ribbon menu do not apply.

The TEXT function resolves this by converting the mathematical calculation into text with a specific formatting structure before it gets concatenated with the rest of your text.

Locate and select the formula cell

Click on the cell containing your current concatenation formula (for example, =H95/I95&" Hours").

Wrap the calculation in the TEXT function

Edit the formula in the formula bar to enclose the numeric calculation within the TEXT function. Add your desired format code as the second argument. For one decimal place, use "0.0" (e.g., TEXT(H95/I95, "0.0")).

Append the text string

Use the ampersand (&) to join the newly formatted number with your descriptive text. The final formula should look like: =TEXT(H95/I95, "0.0")&" Hours".

Press Enter to apply

Hit the Enter key to save the formula. The cell will now display the calculated number with the exact requested decimal places alongside the text.

Use the TEXT Function to Format Numbers Before Concatenation

Common Format Codes: Use "0.00" for two decimal places (e.g., =TEXT((H95/G95)*100, "0.00")&" %") or "0" if you want to display a whole number with no decimals.

Manage Spreadsheets with Ease

Easily Format Complex Formulas Using WPS Spreadsheet

WPS Office Spreadsheet provides robust formula functionalities identical to Excel, including the TEXT function, to help you combine formatting and calculations seamlessly. It is fully compatible with Microsoft Excel files and offers a highly intuitive user interface.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document containing the formulas.
2. Select the target cell: Click on the cell where you want the combined text and formatted number to appear.
3. Input the TEXT formula: Type =TEXT(value, "0.00")&" your text" into the formula bar and press Enter to immediately see the perfectly formatted result.

Fully compatible with Microsoft Excel (.xlsx) file formats and functionsSupports over 300 built-in formulas, including TEXT and CONCATENATEFree, lightweight, and fast to launch on multiple operating systemsIntuitive interface that feels familiar to Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does standard cell formatting not work on formulas with text?

When you use the ampersand (&) or CONCATENATE function to join a number and text, Excel converts the entire output into a text string. Standard cell formatting from the ribbon menu only applies to raw numeric values, not to text strings.

How can I format a number with thousands separators while adding text?

You can include commas in your TEXT function format code. For example, use =TEXT(A1, "#,##0.00")&" USD" to display a number with a thousands comma separator, two decimal places, and the text 'USD'.

Does the TEXT function round the number?

Yes, the TEXT function will automatically round the displayed number based on the format code you provide. For instance, formatting the number 4.56 as "0.0" will correctly round and display it as 4.6.

Can I use the ROUND function instead of TEXT?

While ROUND limits the actual decimal places mathematically (e.g., =ROUND(H95/I95, 1)&" Hours"), it may drop trailing zeros (for example, 4.0 becomes just 4). The TEXT function ensures the exact visual formatting, including trailing zeros, is always maintained.

Document & File Issues