Aung Shine
Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. Sometimes, we use special characters in our excel worksheets. And if those characters are not desired, we will need to convert them. But converting them one by one is a hassle. It consumes a lot of time too. In this article, we’ll show you all the easy and effective ways to Convert Special Characters in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
6 Easy Ways to Convert Special Characters in Excel
To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales. Here, you can see that the sales amounts are in Pound (£). This article will show you the easy methods to convert this pound symbol into a blank.
1. Convert Special Characters in Excel with Find & Select Command
In our first method, we’ll apply the Find & Select command. Therefore, follow the steps below to convert special characters with this feature.
STEPS:
- First, press the Ctrl and F keys together.
- As a result, the Find and Replace dialog box will pop out.
- Then, under the Replace tab, type £ in the Find what box.
- After that, keep the Replace with field blank.
- Subsequently, press Replace All.
- Lastly, use the AutoFill tool to complete the series.
- Thus, you’ll see the desired outcome.
Read More: How to Find Special Characters in Excel (3 Easy Methods)
2. Change Special Characters with Excel REPLACE Function
The REPLACE function replaces any character from any cell with the one we’ll specify. So, learn the following steps to carry out the operation.
STEPS:
- Firstly, select cell E5.
- Then, type the formula:
=REPLACE(D5,1,1,"")- Press Enter afterward.
- Now, use AutoFill to fill the rest.
- Therefore, it’ll return the converted results.
Read More: How to Check If Cell Contains Special Character in Excel (2 Ways)
3. Apply SUBSTITUTE Function for Converting Special Characters
We can use the SUBSTITUTE function to change special characters in Excel. This function will substitute a character or characters with some specified ones. Hence, follow the process to perform the task.
STEPS:
- First of all, choose cell E5.
- Now, type the formula:
=SUBSTITUTE(D5, "£", "")- Next, press Enter to return the output.
- Apply AutoFill for getting other results.
Read More: Apply Formula to Identify Special Characters in Excel (4 Methods)
Similar Readings
- How to Set Character Limit in Excel
- Make a List of Special Characters in Excel (3 Easy Ways)
- How to Check Character Limit in Excel (With Easy Steps)
- How Do I Stop 255 Character Limit in Excel (4 Ways)
4. Replace Special Characters in Excel Using Flash Fill Feature
Another cool feature in Excel is the Flash Fill. With this feature, you can automatically fill a data series. Now, learn the process to convert special characters with this feature.
STEPS:
- In the beginning, type 2600 in cell E5.
- Then, go to Home → Editing → Fill → Flash Fill.
- Immediately, you’ll see the converted outputs.
- See the below figure for a better understanding.
5. Combine RIGHT and LEN Functions for Changing Special Characters
In this method, we’ll combine the RIGHT and the LEN functions to convert the special characters. The RIGHT function returns a given number of characters from the end of a text string. The LEN function indicates the length of a string. Now, go through the steps below.
STEPS:
- Firstly, click cell E5.
- Type the formula:
=RIGHT(D5,LEN(D5)-1)- Press Enter.
- With AutoFill, complete the rest.
- Hence, it’ll convert the special characters.
Read More: How to Filter Special Characters in Excel (An Easy Guide)
6. Convert Special Characters with Excel VBA
Moreover, code lovers can use the Excel VBA to convert special characters. Learn the process to perform the task.
STEPS:
- Go to Developer ➤ Visual Basic at first.
- As a result, the VBA window will pop out.
- Then, select Insert ➤ Module.
- Copy the below code and paste it into the Module box.
Function ConvertSpecial(Str As String) As String
Dim yChars As String
Dim K As Long
yChars = "#$%()^*&"
For K = 1 To Len(yChars)
Str = Replace$(Str, Mid$(yChars, K, 1), "")
Next
ConvertSpecial = Str
End Function
- Close the VBA window after saving it.
- Now, select cell E5.
- Type the formula:
=ConvertSpecial(D5)- Press Enter afterward.
- Consequently, apply AutoFill to change other cells.
- Thus, you’ll get the desired results.
Read More: How to Find Special Characters Using VBA in Excel (3 Quick Ways)
Conclusion
Henceforth, you will be able to Convert Special Characters in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
