Calc, macro to replace cell content

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.

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)

Change word to index

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

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

I think you can find useful information in:

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