On 02/28/2007 03:43 PM, TerryJ wrote: > > > TerryJ wrote: >> >> Ok. I'm not pulling your leg. Search for .* and replace with & >> >> In the dialogue, press "More Options", select "Regular Expressions" and >> "Search in Values". >> >> If, perchance, that does not work, the macro in the following post will: >> http://www.oooforum.org/forum/viewtopic.phtml?p=121791#121791 >> > > I'm glad that worked. (Terry Allen asked why it worked and I'm replying to > the list.) > > Look at Help under Regular Expressions. Help is not completely accurate and > regular expressions in Calc at least (and probably OOo generally) is > idiosyncratic and partly broken. > > As you discovered, Find and Replace cannot find a leading apostrophe. It > can probably find an apostrophe at any other position in a cell. > > The combination I gave you, I believe, finds everything that can be found > and replaces the contents of the cell with what is found. Since the leading > apostrophe is not found, it is not included in the replacement.
That's an interesting & informative read - thanks! I had been exporting as a csv & sometimes cut+paste, but that resolves the problem. Although I'm a little confused as to why http://qa.openoffice.org/issues/show_bug.cgi?id=29054 is shown as "Closed/Invalid" [http://qa.openoffice.org/issues/show_bug.cgi?id=62596 is marked as a duplicate of the 2004 29054 bug]. It seems that this was discovered in 2004, yet the help documentation doesn't list it, and the "Regular Expressions" table doesn't either. Would this be a documentation issue, or an actual bug in the code? BTW: just searching for . (single . not .*) works as well. So if you have the following: A1 = '123 A2 = 4'56 A3 = Abc' A4 = "789 Regular Expressions checked. Search for: . Replace with: & Will find all 4 cells, but only the ' in '123 will be replaced. Does exactly the same in StarOffice 8 (I've been test driving that against 2.1 to see what if any differences there are). --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]