Software

Ask something
Discussion Locked

EXCEL

0 helpful
4 replies

by tipscomp

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
If the whole of your excel sheet conains an unprintable character at the end of each field (eg a square) how can you use the CLEAN to remove them?

4 replies

by dklippert

If the character is the last one on the right , use:
=CODE(Right(CellWithExtraCharacter,1))
To find the code. (substitute a cell address for "CellWithExtraCharacter")

It is probabley a line feed, so you could use this macro. If not substitute the code you found for Chr(X).

Sub ReplaceText()
Dim c As Range
Dim strTemp As String
For Each c In ActiveSheet.UsedRange.Cells
If Not IsError(c.Value) Then
strTemp = Application.WorksheetFunction.Substitute(c.Value, Chr(10), " ")
c.Value = strTemp
End If
Next
End Sub

by dklippert

Here's the code using Clean. It is easier.
Sub ReplaceText()
Dim c As Range
Dim strTemp As String
For Each c In ActiveSheet.UsedRange.Cells
If Not IsError(c.Value) Then
strTemp = Application.WorksheetFunction.Clean(c)
c.Value = strTemp
End If
Next
End Sub

by tipscomp

Thanks for your help - makes my report much neater.
Clean routine is a very fast script to run.

This discussion is closed to new replies.