There's a good chance that a much better solution is out there, somewhere. But here's the not-beginner-friendly approach that I would take. Backup your files if you try this, you're almost certain to mess it up at least once.
1) Create a test spreadsheet in Libre Office with the sort of formula you're having trouble with and save it to the format that your spreadsheets are in. I made one with numbers in A1, A2, and A3, and then summed them.
2) unzip filename.xlsx
3) For me, this created the dirctory xl. The actual data is at xl/worksheets/sheet1.xml. If I open this file (I used emacs, but an xml editor would let you see it better) I can see the following string half way through it: SUM(A1:A3)
4) Now open one of your spreadsheets from excel in the same way and try to find the analogous formula. For this example, let's say you find of:SUM(A1:A3)
5) Cook up a sed command to transform the formula from the old format to the new format. For this example, you might try:
sed -i "s/of://g" sheet1.xml
The format here is "s/<find>/<replace>/g". If you post the old format and the new format, we can help you write this. Sed is very flexible with moving bits around and such, this is exactly the sort of case it's used for.
6) Once you get it to work for one file, write a bash script to do this to all of them. My bash is rusty, I'm sure this wouldn't run, but it gives you the idea. If you get to this point and need help writing a working script, let us know. And as before, do this on copies, be sure not to mess up your originals.
Code:
#!/bin/bash
cd ~/spreadsheets
mkdir tempdir
for i in `ls` do
unzip $i -d tempdir
rm $i
sed -i "s/of://g" tempdir/xl/worksheet/sheet1.xml
cd tempdir
zip -r ../$i ./*
cd ..
rm -r tempdir/*
done
So, if you're comfortable with the bash command line and scripting, the only hard part is finding the text to replace. Think of it as an exercise to help you get used to Linux
Open Office Formula Corrupts in Linux
f but how can I stop this happening in Linux?
