Remove CHAR160 and standard spaces from entire sheet; not by column

[angeloudaki]

Issue:
I often import data from web pages where there is no 'export as' function which means I am often left with non-breaking spaces embedded (not visible in cells), or or leading/trailing spaces. This causes problems when using countif functions and also Conditional formatting (for duplicate values). This makes my calculations inaccurate.

Existing solution:
I know I can use the =TRIM(A1) for leading/trailing spaces and =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) for non-breaking spaces but it is a pain when I have 20 columns. I currently create a helper column and just change the column reference then copy and paste as unformatted content to each column but when I have to do it a lot it is time consuming

All online help content points towards the cell reference formulae; and I couldn't find anything in the forum...

 

[Peter_SSs]

Would a macro solution be acceptable?

 

[angeloudaki]

I have never used Macros - ever - but I actually think they would pretty much solve most of my problems! If a macro is the only way - hit me with it - I will have a go. Learning curves welcome! I am the only one who uses this document so there is no problem with compatibility...

 

[Peter_SSs]

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the CleanUp macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

I'd suggest testing in a copy of your workbook and with a fairly small set of data to see how it goes.
If successful, then expand the data size.

Sub CleanUp()
  With ActiveSheet.UsedRange
    .Value = Evaluate(Replace("if(len(#),trim(clean(substitute(#,char(160),"" ""))),"""")", "#", .Address))
  End With
End Sub

 

[angeloudaki]

Thanks Peter! I will have a go and see - Bit scared of macros!!

 

[Peter_SSs]

Bit scared of macros!!

No time like the present to dip your toe in the water.