You need to add text like a currency symbol or unit of measure to numbers in your Excel sheet. Typing the text manually into each cell is slow and error-prone. Excel’s custom number formatting feature lets you display text with numbers without changing the underlying cell value. This article shows you the exact steps to create formats with prefixes and suffixes.
Key Takeaways: Adding Text to Numbers with Custom Formats
- Custom Number Format dialog: Use this dialog to define a format code that controls how numbers are displayed.
- Format code with text in quotes: Place any text you want to show before or after the number inside double quotation marks.
- Format Cells shortcut Ctrl+1: This is the fastest way to open the dialog box to apply or edit a custom format.
Understanding Custom Number Formatting in Excel
Custom number formatting changes how a cell’s value is displayed without altering the actual data. This means a cell containing the number 100 can be shown as “$100.00”, “100 kg”, or “Item-100”. The cell’s value remains 100 for calculations. The format is built using a code made of placeholders and symbols. Text you want to display must be enclosed in double quotes. You can apply these formats through the Format Cells dialog.
Structure of a Custom Format Code
A basic custom format code has up to four sections separated by semicolons. These sections define formats for positive numbers, negative numbers, zero values, and text, in that order. For adding simple prefixes or suffixes, you often only need the first section. The number placeholder is usually “0” or “#”. The “0” forces a digit to show, while “#” shows a digit only if it is significant. To add static text, you type it within double quotes in the code.
Steps to Create a Custom Format with a Prefix or Suffix
Follow these steps to add text before or after your numbers. The process is the same for both prefixes and suffixes.
- Select the target cells
Click and drag to select the cell or range of cells containing the numbers you want to format. - Open the Format Cells dialog
Press Ctrl+1 on your keyboard. Alternatively, right-click the selected cells and choose Format Cells from the context menu. - Navigate to the Custom category
In the Format Cells dialog box, click the Number tab. From the Category list on the left, select Custom. - Enter your custom format code
In the Type field, you will see the current format. Delete it and type your new code. For a prefix, type the text in quotes, then the number format. Example: “USD “0.00. For a suffix, type the number format first, then the text in quotes. Example: 0.00″ kg”. - Preview and apply the format
Look at the Sample area above the Type field to see a preview. Click OK to apply the custom format to your selected cells.
Method for Adding Both a Prefix and a Suffix
You can combine both elements in one format code. The structure is: “Prefix Text”NumberFormat”Suffix Text”. Ensure the number format part, like 0 or #,##0.00, is placed between the two quoted text sections.
- Select cells and open Format Cells
Select your data range and press Ctrl+1. - Go to the Custom category
Click the Number tab and select Custom from the list. - Enter the combined format code
In the Type box, enter a code like “Ref: “0000”-A” to display the number 15 as “Ref: 0015-A”. Click OK.
Common Mistakes and Formatting Limitations
Custom number formatting is powerful but has specific rules. Avoid these common errors to ensure your formats work correctly.
The Text Does Not Appear After Applying the Format
This usually happens if you forget the double quotation marks around the text. Excel interprets unquoted letters as special format codes. Always enclose your prefix or suffix text in double quotes. Also, check that you are in the Custom category and not using a different format like Text, which will show the full code in the cell.
The Number Loses Its Decimal Places or Comma Separators
When you add text, you must re-specify the number’s format. If your original number was 1,234.56 and you use a code like “$”0, the cell will show “$1235” because the format “0” rounds to no decimals and removes thousands separators. Use a code like “$”#,##0.00 to preserve the original number’s appearance.
You Cannot Use the Formatted Number in Calculations
This is not a mistake but a key limitation. The added text is for display only. If you apply a suffix like ” kg”, the cell value is still just the number. Formulas like SUM will work correctly. However, you cannot reference the displayed text in functions like LEFT or MID because that text is not in the cell.
Custom Formatting vs. Formula Concatenation: Key Differences
| Item | Custom Number Formatting | Formula Concatenation (Using & or TEXTJOIN) |
|---|---|---|
| Cell Value | Remains the original number | Becomes a text string |
| Use in Calculations | Yes, the number can be summed or averaged | No, the result is text and cannot be calculated |
| Editing the Display | Change the format code without changing data | Must edit the formula and recopy it |
| Complexity | Simple codes for adding static text | Required for dynamic text based on conditions |
| Data Type | Cell is still a number | Cell becomes text |
You can now add professional prefixes and suffixes to numbers in your worksheets. Use custom formatting when you need to keep numbers usable in formulas. Try creating a format that shows negative numbers in red with a minus sign prefix. For more control, explore using the TEXT function to build formatted text strings within a formula.