TerryJ wrote:
>
>
>
> Terry Allen-3 wrote:
>>
>> I hope I can explain this clearly:
>>
>> I use Calc (OOo 2.1, Windows XP Home, SP2) to open an .xls file created
>> by a FoxPro 2.4 (DOS) application. Don't ask why, it would take too
>> long. Some fields contain a ' (single quote or tick) at the beginning
>> of the content. From memory, this is how Lotus 123 indicated that what
>> followed should be treated as text, rather than a numerical value. I
>> need to remove these formatting marks so that dates are treated as dates
>> and numbers as numbers.
>>
>> I have tried Search and Replace, copying the ' and pasting it into the
>> "Search" field, but I receive a disheartening beep and "Search string
>> not found".
>>
>> I can manually remove then by using F2,Home,Delete,<Enter>, but that's a
>> bit pedestrian. I've also tried recording a macro to automate these
>> keystrokes, but that simply replaces the content of every cell with the
>> content of the one I used to make the recording.
>>
>> Can it be done, and if so, how?
>>
>> Terry
>>
>>
>
> 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.
--
View this message in context:
http://www.nabble.com/Removing-formatting-marks-from-Calc-cells-tf3307180.html#a9240958
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
