Home

Getting clean data

Deleted
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.

 

Thanks

 PervazA 

  

 

  

3 Replies
Highlighted

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

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

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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies