[PLUG] OO.o Calc Question

Fred James fredjame at fredjame.cnc.net
Mon Mar 21 21:16:20 UTC 2011


Roderick A. Anderson wrote:
> Rich Shepard wrote:
>   
>>    Perhaps I'm not using the proper search string in the OO.o help and on
>> Google, but I cannot find how to replace a blank spreadsheet cell with "NA"
>> as the contents. Does anyone here know?
>>     
>
> I found here:
>
> http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=948
>
> This:
>
> Another is to turn the problem on its head. Lets say the last cell of 
> your sheet is E345. (You can find this by doing a CTL+END). Create a new 
> sheet put "." in A1 and copy the cell. In the range selection box 
> (that's the one next to f(x)) type A1:E345 and hit enter. Now hit CTL-V 
> and paste. You have now A1:E345 filled with dots. Now go the the 
> original CSV download. CTL-END then CTL-SHIFT_HOME to select the entire 
> contents; copy it and toggle over to the new dotty sheet. Now do a Space 
> Special (CTL-SHIFT-V) and check the "Skip Empty Cells" box. Job done. I 
> know its a manual exercise but it only takes 15 secs or so.
>
> Replace the '.' (dot) with NA.
>
> May be more work than you really want but I couldn't find an option to 
> display NA in empty cells in LibreOffice so I doubt it is in OOo either.
>
>
> \\||/
> Rod
>   
Roderick A Anderson
Pretty cool stuff.  Still for a number of CSV files with the same issue 
(replace ,"", with ,"NA",), a single SED file could be run against all 
the files ... could even be dropped into a simple script to do each file 
in a loop, preserving naming conventions.
But I do like that one ... I tested it and it does work.
Regards
Fred James




More information about the PLUG mailing list