How to Convert Special Characters in Excel

The following dataset showcases Salesman, Product, and Net Sales.

To see the sales amounts in pounds (£), convert the symbol into a blank:

Method 1 – Converting Special Characters in Excel with the Find & Select Command.

STEPS:

Press Ctrl+ F.
In the Find and Replace dialog box, select Replace.
Enter £ in Find what.
Keep Replace with blank.
Click Replace All.

Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Method 2 – Change Special Characters with the Excel REPLACE Function

STEPS:

Select E5.
Enter the formula:

=REPLACE(D5,1,1,"")

Press Enter.

Change Special Characters with Excel REPLACE Function

Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Change Special Characters with Excel REPLACE Function

Method 3 – Apply the SUBSTITUTE Function to Convert Special Characters

STEPS:

Select E5.
Enter the formula:

=SUBSTITUTE(D5, "£", "")

Press Enter.
Drag down the Fill Handle to see the result in the rest of the cells.

Method 4 – Replace Special Characters in Excel Using the Flash Fill Feature

STEPS:

Enter 2600 in E5.

Replace Special Characters in Excel Using Flash Fill Feature

Go to Home → Editing → Fill → Flash Fill.

Replace Special Characters in Excel Using Flash Fill Feature

This is the output.

Replace Special Characters in Excel Using Flash Fill Feature

Method 5 – Combine the RIGHT and the LEN Functions to Change Special Characters

STEPS:

Select E5.
Enter the formula:

=RIGHT(D5,LEN(D5)-1)

Press Enter.
Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

NOTE: The LEN function returns 5. After subtracting 1, it’ll be 4. The RIGHT function returns 4 characters.

Method 6 – Convert Special Characters with Excel VBA

STEPS:

Go to Developer > Visual Basic.

Convert Special Characters with Excel VBA

The VBA window will be displayed.
Select Insert > Module.
Copy the 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