How to Combine Text and Percentages in Excel and Keep Formatting

Combining descriptive text with percentage values in Excel often strips away crucial formatting, rendering professional reports unreadable. When tracking standard funding sources like federal allocations, presenting data clearly is vital. Securing grants requires absolute precision, and preserving the "%" symbol ensures stakeholders immediately grasp the data. Note the stipulation: Excel's native concatenation discards formatting unless you explicitly define the text structure. For example, converting "NIH Grant: 0.85" into "NIH Grant: 85%" requires the TEXT function. Below, we outline the exact formula syntax to seamlessly merge your strings and percentages while maintaining pristine number formatting.

When working with Excel, you will often find yourself needing to create dynamic reports, summaries, or narrative sentences that combine text with numeric data. For instance, you might want to create a sentence like: "The project completion rate is 85%."

However, if you attempt a straightforward concatenation in Excel-using either the ampersand (&) operator or the CONCATENATE function-you will quickly run into a frustrating limitation. Instead of seeing your beautifully formatted percentage, Excel displays its underlying decimal value. Your sentence ends up looking like this: The project completion rate is 0.85.

This happens because Excel treats cell formatting (like percentage signs, currency symbols, and thousand separators) as a visual layer. Under the hood, the raw data is stored as a decimal or standard number. When you concatenate text with a cell containing a percentage, Excel strips away the visual formatting and pulls only the raw numeric value.

Fortunately, you can easily solve this problem and preserve your desired number format. In this guide, we will explore the Excel formulas and techniques required to combine strings and percentages while keeping the formatting perfectly intact.

The Core Solution: The TEXT Function

The secret to keeping your percentage format when combining it with a text string is Excel's TEXT function. This highly versatile function allows you to convert a numeric value into a text string while explicitly applying a specific number format.

Syntax of the TEXT Function

The syntax for the TEXT function is straightforward:

=TEXT(value, format_text)

• value: The numeric value, formula, or cell reference that you want to format.
• format_text: A text string enclosed in double quotation marks that defines the format you want to apply (e.g., "0%", "0.0%").

Method 1: Combining Text and Percentages Using the Ampersand (&) Operator

The ampersand (&) is the most common and efficient operator for concatenating strings in Excel. By pairing it with the TEXT function, you can build dynamic, clean narrative strings.

Example 1: Basic Percentage (No Decimals)

Let's say cell A2 contains the text "Q3 Growth Rate" and cell B2 contains the value 0.125 (formatted as 12.5%). If you want to create a sentence that states the growth rate rounded to the nearest whole percentage, use the following formula:

="The " & A2 & " is " & TEXT(B2, "0%")

Result: The Q3 Growth Rate is 13%

Example 2: Percentage with Decimals

If you need to display the percentage with a specific number of decimal places, adjust the format_text argument in the TEXT function. For one decimal place, use "0.0%". For two decimal places, use "0.00%".

="The " & A2 & " is " & TEXT(B2, "0.0%")

Result: The Q3 Growth Rate is 12.5%

Method 2: Using the CONCAT or CONCATENATE Functions

If you prefer using formal Excel functions over the ampersand operator, you can use CONCAT (available in Excel 2016 and later) or the older CONCATENATE function. The logic remains exactly the same: you must wrap the percentage cell inside the TEXT function.

Using the same example data (A2 = "Q3 Growth Rate", B2 = 0.125), the formula would look like this:

=CONCAT("The ", A2, " is ", TEXT(B2, "0.0%"))

Result: The Q3 Growth Rate is 12.5%

This approach achieves the exact same output as the ampersand operator; choose whichever method aligns best with your personal formula-writing style.

Advanced Formatting: Handling Positive, Negative, and Zero Percentages

Excel's formatting engine allows you to specify different formats for positive numbers, negative numbers, and zeros within a single TEXT function. You can separate these formats using semicolons (;) inside the format string:

"positive_format;negative_format;zero_format"

This is incredibly useful if you want to explicitly show plus (+) and minus (-) signs in your generated text strings. Consider the following formula:

="Performance change: " & TEXT(B2, "+0.0%;-0.0%;0.0%")

Depending on the value in cell B2, this formula will yield different results:

• If B2 is 0.152, the output is: Performance change: +15.2%
• If B2 is -0.045, the output is: Performance change: -4.5%
• If B2 is 0, the output is: Performance change: 0.0%

Handling Empty Cells or Non-Numeric Values

In professional worksheets, you must always account for potential data anomalies, such as empty cells or unexpected text entries. If you point a TEXT function to an empty cell, Excel treats it as zero, which might display an inaccurate 0% in your final string.

To prevent this, you can wrap your concatenation formula in an IF statement to verify whether the target cell contains data:

=IF(ISBLANK(B2), "No data available", "The rate is " & TEXT(B2, "0%"))

If you want to ensure the cell contains a number before performing the operation, use ISNUMBER:

=IF(ISNUMBER(B2), "The rate is " & TEXT(B2, "0.0%"), "Invalid data")

Quick Reference: Common Percentage Format Codes

When customizing your formulas, refer to this table of standard format codes for the TEXT function:

Summary

Combining strings and percentages in Excel doesn't have to break your spreadsheet's visual formatting. By utilizing the TEXT function, you can cleanly convert numeric data into structured, reader-friendly text strings. Whether you prefer using the ampersand (&) operator or the CONCAT function, explicitly declaring your format string-such as "0%" or "0.0%"-gives you total control over how numbers, decimals, and negative values appear in your dynamically generated text outputs.