Removing ALL Special Characters from a Column in Excel to save as .Txt and upload to Database

Question

Scenario: I am helping to clean .xls files we are getting from 3rd Parties. They are submitting horrific looking .xls. We are using IBM Datastage(DS) to upload the data but the list below and their special chars are crashing our DS job, these are first names, and last names. Is there a way in Excel to strip the columns from all special characters

Special Chars: (~*, ",", " “ ", "‘", "~", "?", "Ñ", "Á", "’", "Í", "é")

Currently we are: ctrl + F to find the special chars and delete them with a space and sometimes the hidden characters we have to remove on the .TXT because they won't show on .xls until they are saved on the .txt.

We have over 85 files to clean and there has to be an easier way to format these files. Please help :(

Can you define a list of characters you want to **keep** ? It might be easier to write code to remove all the rest. — Gary's Student, Apr 26 '20 at 15:24
You can write a VBA macro that will open each file; clean the characters; and then save it. — Ron Rosenfeld, Apr 26 '20 at 16:19

score 0 · Answer 1 · answered Apr 26 '20 at 16:09

1) Alt + F11 on excel 2) Insert Tab 3) Module 4) Copy Paste this there and save (you can close the window)

Function Remove(Str As String) As String 'UPDATE BY PITAARA TECH Dim xChars As String Dim I As Long xChars = "=/.',#$%@!()^*&~?-“ ’" For I = 1 To Len(xChars) Str = Replace$(Str, Mid$(xChars, I, 1), "") Next Remove = Str End Function

5) Insert a column to the right of the column you wish to remove special chars 6) Enter =remove(column) hit enter 7) Drag down

Removing ALL Special Characters from a Column in Excel to save as .Txt and upload to Database

1 Answers1