software

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

I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
too longquot; 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.

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;mawmawballquot; gt; wrote in message
...
gt; Hello,
gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt;
gt; I've tried using find and replace with the ~quot; but I keep getting a
quot;Formula
gt; too longquot; error message. I get the message if I try to find and replace
them
gt; singularly and when I use the replace all feature.
gt;
gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt; I've searched high and low and can't even find a reference to the error
gt; message anywhere.
First, do you mean apostrophe (') or quotation mark (quot;)?

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 (quot;) is chr(34)
BeforeStr = Chr(34) amp; 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 quot;Select some cells in the used rangequot;
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:
www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!mawmawball wrote:
gt;
gt; Hello,
gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt;
gt; I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
gt; too longquot; error message. I get the message if I try to find and replace them
gt; singularly and when I use the replace all feature.
gt;
gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt; I've searched high and low and can't even find a reference to the error
gt; message anywhere.

--

Dave Peterson

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 quot; text qualifier, and get 1
extra apostrophe when I open it. I tried using the quot;nonequot; 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?quot;Dave Petersonquot; wrote:

gt; First, do you mean apostrophe (') or quotation mark (quot;)?
gt;
gt; I don't think it's the wildcard. I think the length of the new string confuses
gt; excel and you see that message.
gt;
gt; You could use a macro that does its best to fix all the problems. But if there
gt; would be some of those error messages displayed, it goes back to try to fix
gt; them--cell by cell.
gt;
gt; Option Explicit
gt; Sub testme01()
gt;
gt; Dim FoundCell As Range
gt; Dim ConstCells As Range
gt; Dim BeforeStr As String
gt; Dim AfterStr As String
gt;
gt; 'double quote (quot;) is chr(34)
gt; BeforeStr = Chr(34) amp; Chr(34)
gt; AfterStr = Chr(34)
gt;
gt; With ActiveSheet
gt; Set ConstCells = Nothing
gt; On Error Resume Next
gt; Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
gt; xlTextValues)
gt; On Error GoTo 0
gt;
gt; If ConstCells Is Nothing Then
gt; MsgBox quot;Select some cells in the used rangequot;
gt; Exit Sub
gt; End If
gt;
gt; With ConstCells
gt; 'get as many as we can in one step
gt; .Replace what:=BeforeStr, Replacement:=AfterStr, _
gt; lookat:=xlPart, SearchOrder:=xlByRows
gt;
gt; Do
gt; Set FoundCell = .Cells.Find(what:=BeforeStr, _
gt; after:=.Cells(1), _
gt; LookIn:=xlValues, _
gt; lookat:=xlPart, _
gt; SearchOrder:=xlByRows, _
gt; searchdirection:=xlNext, _
gt; MatchCase:=False)
gt;
gt; If FoundCell Is Nothing Then
gt; 'done, get out!
gt; Exit Do
gt; End If
gt; FoundCell.Value _
gt; = Replace(FoundCell.Value, BeforeStr, AfterStr)
gt; Loop
gt; End With
gt; End With
gt; End Sub
gt;
gt; If you're using xl97, change that Replace() to application.substitute()
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt; Ps. Try it against a copy of your data--just in case!
gt;
gt;
gt; mawmawball wrote:
gt; gt;
gt; gt; Hello,
gt; gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt; gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt; gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt; gt;
gt; gt; I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
gt; gt; too longquot; error message. I get the message if I try to find and replace them
gt; gt; singularly and when I use the replace all feature.
gt; gt;
gt; gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt; gt; I've searched high and low and can't even find a reference to the error
gt; gt; message anywhere.
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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:

quot;Mawmawball's answer will be coming soonquot;
mawmawball wrote:
gt;
gt; Thanks for the help. I got all the apostrophe's off the sheet. The trouble
gt; is, they reappeared the next time I opened the spreadsheet. I'm working with
gt; a text delimited file. I've tried using the quot; text qualifier, and get 1
gt; extra apostrophe when I open it. I tried using the quot;nonequot; text qualifier and
gt; got 2 extra apostrophes when I opened it. This problem is driving me nuttier
gt; than I already am lol. Could you tell me what I'm doing wrong here?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; First, do you mean apostrophe (') or quotation mark (quot;)?
gt; gt;
gt; gt; I don't think it's the wildcard. I think the length of the new string confuses
gt; gt; excel and you see that message.
gt; gt;
gt; gt; You could use a macro that does its best to fix all the problems. But if there
gt; gt; would be some of those error messages displayed, it goes back to try to fix
gt; gt; them--cell by cell.
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme01()
gt; gt;
gt; gt; Dim FoundCell As Range
gt; gt; Dim ConstCells As Range
gt; gt; Dim BeforeStr As String
gt; gt; Dim AfterStr As String
gt; gt;
gt; gt; 'double quote (quot;) is chr(34)
gt; gt; BeforeStr = Chr(34) amp; Chr(34)
gt; gt; AfterStr = Chr(34)
gt; gt;
gt; gt; With ActiveSheet
gt; gt; Set ConstCells = Nothing
gt; gt; On Error Resume Next
gt; gt; Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
gt; gt; xlTextValues)
gt; gt; On Error GoTo 0
gt; gt;
gt; gt; If ConstCells Is Nothing Then
gt; gt; MsgBox quot;Select some cells in the used rangequot;
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; With ConstCells
gt; gt; 'get as many as we can in one step
gt; gt; .Replace what:=BeforeStr, Replacement:=AfterStr, _
gt; gt; lookat:=xlPart, SearchOrder:=xlByRows
gt; gt;
gt; gt; Do
gt; gt; Set FoundCell = .Cells.Find(what:=BeforeStr, _
gt; gt; after:=.Cells(1), _
gt; gt; LookIn:=xlValues, _
gt; gt; lookat:=xlPart, _
gt; gt; SearchOrder:=xlByRows, _
gt; gt; searchdirection:=xlNext, _
gt; gt; MatchCase:=False)
gt; gt;
gt; gt; If FoundCell Is Nothing Then
gt; gt; 'done, get out!
gt; gt; Exit Do
gt; gt; End If
gt; gt; FoundCell.Value _
gt; gt; = Replace(FoundCell.Value, BeforeStr, AfterStr)
gt; gt; Loop
gt; gt; End With
gt; gt; End With
gt; gt; End Sub
gt; gt;
gt; gt; If you're using xl97, change that Replace() to application.substitute()
gt; gt;
gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;
gt; gt; Ps. Try it against a copy of your data--just in case!
gt; gt;
gt; gt;
gt; gt; mawmawball wrote:
gt; gt; gt;
gt; gt; gt; Hello,
gt; gt; gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt; gt; gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt; gt; gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt; gt; gt;
gt; gt; gt; I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
gt; gt; gt; too longquot; error message. I get the message if I try to find and replace them
gt; gt; gt; singularly and when I use the replace all feature.
gt; gt; gt;
gt; gt; gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt; gt; gt; I've searched high and low and can't even find a reference to the error
gt; gt; gt; message anywhere.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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.

