Ask Your Question
0

Calc, macro to replace cell content [closed]

asked 2013-11-06 11:17:21 +0100

leica69 gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-13 05:43:55.611826

3 Answers

Sort by » oldest newest most voted
1

answered 2013-11-07 09:15:19 +0100

JohnSUN gravatar image

updated 2013-11-07 09:20:04 +0100

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

edit flag offensive delete link more

Comments

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

leica69 gravatar imageleica69 ( 2013-11-07 09:52:26 +0100 )edit
0

answered 2013-11-06 19:57:26 +0100

m.a.riosv gravatar image

updated 2013-11-06 19:57:59 +0100

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

I think you can find useful information in:
http://ask.libreoffice.org/en/question/9362/help-getting-started-with-libreoffice-basic/

edit flag offensive delete link more
0

answered 2013-11-06 23:18:39 +0100

leica69 gravatar image

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-11-06 11:17:21 +0100

Seen: 3,480 times

Last updated: Nov 07 '13