How do I find and replace the "

[Guest]

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.

 

[Bob Phillips]

I used ~"" replaced by " in my test using Ctrl-H and it seemed to work okay.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

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!

 

[Guest]

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]

Open the text delimited file and see if the apostrophes are there (maybe use
Notepad).

Maybe you can just clean up that text file--but watch out for strings like:

"Mawmawball's answer will be coming soon"

 

[Guest]

Hi Dave,
I opened them in Notepad and they're definitely there. I'm cleaning them up
again in Notepad. But I definitely need to find out why they are
multiplying. I use these text delimited files quite often and this is the
first time I've encountered this problem.

 

[Dave Peterson]

If they're multiplying in the .CSV file, then I'd look at the thing that creates
the .CSV file.

Hi Dave,
I opened them in Notepad and they're definitely there. I'm cleaning them up
again in Notepad. But I definitely need to find out why they are
multiplying. I use these text delimited files quite often and this is the
first time I've encountered this problem.

 

[Gord Dibben]

Could the extra apostrophe come from having
Tools>Options>Transition>Transition Navigation Keys checked?

If it is checked you will see an apostrophe in any text cell that is
left-aligned.

Or a ^ if centered and a " if right-aligned.


Gord Dibben Excel MVP

 

[Guest]

Found the problem. There are a lot of measurements in the product text file.
Apparently, when I chose the " as a text qualifier, Excel was placing an
apostrophe beside each of the apostrophe's in the file to mark them as text.
I got rid of all the unnecessary apostrophes, tried opening it again using
"none" as the text qualifier and it's working great again. Live and learn.
lol.

Thank you all for all your help. Especially you Dave, I'd still be deleting
the apostrophes if not for your help with the macro.

Have a Merry Christmas, everybody.