quot;Dave Petersonquot; wrote:

gt; Open the text delimited file and see if the apostrophes are there (maybe use
gt; Notepad).
gt;
gt; Maybe you can just clean up that text file--but watch out for strings like:
gt;
gt; quot;Mawmawball's answer will be coming soonquot;
gt;
gt;
gt;
gt; mawmawball wrote:
gt; gt;
gt; gt; Thanks for the help. I got all the apostrophe's off the sheet. The trouble
gt; gt; is, they reappeared the next time I opened the spreadsheet. I'm working with
gt; gt; a text delimited file. I've tried using the quot; text qualifier, and get 1
gt; gt; extra apostrophe when I open it. I tried using the quot;nonequot; text qualifier and
gt; gt; got 2 extra apostrophes when I opened it. This problem is driving me nuttier
gt; gt; than I already am lol. Could you tell me what I'm doing wrong here?
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; First, do you mean apostrophe (') or quotation mark (quot;)?
gt; gt; gt;
gt; gt; gt; I don't think it's the wildcard. I think the length of the new string confuses
gt; gt; gt; excel and you see that message.
gt; gt; gt;
gt; gt; gt; You could use a macro that does its best to fix all the problems. But if there
gt; gt; gt; would be some of those error messages displayed, it goes back to try to fix
gt; gt; gt; them--cell by cell.
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub testme01()
gt; gt; gt;
gt; gt; gt; Dim FoundCell As Range
gt; gt; gt; Dim ConstCells As Range
gt; gt; gt; Dim BeforeStr As String
gt; gt; gt; Dim AfterStr As String
gt; gt; gt;
gt; gt; gt; 'double quote (quot;) is chr(34)
gt; gt; gt; BeforeStr = Chr(34) amp; Chr(34)
gt; gt; gt; AfterStr = Chr(34)
gt; gt; gt;
gt; gt; gt; With ActiveSheet
gt; gt; gt; Set ConstCells = Nothing
gt; gt; gt; On Error Resume Next
gt; gt; gt; Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
gt; gt; gt; xlTextValues)
gt; gt; gt; On Error GoTo 0
gt; gt; gt;
gt; gt; gt; If ConstCells Is Nothing Then
gt; gt; gt; MsgBox quot;Select some cells in the used rangequot;
gt; gt; gt; Exit Sub
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; With ConstCells
gt; gt; gt; 'get as many as we can in one step
gt; gt; gt; .Replace what:=BeforeStr, Replacement:=AfterStr, _
gt; gt; gt; lookat:=xlPart, SearchOrder:=xlByRows
gt; gt; gt;
gt; gt; gt; Do
gt; gt; gt; Set FoundCell = .Cells.Find(what:=BeforeStr, _
gt; gt; gt; after:=.Cells(1), _
gt; gt; gt; LookIn:=xlValues, _
gt; gt; gt; lookat:=xlPart, _
gt; gt; gt; SearchOrder:=xlByRows, _
gt; gt; gt; searchdirection:=xlNext, _
gt; gt; gt; MatchCase:=False)
gt; gt; gt;
gt; gt; gt; If FoundCell Is Nothing Then
gt; gt; gt; 'done, get out!
gt; gt; gt; Exit Do
gt; gt; gt; End If
gt; gt; gt; FoundCell.Value _
gt; gt; gt; = Replace(FoundCell.Value, BeforeStr, AfterStr)
gt; gt; gt; Loop
gt; gt; gt; End With
gt; gt; gt; End With
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; If you're using xl97, change that Replace() to application.substitute()
gt; gt; gt;
gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt;
gt; gt; gt; Ps. Try it against a copy of your data--just in case!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; mawmawball wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Hello,
gt; gt; gt; gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt; gt; gt; gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt; gt; gt; gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt; gt; gt; gt;
gt; gt; gt; gt; I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
gt; gt; gt; gt; too longquot; error message. I get the message if I try to find and replace them
gt; gt; gt; gt; singularly and when I use the replace all feature.
gt; gt; gt; gt;
gt; gt; gt; gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt; gt; gt; gt; I've searched high and low and can't even find a reference to the error
gt; gt; gt; gt; message anywhere.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

