Forum Discussion
Tool for removing unwanted/obstructive/redundant characters
I've lately come across numerous questions on how to remove redundant/unwanted leading and trailing spaces.
Question:
Does the latest and greatest Excel 365 offer a ready-to-use and robust tool that facilitates this job with the least effort?
4 Replies
Use TEXT.TRIM for most space cleanup in Excel 365.
Task
Best Function
Extra spaces (all kinds)
Non-printable characters
Non-breaking spaces from HTML
SUBSTITUTE(..., CHAR(160), " ")
Combine all
TRIM(CLEAN(...)) or TEXT.TRIM(...)
My answers are voluntary and without guarantee!
Hope this will help you.
Thanks for your prompt reply.
Yes, in order to accomplish the job you can use a bunch of formulas or create a macro if this is your preference. I myself am leaning toward automation by means of C#.
However, myriads of regular users do not have this capacity. They do not know how to build complex formulas, let alone how to create a macro. They have no clue about non-breaking space, ASCII codes, control chars, etc.
Speaking on behalf of those users who occasionally need to process data copied from Word or PDF data, Excel lacks a robust and flexible tool with simple user interface allowing to run the cleanup without too much thinking and within a blink of an eye. And that is where my question stems from.
Hope it clarifies the aim of this ticket.
That actually was never a problem for me. Always had ASAPUtilites Addin doing the work without a fuss. I hope you must have come across the same. ASAPUtilites > Text > Advance Character Removal or Replace
Hello,
thanks for your reply.No, I am not going to install third-party utilities.
Looking for a built-in tool with user-friendly interface.
