Find/Replace on leading quotation mark

[Dave]

Find/Replace function doesn't work to replace quotation
marks with blanks. Is there another way to do this?
Thanks.

 

[Frank Kabel]

Hi Dave
try
=SUBSTITUTE(A1,"""","")
to delete all " in cell A1

Frank

 

[Dave Peterson]

I guessing that you really meant apostrophe (or single quote).

And you didn't want to replace it with spaces--just get rid of it.

If those are true, you could use something like:
Select an empty cell
copy it
Select your range to fix
Edit|paste special|check Add

But if you wanted to do it lots of times, maybe a macro would be more
convenient:

Option Explicit

Sub testme()

Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no text constants found"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = myCell.Value
Next myCell

End Sub


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