If they're multiplying in the .CSV file, then I'd look at the thing that creates
the .CSV file.
mawmawball wrote:
gt;
gt; Hi Dave,
gt; I opened them in Notepad and they're definitely there. I'm cleaning them up
gt; again in Notepad. But I definitely need to find out why they are
gt; multiplying. I use these text delimited files quite often and this is the
gt; first time I've encountered this problem.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Open the text delimited file and see if the apostrophes are there (maybe use
gt; gt; Notepad).
gt; gt;
gt; gt; Maybe you can just clean up that text file--but watch out for strings like:
gt; gt;
gt; gt; quot;Mawmawball's answer will be coming soonquot;
gt; gt;
gt; gt;
gt; gt;
gt; gt; mawmawball wrote:
gt; gt; gt;
gt; gt; gt; Thanks for the help. I got all the apostrophe's off the sheet. The trouble
gt; gt; gt; is, they reappeared the next time I opened the spreadsheet. I'm working with
gt; gt; gt; a text delimited file. I've tried using the quot; text qualifier, and get 1
gt; gt; gt; extra apostrophe when I open it. I tried using the quot;nonequot; text qualifier and
gt; gt; gt; got 2 extra apostrophes when I opened it. This problem is driving me nuttier
gt; gt; gt; than I already am lol. Could you tell me what I'm doing wrong here?
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; First, do you mean apostrophe (') or quotation mark (quot;)?
gt; gt; gt; gt;
gt; gt; gt; gt; I don't think it's the wildcard. I think the length of the new string confuses
gt; gt; gt; gt; excel and you see that message.
gt; gt; gt; gt;
gt; gt; gt; gt; You could use a macro that does its best to fix all the problems. But if there
gt; gt; gt; gt; would be some of those error messages displayed, it goes back to try to fix
gt; gt; gt; gt; them--cell by cell.
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Sub testme01()
gt; gt; gt; gt;
gt; gt; gt; gt; Dim FoundCell As Range
gt; gt; gt; gt; Dim ConstCells As Range
gt; gt; gt; gt; Dim BeforeStr As String
gt; gt; gt; gt; Dim AfterStr As String
gt; gt; gt; gt;
gt; gt; gt; gt; 'double quote (quot;) is chr(34)
gt; gt; gt; gt; BeforeStr = Chr(34) amp; Chr(34)
gt; gt; gt; gt; AfterStr = Chr(34)
gt; gt; gt; gt;
gt; gt; gt; gt; With ActiveSheet
gt; gt; gt; gt; Set ConstCells = Nothing
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
gt; gt; gt; gt; xlTextValues)
gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt;
gt; gt; gt; gt; If ConstCells Is Nothing Then
gt; gt; gt; gt; MsgBox quot;Select some cells in the used rangequot;
gt; gt; gt; gt; Exit Sub
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; With ConstCells
gt; gt; gt; gt; 'get as many as we can in one step
gt; gt; gt; gt; .Replace what:=BeforeStr, Replacement:=AfterStr, _
gt; gt; gt; gt; lookat:=xlPart, SearchOrder:=xlByRows
gt; gt; gt; gt;
gt; gt; gt; gt; Do
gt; gt; gt; gt; Set FoundCell = .Cells.Find(what:=BeforeStr, _
gt; gt; gt; gt; after:=.Cells(1), _
gt; gt; gt; gt; LookIn:=xlValues, _
gt; gt; gt; gt; lookat:=xlPart, _
gt; gt; gt; gt; SearchOrder:=xlByRows, _
gt; gt; gt; gt; searchdirection:=xlNext, _
gt; gt; gt; gt; MatchCase:=False)
gt; gt; gt; gt;
gt; gt; gt; gt; If FoundCell Is Nothing Then
gt; gt; gt; gt; 'done, get out!
gt; gt; gt; gt; Exit Do
gt; gt; gt; gt; End If
gt; gt; gt; gt; FoundCell.Value _
gt; gt; gt; gt; = Replace(FoundCell.Value, BeforeStr, AfterStr)
gt; gt; gt; gt; Loop
gt; gt; gt; gt; End With
gt; gt; gt; gt; End With
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; If you're using xl97, change that Replace() to application.substitute()
gt; gt; gt; gt;
gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt;
gt; gt; gt; gt; Ps. Try it against a copy of your data--just in case!
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; mawmawball wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hello,
gt; gt; gt; gt; gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt; gt; gt; gt; gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt; gt; gt; gt; gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
gt; gt; gt; gt; gt; too longquot; error message. I get the message if I try to find and replace them
gt; gt; gt; gt; gt; singularly and when I use the replace all feature.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt; gt; gt; gt; gt; I've searched high and low and can't even find a reference to the error
gt; gt; gt; gt; gt; message anywhere.
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Could the extra apostrophe come from having
Toolsgt;Optionsgt;Transitiongt;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 quot; if right-aligned.Gord Dibben Excel MVP

