Forum Discussion

Anonymous's avatar
Anonymous
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 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 

  

 

  

Formulas and Functions
Macros and VBA

3 Replies

Sort By
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 25, 2018

    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

    • Anonymous's avatar
      Anonymous
      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's avatar
        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 macro recorder!

Resources