asked on

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

zorvek (Kevin Jones)

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

Tocogroup

ASKER

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

zorvek (Kevin Jones)

:-)

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

Tocogroup

ASKER

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

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tocogroup

ASKER

Many thanks Kevin.
That's very informative and helpful.
Toco