On Wed, 21 Dec 2005 11:16:28 -0800, quot;mawmawballquot;
gt; wrote:

gt;Thanks for the help. I got all the apostrophe's off the sheet. The trouble
gt;is, they reappeared the next time I opened the spreadsheet. I'm working with
gt;a text delimited file. I've tried using the quot; text qualifier, and get 1
gt;extra apostrophe when I open it. I tried using the quot;nonequot; text qualifier and
gt;got 2 extra apostrophes when I opened it. This problem is driving me nuttier
gt;than I already am lol. Could you tell me what I'm doing wrong here?
gt;
gt;
gt;quot;Dave Petersonquot; wrote:
gt;
gt;gt; First, do you mean apostrophe (') or quotation mark (quot;)?
gt;gt;
gt;gt; I don't think it's the wildcard. I think the length of the new string confuses
gt;gt; excel and you see that message.
gt;gt;
gt;gt; You could use a macro that does its best to fix all the problems. But if there
gt;gt; would be some of those error messages displayed, it goes back to try to fix
gt;gt; them--cell by cell.
gt;gt;
gt;gt; Option Explicit
gt;gt; Sub testme01()
gt;gt;
gt;gt; Dim FoundCell As Range
gt;gt; Dim ConstCells As Range
gt;gt; Dim BeforeStr As String
gt;gt; Dim AfterStr As String
gt;gt;
gt;gt; 'double quote (quot;) is chr(34)
gt;gt; BeforeStr = Chr(34) amp; Chr(34)
gt;gt; AfterStr = Chr(34)
gt;gt;
gt;gt; With ActiveSheet
gt;gt; Set ConstCells = Nothing
gt;gt; On Error Resume Next
gt;gt; Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
gt;gt; xlTextValues)
gt;gt; On Error GoTo 0
gt;gt;
gt;gt; If ConstCells Is Nothing Then
gt;gt; MsgBox quot;Select some cells in the used rangequot;
gt;gt; Exit Sub
gt;gt; End If
gt;gt;
gt;gt; With ConstCells
gt;gt; 'get as many as we can in one step
gt;gt; .Replace what:=BeforeStr, Replacement:=AfterStr, _
gt;gt; lookat:=xlPart, SearchOrder:=xlByRows
gt;gt;
gt;gt; Do
gt;gt; Set FoundCell = .Cells.Find(what:=BeforeStr, _
gt;gt; after:=.Cells(1), _
gt;gt; LookIn:=xlValues, _
gt;gt; lookat:=xlPart, _
gt;gt; SearchOrder:=xlByRows, _
gt;gt; searchdirection:=xlNext, _
gt;gt; MatchCase:=False)
gt;gt;
gt;gt; If FoundCell Is Nothing Then
gt;gt; 'done, get out!
gt;gt; Exit Do
gt;gt; End If
gt;gt; FoundCell.Value _
gt;gt; = Replace(FoundCell.Value, BeforeStr, AfterStr)
gt;gt; Loop
gt;gt; End With
gt;gt; End With
gt;gt; End Sub
gt;gt;
gt;gt; If you're using xl97, change that Replace() to application.substitute()
gt;gt;
gt;gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt;gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;gt;
gt;gt; Ps. Try it against a copy of your data--just in case!
gt;gt;
gt;gt;
gt;gt; mawmawball wrote:
gt;gt; gt;
gt;gt; gt; Hello,
gt;gt; gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt;gt; gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt;gt; gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt;gt; gt;
gt;gt; gt; I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
gt;gt; gt; too longquot; error message. I get the message if I try to find and replace them
gt;gt; gt; singularly and when I use the replace all feature.
gt;gt; gt;
gt;gt; gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt;gt; gt; I've searched high and low and can't even find a reference to the error
gt;gt; gt; message anywhere.
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Dave Peterson
gt;gt;

