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

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
Highlighted

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
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies