Replacing ascii character 13 with -

mark_h82

New Member
Joined
Apr 4, 2005
Messages
41
I have this code I found on here that would delete the ascii character 13:

Code:
Sub KillChar() 
Dim Cll As Object 
Dim X As Long 
Dim TmpCll As String 
Dim Evilcharacter As Byte 'Me 
On Error Resume Next 
Evilcharacter = InputBox("Enter the ASCII number of the character you wish to remove?", "Boxes must go.", 10) 
If Err.Number <> 0 Then End 
On Error GoTo 0 
For Each Cll In Selection 
    If Len(Cll.Value) <> 0 Then 
        TmpCll = Empty 
        For X = 1 To Len(Cll.Value) 
            If Asc(Mid(Cll.Value, X, 1)) <> Evilcharacter Then TmpCll = TmpCll & Mid(Cll.Value, X, 1) 
        Next X 
        ActiveCell.Value = TmpCll 
        End If 
Next Cll 
MsgBox "Done." 
End Sub

Is it possible rather than it deleting this character, to replace it with a - (dash) instead?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
I think that this is all you need :-
Code:
Sub test()
    Dim MyChar As String
    MyChar = Chr(13)
    ActiveSheet.Cells.Replace what:=MyChar, replacement:="-"
    MsgBox ("Done")
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,004
Members
412,304
Latest member
citrus
Top