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:
=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

Use the SUBSTITUTE function.

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

Combine the RIGHT and the LEN functions.

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

  • Close the VBA window after saving it.
  • Select E5.
  • Enter the formula:
=ConvertSpecial(D5)
  • Press Enter.
  • Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Convert Special Characters with Excel VBA


Download Practice Workbook

Download the following workbook.


<< Go Back to Characters | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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