This article illustrates how to add characters in the excel formula. You may need to add some specific characters to an existing excel formula to get the desired result. For example, assume you have applied some formulas to show the growth of your business over the years. But, Excel does not show the + sign before positive numbers. So, you may need to add this character in those formulas to show the growth with signs. Here, you will learn about the 4 simplest ways to do that in excel.
Download Practice Workbook
You can download the practice workbook from the download button below.
What Are Excel Characters?
Numbers, letters, punctuations, and special characters are all considered characters in excel. Numbers are from 0 to 9. Both uppercase and lowercase letters are different characters. Space, exclamation marks (!), double quotes (“ ”) etc. are punctuations. Colon (:), semicolon (;), At symbol (@), etc. are special characters. Each of these characters has a different code to represent them. For example, the code for the dollar sign ($) and the copyright sign (©) are 36 and 169 respectively. The CHAR and UNICHAR functions are used to retrieve them. You can see the list here.
4 Ways to Add Characters in Excel Formula
Assume you have used the LEFT function to extract the area codes from a list of phone numbers.
- But you want to add some special characters in those formulas so that the area codes are wrapped in brackets as shown below.
- Follow the methods below to be able to do that.
1. Add Ampersand (&) Symbol in Excel Formula
You can use the Ampersand symbol to add characters in the excel formula.
- Now, enter the following formula in cell C5 instead of the earlier one. Then drag the Fill Handle icon to the cells below. After that, you will get the following result.
="(" & LEFT(B5,3) & ")"
- You can also use the CHAR function instead of enclosing the characters in double quotes. Change the formula to the following one to do that.
=CHAR(40) & LEFT(B5,3) & CHAR(41)
2. Combine CONCAT Function with Current Formula
Alternatively, you can use the CONCAT function to add characters in excel formulas. You can change the formula in cell C5 to the following one. Then, you will see the same result as earlier.
- How to Set Character Limit in Excel
- Make a List of Special Characters in Excel (3 Easy Ways)
- How to Filter Special Characters in Excel (An Easy Guide)
- Apply Formula to Identify Special Characters in Excel (4 Methods)
3. Use CONCATENATE Function
You can use the CONCATENATE function too to add characters in the excel formula. Replace the formula in cell C5 with the following one to do that.
4. Add Characters in Excel Formula Using TEXTJOIN Function
The TEXTJOIN function will also allow you to add characters to an excel formula. You need to apply the following formula in cell C5 for that.
Things to Remember
- You must enclose the characters in double quotes to add them to the formula.
- The CONCAT function is the replacement for the CONCATENATE function.
Now you know the 4 methods to add characters in the excel formula. Do you have any further queries or suggestions? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.