In this article, you will learn about Excel characters. We will show you how to create a list of Excel characters. We will apply formulas and cell formatting to insert characters into the cell.
Then, we will describe various applications of Excel characters with different criteria. We will also apply VBA code to insert Excel characters into cells. We will list some special Excel characters as well. Lastly, we will count the number of characters in a cell or range of cells.
Excel characters are vital as they allow users to organize, manipulate, format, and customize the dataset.
Download Practice Workbook
You can download this practice book while going through the article.
How to Create a List of Excel Characters
Here, we’ll learn how to make a list of Special Characters in Excel. Follow the instructions below.
1. Create a List of Excel Characters Using CHAR Function
- Go to cell C5 and insert the following formula based on the CHAR function.
=CHAR(B5)- Use Fill Handle to Autofill data in range C6:C14.
2. Get the Character Codes in Excel from Characters
- Go to cell C5 and insert the following formula based on the CODE function.
=CODE(B5)- Use Fill Handle to Autofill data in range C6:C14.
Apply Formulas to Insert Characters in Excel
1. Use Ampersand Operator to Insert Characters
- Go to cell D5 and insert the following formula based on the ampersand operator.
=B5&" " &C5- Apply Fill Handle to Autofill data in range D6:D14.
2. Insert Characters Using CONCATENATE Function
- Go to cell D5 and insert the following formula based on the CONCATENATE function.
=CONCATENATE(B5," ",C5)- Drag the Fill Handle icon to Autofill data in range D6:D14.
3. Apply CONCAT Function to Insert Characters
- Go to cell D5 and insert the following formula based on the CONCAT function.
=CONCAT(B5," ",C5)- Use Fill Handle to Autofill data in range D6:D14.
The ampersand operator, the CONCATENATE function, and the CONCAT function – all are interchangeable. So, you can use any of these to do the same operations in Excel.
Read More: How to Find Special Characters in Excel
Apply Cell Formatting to Insert Numeric Characters in Excel
- Copy values from range B5:B14 to range C5:C14.
- Select range C5:C14 >> right-click mouse >> select Format Cells option from the Context Menu.
- Apply the following steps in the dialog box.
- You will see +1 character before the cells.
Insert Excel Characters with Different Criteria
1. Insert Characters to the Beginning of Cells
- Go to cell C5 and insert the following formula based on the ampersand operator.
="USA-"&B5- Drag Fill Handle icon to Autofill data in range C6:C14.
2. Insert Characters to the End of Cells
- Go to cell C5 and insert the following formula based on the CONCATENATE function.
=CONCATENATE(B5, ", USA")- Pull Fill Handle icon to Autofill data in range C6:C14.
3. Add Characters to the Beginning and End of String
- Go to cell C5 and insert the following formula based on the CONCAT function.
=CONCAT("Place: ",B5,", USA")- Use Fill Handle to Autofill data in range C6:C14.
4. Combine Characters from Multiple Cells
- Go to cell E5 and insert the following formula based on the TEXTJOIN function.
=TEXTJOIN(", ",TRUE,B5,C5,D5)- Apply Fill Handle to Autofill data in range E6:E14.
You can also use the ampersand operator, the CONCATENATE function, or the CONCAT function here as well. For example, you can use this formula.
=CONCAT(B5,", ",C5,", ",D5)
5. Insert Characters Before Excel Formula
- Go to cell D5 and insert the following formula based on the SUM function and the ampersand operator.
="Revenue: " & SUM(B5,C5)- Apply Fill Handle to Autofill data in range D6:D14.
6. Insert Characters After Excel Formula
- Go to cell D5 and insert the following formula based on the SUM function and the CONCAT function.
=CONCAT(SUM(B5+C5), "-Revenue")- Drag Fill Handle icon to Autofill data in range D6:D14.
Read More: How to Add Characters in Excel Formula
7. Insert Characters to Formula on Both Sides
- Go to cell D5 and insert the following formula based on the SUM function and the CONCATENATE function.
=CONCATENATE("$",SUM(B5,C5)," is the revenue")- Use Fill Handle to Autofill data in range D6:D14.
8. Insert Characters After Nth Character
- Go to cell C5 and insert the following formula based on LEFT, RIGHT, LEN functions, and the ampersand operator.
=LEFT(B5,1)&"-"&RIGHT(B5,LEN(B5)-1)- Use Fill Handle to Autofill data in range C6:C14.
9. Insert Character Before a Specific Character
- Go to cell C5 and insert the following formula based on LEFT, RIGHT, LEN, SEARCH functions, and the CONCAT function.
=CONCAT(LEFT(B5,SEARCH("-",B5)-1),"(EEE)",RIGHT(B5,LEN(B5)-SEARCH("-",B5)+1))- Use Fill Handle to Autofill data in range C6:C14.
10. Insert Character After a Specific Character
- Go to cell C5 and insert the following formula based on LEFT, RIGHT, LEN, SEARCH functions and the CONCAT function.
=CONCAT(LEFT(B5,SEARCH("-",B5)),"(EEE)",RIGHT(B5,LEN(B5)-SEARCH("-",B5)))- Pull Fill Handle icon to Autofill data in range C6:C14.
Apply VBA to Insert Characters in Excel Cell
- Go to the Developer tab >> Visual Basic.
- Select Insert >> Module.
- Put the following code in your VBA Macro Editor and press the Run key or F5 to run the code.
Sub Excel_Characters()
'variable declaration
Dim myRng As Range
'set the range variable
Set myRng = Sheets("VBA").Range("B5:C14")
'insert characters into cells of the range
For i = 1 To myRng.Rows.Count
myRng.Cells(i, 2) = "Place: " & myRng.Cells(i, 1) & ", USA"
Next i
End Sub- After running the code, you will see the output.
How to Make a List of Special Excel Characters
1. Use CHAR Function to Make a List of Special Characters
- Go to cell C5 and insert the following formula based on the CHAR function.
=CHAR(B5)- Use Fill Handle to Autofill data in range C6:C14.
2. Combine CHAR, INDIRECT, and ROW Functions for Special Characters
- Go to cell C5 and insert the following formula based on the combination of CHAR, INDIRECT and ROW functions.
=CHAR(INDIRECT("B"&ROW()))- Use Fill Handle to Autofill data in range C6:C14.
How to Count Number of Characters in Excel
1. Count Number of Characters in a Cell
- Go to cell C5 and insert the following formula based on the LEN function.
=LEN(B5)- Apply Fill Handle to Autofill data in range C6:C14.
2. Count Number of Characters in a Range of Cells
- Go to cell D5 and insert the following formula based on SUMPRODUCT and LEN functions.
=SUMPRODUCT(LEN(B5:B14))
3. Count Specific Character in a Cell
- Go to cell C5 and insert the following formula based on SUBSTITUTE and LEN functions.
=LEN(B5)-LEN(SUBSTITUTE(B5,"e",""))- Use Fill Handle to Autofill data in range C6:C14. You will see the number “e” in each cell.
4. Count Specific Character in a Range of Cells
- Go to cell D5 and insert the following formula based on SUMPRODUCT, SUBSTITUTE, and LEN functions.
=SUMPRODUCT(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,"e","")))- You will see the number of “e” in range B5:B14.
Things to Remember
There are a few things to remember in Excel characters:
- Be careful of cell references.
- Put characters cautiously in between Excel formulas.
- Learn the appropriate character code.
Conclusion
In this article, we have discussed Excel characters. This article will allow users to use Excel characters more effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.
Frequently Asked Questions
1. How can I remove unwanted characters or symbols from a text?
You can use functions like the SUBSTITUTE function or the REPLACE function to remove or replace unwanted characters in Excel.
2. Can I split a text into multiple cells?
Yes, you can use the TEXTSPLIT function to split a text character into multiple cells using a delimiter.
3. What is the difference between CONCATENATE and CONCAT functions?
These two functions have the same purpose and format. The difference is, the CONCATENATE function is available in earlier versions of Excel, while the CONCAT function is available in Excel 2016 and later versions.
The CONCAT function is concise and it can take a range of cells as an input and return a text with all the elements of the range of cells joined together. On the other hand, the CONCATENATE function does take a range of cells as input but does not return a concatenated text rather returns an array of cells as same as the input range in a column. So, these are the basic differences between the two functions.
Excel Characters: Knowledge Hub
<< Go Back to Learn Excel
