How do I detect and remove hidden characters from an Excel range of cells.

Hi All,

I have an Excel worksheet which requires 'cleaning'. Specifically, there is a single column in which some of the cells appear blank. However, my VLOOKUP formulas have identified that some of these blank cells actually have hidden characters in them. If I do a LEN() on them they return a value of 1.

How can I create a little VBA procedure to identify and clear these rogue cells ?

Thanks in advance
Toco
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
This macro cleans the current selection:

Public Sub CleanText()

    Dim Cell As Range
   
    For Each Cell In Selection
        Cell.Value = Application.Clean(Cell.Value)
    Next Cell

End Sub

Kevin
0
TocogroupAuthor Commented:
Hi Kevin,

Thanks for your help.

I linked your macro to a button but I'm assuming I need to change your code for it work. When I select a range on the sheet and click the button there's no apparent change.

I've attached a cut-down example of my sheet with the column that requires cleaning, and a 'control' column set to LEN() to reveal the hidden character in apparently blank cells.

How do I code the macro if I want a specific column to be selected ?

Many thanks
Toco
Example-Clean-Cells-Macro.xlsm
0
zorvek (Kevin Jones)ConsultantCommented:
:-)

Those are just single spaces.

Use this macro to remove them:

Public Sub Clear_Hidden_Chars_Click()

    Dim Cell As Range
   
    For Each Cell In Selection
        Cell.Value = Trim(Application.Clean(Cell.Value))
    Next Cell

End Sub

Kevin
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

TocogroupAuthor Commented:
That works great !

How did you know they were spaces ?

How do I change the code to work on column D without having to select it manually ?

Thanks
0
zorvek (Kevin Jones)ConsultantCommented:
I looked at the values in the cells and decoded the character using the VBA Asc function.

On the worksheet you can use the CODE function:

=CODE(D5)

Use this macro to always clean column D on the active worksheet:

Public Sub Clear_Hidden_Chars_Click()

    Dim Cell As Range
   
    For Each Cell In Intersect(ActiveSheet.Range("D:D"), ActiveSheet.UsedRange)
        Cell.Value = Trim(Application.Clean(Cell.Value))
    Next Cell

End Sub

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Many thanks Kevin.
That's very informative and helpful.
Toco
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.