Search and Replace a Tilby "~" 1

[ste4en]

I need to replace a "~" character in an Excel spreadsheet with a "-" I have thousands of instances. I can not use search and replace? How do i do this..

I seem to remember using a search for an ASCI character but cant seem to do that either.

thanks

 

[Zathras]

The tilde is a special character when using search and replace. Its meaning is to take the next character literally. The primary purpose would be to allow you to change asterisks and question marks rather than have them treated as wild cards.

In this case you want to change the literal tilde with something else (the dash), so in the replace dialog, you would enter two tildes as the text to be replaced. I.e.,
[tt]
Find what: ~~
Replace with: -
[/tt]

 

[ThomasLafferty]

Hi! Try this:

Public Sub ScrubText()
'*****VARIABLE DECLARATIONS*****
    Dim SearchString As String, ReplacementString As String, ReplacementAttempts As Integer


Range("A1").Select

'*****INITIALIZE VARIABLE*****
Selection.End(xlDown).Select
ReplacementAttempts = Selection.Row

'Loop and clean...
        For CleanString = 1 To ReplacementAttempts
            Cells(CleanString, 1).Select
            ReplacementString = Replace(ActiveCell.Value, "~", "-")
            ActiveCell.Value = ReplacementString
        Next CleanString
        
        
End Sub

Note: this assumes that all your data is in the A column. Attach this routine to a command button and you are all set.

Tom

Born once die twice; born twice die once.

 

[ThomasLafferty]

Go with what Zathras gave you - it's about 10 times as fast and needs no code module nor button...
Tom

Born once die twice; born twice die once.

 

[ste4en]

Thanks, ~~ worked fine.