Forum Discussion

Deleted

Apr 25, 2018

Getting clean data

I am working on a preping expense sheets for import by another ERP. I am running into issues with ( ` ) character. But I suspect there will be other characters ", ' that are in the sheets. What...

Formulas and Functions

Macros and VBA

Haytham Amairah

Silver Contributor

Apr 25, 2018

Hi Pervaz,

You can depend on some native tools such as https://support.office.com/en-us/article/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90.

Select data
Press Ctrl+H to open the Find and Replace tool
In Find what box, type the character `
Leave the Replace with box blank as it is, and then hit Replace All

Regards

Deleted
Apr 25, 2018
Yes True but sorry I forgot to mention;
The users will be other people and we need a solution that is as Automated as possible.

And that is a consideration for the Macro.

Pervaz
- Haytham Amairah
  Silver Contributor
  Apr 25, 2018
  Hi Pervaz,
  
  You can simulate the steps of using Find and Replace to clean up the worksheet from this character ` as follows:
  Sub Macro1()
  Cells.Replace What:="`", Replacement:="", LookAt:=xlPart, SearchOrder:= _
  xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  End Sub
  
  I have not written this macro but have recorded it using the https://support.office.com/en-us/article/Automate-tasks-with-the-Macro-Recorder-974ef220-f716-4e01-b015-3ea70e64937b!