Getting clean data

Not applicable

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 is recommended for cleaning up data in sheets for a regular use system.

I think formulas in the cells would be too cumbersome.


I could put the cleanup in the Macros.

Is there a code ready made to solve this issues, including ranges.

Or a free third party cleaner available.







3 Replies

Hi Pervaz,


You can depend on some native tools such as Find and Replace.

  • 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



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.


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 macro recorder!

Related Conversations