Getting clean data

[Deleted]

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.

Thanks

 PervazA 

[#2]

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
  

Regards

[#3]

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

[#4]

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!