How do I find and replace a symbol in an excel spreadsheet

[Dave Peterson]

How did you insert it?

Is it in a formula or is it just typed (or inserted via Insert|Symbol)?

You can use Chip Pearson's Cell View addin to find out the character it is:
http://www.cpearson.com/excel/CellView.htm

If you're lucky, you'll be able to do Edit|Replace and hit and hold the alt-key
while you type the ASCII representation (like alt-0010 for alt-enter (line feed
in a cell)).

And replace it with the symbol you want.

If you're unluck, you could use a macro:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(13))

myGoodChars = Array(Chr(9))

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

(I replaced the CarriageReturn with the Tab character in this example.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Another way using a helper cell:

=substitute(a1,char(10),char(9))

and copy|paste special values over the original cell.

John C wrote: