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 plus (+) 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.
What Are Excel Characters?
Numbers, letters, punctuation, 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.
Add Characters in Excel Formula: 4 Ways
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 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)
Read More: How to Insert Character Between Text in Excel
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.
=CONCAT("(",LEFT(B5,3),")")
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.
=CONCATENATE("(",LEFT(B5,3),")")
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.
=TEXTJOIN("",TRUE,"(",LEFT(B5,3),")")
Read More: How to Add a Character in Excel to Multiple Cells
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.
Download Practice Workbook
You can download the practice workbook from the download button below.
Conclusion
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. Stay with us and keep learning.