Found the problem. There are a lot of measurements in the product text file.
Apparently, when I chose the quot; 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
quot;nonequot; 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.

quot;Gord Dibbenquot; wrote:

gt; Could the extra apostrophe come from having
gt; Toolsgt;Optionsgt;Transitiongt;Transition Navigation Keys checked?
gt;
gt; If it is checked you will see an apostrophe in any text cell that is
gt; left-aligned.
gt;
gt; Or a ^ if centered and a quot; if right-aligned.
gt;
gt;
gt; Gord Dibben Excel MVP
gt;
gt; On Wed, 21 Dec 2005 11:16:28 -0800, quot;mawmawballquot;
gt; gt; wrote:
gt;
gt; gt;Thanks for the help. I got all the apostrophe's off the sheet. The trouble
gt; gt;is, they reappeared the next time I opened the spreadsheet. I'm working with
gt; gt;a text delimited file. I've tried using the quot; text qualifier, and get 1
gt; gt;extra apostrophe when I open it. I tried using the quot;nonequot; text qualifier and
gt; gt;got 2 extra apostrophes when I opened it. This problem is driving me nuttier
gt; gt;than I already am lol. Could you tell me what I'm doing wrong here?
gt; gt;
gt; gt;
gt; gt;quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt;gt; First, do you mean apostrophe (') or quotation mark (quot;)?
gt; gt;gt;
gt; gt;gt; I don't think it's the wildcard. I think the length of the new string confuses
gt; gt;gt; excel and you see that message.
gt; gt;gt;
gt; gt;gt; You could use a macro that does its best to fix all the problems. But if there
gt; gt;gt; would be some of those error messages displayed, it goes back to try to fix
gt; gt;gt; them--cell by cell.
gt; gt;gt;
gt; gt;gt; Option Explicit
gt; gt;gt; Sub testme01()
gt; gt;gt;
gt; gt;gt; Dim FoundCell As Range
gt; gt;gt; Dim ConstCells As Range
gt; gt;gt; Dim BeforeStr As String
gt; gt;gt; Dim AfterStr As String
gt; gt;gt;
gt; gt;gt; 'double quote (quot;) is chr(34)
gt; gt;gt; BeforeStr = Chr(34) amp; Chr(34)
gt; gt;gt; AfterStr = Chr(34)
gt; gt;gt;
gt; gt;gt; With ActiveSheet
gt; gt;gt; Set ConstCells = Nothing
gt; gt;gt; On Error Resume Next
gt; gt;gt; Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
gt; gt;gt; xlTextValues)
gt; gt;gt; On Error GoTo 0
gt; gt;gt;
gt; gt;gt; If ConstCells Is Nothing Then
gt; gt;gt; MsgBox quot;Select some cells in the used rangequot;
gt; gt;gt; Exit Sub
gt; gt;gt; End If
gt; gt;gt;
gt; gt;gt; With ConstCells
gt; gt;gt; 'get as many as we can in one step
gt; gt;gt; .Replace what:=BeforeStr, Replacement:=AfterStr, _
gt; gt;gt; lookat:=xlPart, SearchOrder:=xlByRows
gt; gt;gt;
gt; gt;gt; Do
gt; gt;gt; Set FoundCell = .Cells.Find(what:=BeforeStr, _
gt; gt;gt; after:=.Cells(1), _
gt; gt;gt; LookIn:=xlValues, _
gt; gt;gt; lookat:=xlPart, _
gt; gt;gt; SearchOrder:=xlByRows, _
gt; gt;gt; searchdirection:=xlNext, _
gt; gt;gt; MatchCase:=False)
gt; gt;gt;
gt; gt;gt; If FoundCell Is Nothing Then
gt; gt;gt; 'done, get out!
gt; gt;gt; Exit Do
gt; gt;gt; End If
gt; gt;gt; FoundCell.Value _
gt; gt;gt; = Replace(FoundCell.Value, BeforeStr, AfterStr)
gt; gt;gt; Loop
gt; gt;gt; End With
gt; gt;gt; End With
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt; If you're using xl97, change that Replace() to application.substitute()
gt; gt;gt;
gt; gt;gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt;gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;gt;
gt; gt;gt; Ps. Try it against a copy of your data--just in case!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; mawmawball wrote:
gt; gt;gt; gt;
gt; gt;gt; gt; Hello,
gt; gt;gt; gt; I've got approximately 3500 products in my spreadsheet. I'm not sure why,
gt; gt;gt; gt; but the apostrophe signs are multiply. For example quot;quot;quot;quot;quot;quot;Product a is
gt; gt;gt; gt; 2quot;quot;x3quot;quot; long.quot;quot;quot;quot;quot;
gt; gt;gt; gt;
gt; gt;gt; gt; I've tried using find and replace with the ~quot; but I keep getting a quot;Formula
gt; gt;gt; gt; too longquot; error message. I get the message if I try to find and replace them
gt; gt;gt; gt; singularly and when I use the replace all feature.
gt; gt;gt; gt;
gt; gt;gt; gt; Does anyone know how I can remove these apostrophes from my spreadsheet?
gt; gt;gt; gt; I've searched high and low and can't even find a reference to the error
gt; gt;gt; gt; message anywhere.
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Dave Peterson
gt; gt;gt;
gt;