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.


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.

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

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


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.

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

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.

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

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.


Related Articles

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo