How to Convert Special Characters in Excel – 6 Methods

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

Use the REPLACE function.

STEPS:

• Select E5.
• Enter the formula:

• Press Enter.

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

This is the output.

Method 3 – Apply the SUBSTITUTE Function to Convert Special Characters

Use the SUBSTITUTE function.

STEPS:

• Select E5.
• Enter the formula:

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

• Go to Home → Editing → Fill → Flash Fill.

This is the output.

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

Combine the RIGHT and the LEN functions.

STEPS:

• Select E5.
• Enter the formula:

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

This is the output.

Method 6 – Convert Special Characters with Excel VBA

STEPS:

• Go to Developer > Visual Basic.

• 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

• Close the VBA window after saving it.
• Select E5.
• Enter the formula:

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

This is the output.

Download Practice Workbook

Download the following workbook.

<< Go Back to Learn Excel