Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
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) "mawmawball" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
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" mawmawball wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
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" wrote: 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" mawmawball wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
If they're multiplying in the .CSV file, then I'd look at the thing that creates
the .CSV file. mawmawball wrote: 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" wrote: 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" mawmawball wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
Could the extra apostrophe come from having
ToolsOptionsTransitionTransition 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 On Wed, 21 Dec 2005 11:16:28 -0800, "mawmawball" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find and replace the "
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. "Gord Dibben" wrote: Could the extra apostrophe come from having ToolsOptionsTransitionTransition 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 On Wed, 21 Dec 2005 11:16:28 -0800, "mawmawball" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace | Excel Worksheet Functions | |||
Find and Replace | Excel Discussion (Misc queries) | |||
find and replace, within workbook | Excel Discussion (Misc queries) | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
VB Find and Replace | Excel Worksheet Functions |