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

Get FREE Advanced Excel Exercises with Solutions!

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:
=REPLACE(D5,1,1,"")
  • Press Enter afterward.

Change Special Characters with Excel REPLACE Function

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

Change Special Characters with Excel REPLACE Function


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 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.

Replace Special Characters in Excel Using Flash Fill Feature

  • Then, go to Home Editing Fill Flash Fill.

Replace Special Characters in Excel Using Flash Fill Feature

  • Immediately, you’ll see the converted outputs.
  • See the figure below for a better understanding.

Replace Special Characters in Excel Using Flash Fill Feature


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.

NOTE: The LEN function gives out 5. And after subtracting 1, it’ll be 4. Thus, the RIGHT function returns 4 characters from the last.

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.

Convert Special Characters with Excel VBA

  • 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

Convert Special Characters with Excel VBA

  • 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.

Convert Special Characters with Excel VBA


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo