Remove non-printing characters

  • Thread starter Thread starter ian
  • Start date Start date
I

ian

I'm having trouble working with apperently empty cells in a
spreadsheet which has data pasted from a large Word document.


Some cells only look empty. I tried Clean which doesn't work
completetly. I don't want to try Trim as it will replace any double
spaces etc with single spaces.

Using Code on one cell I get 32, telling there is a space in the
cell, (other "empty" cells might have other Codes.)

Is there any simple Macros I can use to delete the contents of
apperently empty cells.

Thanks
 
Loop through the cells, loop through the characters in the cell and use the
replace command with a list of characters you want to remove. You could use
the Like command in your comparisons.
 
Look at the CLEAN function. You mat also want to look at David McRitchies TRIMALL function:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm having trouble working with apperently empty cells in a
| spreadsheet which has data pasted from a large Word document.
|
|
| Some cells only look empty. I tried Clean which doesn't work
| completetly. I don't want to try Trim as it will replace any double
| spaces etc with single spaces.
|
| Using Code on one cell I get 32, telling there is a space in the
| cell, (other "empty" cells might have other Codes.)
|
| Is there any simple Macros I can use to delete the contents of
| apperently empty cells.
|
| Thanks
|
 
Tom

Thanks. I was hoping to do this without specifying the characters to
remove (as I don't know if I'd miss any). Just to empty any cells
which look empty.

Ian
 
Select your cells and try this:

Sub DeleteBlanks()
Application.ScreenUpdating = False

With Selection.SpecialCells(xlConstants)
.Replace what:=" ", Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
End With

Application.ScreenUpdating = True
End Sub



HTH,
JP
 
Back
Top