Remove non-printing characters

[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

 

[Guest]

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.

 

[Niek Otten]

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
|

 

[ian]

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

 

[ian]

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

Thanks for pointers, using WorksheetFunction.Clean and Trim seems to
have worked.

Ian

 

[JP]

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