How to Convert Special Characters in Excel (6 Easy Ways)

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.

Convert Special Characters in Excel: 6 Easy Ways

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 pounds (£). 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.

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:

• Press Enter afterward.

• Now, use AutoFill to fill the rest.
• Therefore, it’ll return the converted results.

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:

• Next, press Enter to return the output.
• Apply AutoFill to get other results.

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 of converting 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 figure below 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:

• Press Enter.
• With AutoFill, complete the rest.
• Hence, it’ll convert the special characters.

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:

• Press Enter afterward.
• Consequently, apply AutoFill to change other cells.
• Thus, you’ll get the desired results.

Download Practice Workbook

Download the following workbook to practice by yourself.

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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Characters | Learn Excel