Calc, macro to replace cell content

[leica69]

Hi,
I need to use a LO Calc file to manage content I recieved from a mySQL database.
I need to search/replace some cell content in a column.
ie: “car” → 1; “bus” → 2, …
I have over 500 replacements to make so I want to use a macro but I don’t know how to work with macros.
What I’d love is something like

function myReplace(search, replace, column){
   someReplaceFunction(search, replace, column);
}
myReplace("car", "1","A");
myReplace("bus", "2","A");

Writing 500 lines to call the function is not a problem, I can automate this.

I hope somebody ca help me.

[JohnSUN]

You can do it with simple actions:

1. Create table with 500 cells (“air”,“car”,“bus”, etc) where position (offset from first cell) of word is index (1,2,3, etc.). Set name for this range, for example “aaa”

2. On sheet with imported data create temporary column with formula in second row

   =MATCH(A2;aaa;0)

3. Extend this formula to the end of the sheet (Ctrl+Shift+End, Ctrl+D)

4. Just copy this column and paste it as numbers over column A (right click - Paste only - Number)

5. Remove temporary column

The first steps are shown in the animation (without steps 4 and 5 - sorry, did not have time to finish)

[leica69]

THANKS! Great solution, easy and elegant. Works like a charm.

[mariosv]

Have you tried recording a macro?
To activate the option: Menu/Tools/LibreOffice/Advanced - Optional options

I think you can find useful information in:

Help getting started with LibreOffice Basic

Hello, I want to be able to open a document in LibreOffice Writer, get a pop-up dialog asking for some information (line edits, a combobox, two radio buttons and a multi line edit). This information should then be inserted into the document (for the combobox and the radio buttons it should choose between different text versions). I think this should be rather simple, and perhaps it’s because of that that I haven’t found any useful information or tutorials on the theme. I have found this page …

[leica69]

Hi,
yes, I’ve tried but I get 45 lines of code that would be hell to update 500 times.