How to clean SPECIAL Characters from Excel Sheet for WordPress or SQL?

Here is how to clean special characters from excel Sheet for WordPress or SQL. If you have Excel or CSV sheet and you want to import in SQL or WordPress. 

Then you should know about these things. We have recently seen, many people have problem to import Excel or CSV template for special characters in WordPress or SQL Database. 

In WordPress, If you have facing any import problem related to Excel or CSV sheet. You need to know about SPECIAL Characters.

Special Characters are responsible to stopped you import process. So, you have to remove your special characters from your Excel or CSV Sheet.  If there are some special characters such as %^&*() within the text strings, and now, you want to remove theses specific characters from the cell strings. To remove them one by one will be time-consuming, here, I will introduce some quick tricks for solving this task in Excel.

VBA Function  : Remove Some Special Characters From Text String With User Defined Function

The following VBA code can help you to remove the specific characters, please do as follows:

Step 1 : Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

Step 2 : Click Insert, go to Module, and paste the following code in the Module Window.

Remove some special characters from text string

Function RemoveSpecial(Str As String) As String

    Dim xChars As String

    Dim I As Long

    xChars = "#$%()^*&"

    For I = 1 To Len(xChars)

        Str = Replace$(Str, Mid$(xChars, I, 1), "")

    Next

    RemoveSpecial = Str

End Function

Step 3 : Then save and close this code, go back to the worksheet, and enter this formula: =removespecial(A2) into a blank cell where you want to put the result. 

Clean Some Special Characters From Text String With Kutools For Excel

If you are not familiar with the VBA code, The best option to choose Kutools for Excel’s to clean Characters very easily.

After installing Kutools for Excel, please so as follows:

Step 1. Select the text strings that you want to remove some special characters.

Step 2. Click Kutools > Text > Remove Characters, see screenshot:

Step 3 : In the Remove Characters dialog box, check Custom option under the Remove Characters section, and enter the special characters that you want to remove, see screenshot:

If you have problem to import in WordPress or SQL, You need to remove the follow invisible Special Characters (_x000D_) code from your Excel or CSV template.  

To remove special characters(_x000D_) code you need to install Kutools for excel or you can done it through above mentioned VBA function.  

For more information, if you use ALT + Enter key in your keyborad to WRAP the text, _x000D_ special characters will generate in the backend in your Excel Sheet. This code will give import problem.

After installing Kutools for Excel, please so as follows to remove this characters from your Excel or CSV sheet:

Step 1. Select the text strings that you want to remove some special characters.

Step 2. Click Kutools > Text > Remove Characters, see screenshot:

Step 3:  Click on Custom checkbox > paste _x000D_  > and click ok button

Step 4 : Click APPLY and OK

Now, you can import your template easily.