Home

Getting clean data

Highlighted
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

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
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies