This guide will explain how to add a text prefix to an existing value using custom formatting in Excel.
Text prefixes refer to words or characters that are placed at the beginning of an existing string. For example, given a list of surnames, you may want to add a “Mr.” or “Ms.” at the start of each value.
While Excel offers several ways to add two or more strings together, using these methods to add a prefix comes with a downside. A formula has to be set every time a specific prefix is added. Any numerical values will also be converted to text when you append text at the start.
Instead of this, we can easily use custom formatting options to create a format that adds prefixes for us. In this way, it maintains the original data and any added text can be removed by just reverting the cell format.
In this guide, we will provide a step-by-step tutorial on how to add text prefixes with the custom format options in Excel. We will cover how to add prefixes to both numerical and text values and how to remove the text prefixes if needed.
A Real Example of Adding Text Prefixes with Custom Format in Excel.
Adding Text Prefixes to Numerical Values
You may have already encountered text prefixes for numerical values through number formatting options. For example, applying the accounting number formatting to the value 100 will let it show as “$ 100” in your spreadsheet.
In situations where you need to add custom prefixes like Week, Day etc., Excel has a feature that allows you to create custom number formatting codes that you can apply to existing values in your spreadsheet.
Let’s explore a simple example where we are required to add a text prefix to existing numerical values.
In the table above, we have a list of dates and their corresponding week number. We want to add the word “Week” as a prefix for each week number. Instead of just 22, the value should say “Week 22” in the sheet. However, we still want the actual numerical value to remain unchanged.
Typing the shortcut Ctrl+1 (Windows) or Cmd+1 (Mac) will allow you to access Excel’s Cell Formatting options. These options are flexible enough to allow users to create custom format codes. The number format code “Week” 0 adds the string “Week” before a given integer.
Applying our custom format to our target range will add our desired text prefix.
In the example above, we demonstrate that the formatted values can still be used as numerical input. We’ve created a third column labeled Processing Week that calculates the week number four weeks after each date. We used the formula =B2+4 which results in a value of “Week 26” since Excel considers the cell B.
Adding Text Prefixes to Text Values
We can also add text prefixes to text values as well. This is often needed when you need to add honorifics to a person’s name.
In the table above, we’ve added the “Dr. ” prefix to each surname in a list. We can achieve this format by using the custom formatting code “Dr .” @. The ‘@’ symbol is used as a placeholder for any alphanumeric characters.
Removing Text Prefixes
An advantage to using custom formatting codes when adding prefixes is the ability to quickly remove the prefix when needed.
We can select the text containing our text prefixes and change the option in the Number format dropdown menu from Custom to Number.
Converting the formatting of our cells to Number removes the text prefix we’ve added in our custom format code.
Click on the link below to create a copy of our examples.
Head to the next section to read our step-by-step tutorial on how to add a text prefix to your cells using custom formatting.
How to Add Text Prefix with Custom Format in Excel
- Select the range of values you wish to add a text prefix to.
In the table above, we want to add the prefix “Week” to the range B2:B19.
- On the Home tab, click on the icon seen below to access the Cell Formatting dialog box.
- Head to the Number tab and select Custom as the category.
- Use the provided text box under Type to enter the appropriate formatting code. For example, if you want to add the word “Week” before an integer, use the code “Week” 0. You can see the result of your formatting codes under the Sample label.
Click on OK to apply the formatting to the selected cells.
- The selected cells should now follow the specified custom formatting.
- We can also use custom formatting to add prefixes to text values. Select the text values you want to add a prefix to.
- Access the Format Cells dialog box from the Home tab. In the Number tab, click on Custom as the category.
Instead of using “0”, use the “@” character as a placeholder for your text. Click on OK to proceed.
- Your selected text should now have formatting with the specified text prefix.
That should be everything you need to know to add a text prefix with custom format options in Excel.
If you want to read more about number formatting, read our guide on how to convert time values into decimals in Microsoft Excel.
That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!
