UnKnown Character Removal

[CyndyG]

I would like to have a command button to run code that would check for
unknown characters in column A. I need to remove the end of file character
before it is copied to a template. I don't know how many rows to have
searched because data with vary. The data consist of all numbers.

 

[Biff]

Hi!

This *might* work.

From David McRitchie:

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

Biff

 

[Gord Dibben]

Cyndy

Try this macro. If no luck, post back and we'll see about another tack.

Public Sub StripAll_But_Nums()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String
On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.Text)
sChar = Mid(.Text, i, 1)
If sChar Like "[0-9]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben Excel MVP