Thanks for the help. I got all the apostrophe's off the sheet. The trouble
is, they reappeared the next time I opened the spreadsheet. I'm working with
a text delimited file. I've tried using the " text qualifier, and get 1
extra apostrophe when I open it. I tried using the "none" text qualifier and
got 2 extra apostrophes when I opened it. This problem is driving me nuttier
than I already am lol. Could you tell me what I'm doing wrong here?


"Dave Peterson" wrote:

First, do you mean apostrophe (') or quotation mark (")?

I don't think it's the wildcard. I think the length of the new string confuses
excel and you see that message.

You could use a macro that does its best to fix all the problems. But if there
would be some of those error messages displayed, it goes back to try to fix
them--cell by cell.

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

'double quote (") is chr(34)
BeforeStr = Chr(34) & Chr(34)
AfterStr = Chr(34)

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

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

Ps. Try it against a copy of your data--just in case!


mawmawball wrote:

Hello,
I've got approximately 3500 products in my spreadsheet. I'm not sure why,
but the apostrophe signs are multiply. For example """"""Product a is
2""x3"" long."""""

I've tried using find and replace with the ~" but I keep getting a "Formula
too long" error message. I get the message if I try to find and replace them
singularly and when I use the replace all feature.

Does anyone know how I can remove these apostrophes from my spreadsheet?
I've searched high and low and can't even find a reference to the error
message anywhere.

--

Dave Peterson