We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How can i search/replace the content in one column with content from another column? (libreoffice calc)

asked 2020-09-07 12:34:43 +0200

gomebuba1 gravatar image

I have a column of sentences, and another column of words. most of the sentences contain the the word in the second column. i would like to wrap this word inside {{c1:: }}, because i want to import the spreadsheet into a flashcard app.

This is what the spreadsheet currently looks like:

column 1 | column 2


i like to sing | to sing

this is a pen | pen

i want to eat | want

this is what i want it to look like:

column 1 | column 2


i like {{c1::to sing}} | to sing

this is a {{c1::pen}} | pen

i {{c1::want}} to eat | want

How can I do this in libreoffice calc?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2020-09-07 13:55:02 +0200

erAck gravatar image

updated 2020-09-07 14:26:22 +0200

Assuming your example data in A1:B3, in C1 enter


and then copy-paste that cell down until C3, or select C1:C3 and hit Ctrl+D. Note that if the content of B does not occur in A then no replacement is done and the original content of A is the result.

A little caveat: this REGEX() works only if the search expression does not contain regular expression metacharacters or operators (i.e. nothing of http://userguide.icu-project.org/stri... and following), but works fine if the search is only letters and spaces.

Otherwise, or rather anyway preferred because less computation overhead (yes, I like regex too much ;-)


which for this simple case does the same as the REGEX() call.

edit flag offensive delete link more


.. ach komm',wo ich es doch so schön kompliziert gemacht habe :-)

Opaque gravatar imageOpaque ( 2020-09-07 13:57:50 +0200 )edit

erAck mag halt REGEX, kann halt nicht jeder, ist auch besser so ... ;-)

newbie-02 gravatar imagenewbie-02 ( 2020-09-07 14:11:30 +0200 )edit

Sorry for switching to German - did not carefully watch the ask section I'm commenting in

Opaque gravatar imageOpaque ( 2020-09-07 14:17:36 +0200 )edit

answered 2020-09-07 14:07:42 +0200

newbie-02 gravatar image

hello @gomebuba1,

if i'd understand right it's quite easy:

just try this:

=REPLACE(B2;SEARCH(C2;B2;1);LEN(C2);"{{c1::" & C2 & "}}")

(throws #value! error if not found)

or this:

=IFERROR(REPLACE(B2;SEARCH(C2;B2;1);LEN(C2);"{{c1::" & C2 & "}}");B2 & " !!! search string not found !!!")

(provides meaningful error message)

formula in D2, assuming your data is in B2:C2, the formula will adapt on copy to other rows ...

happy hacking, enhance to your needs ...

P.S. 'solved marks' (click the grey circeled hook - ✓ - top left to the answer to turn it green) and 'likes' (click the upvote link - ^ - above it if you consider the answer 'good', - v - for the opposite) are welcome, please use the 'answer' button only! if you have an answer to the question, to add info edit the question or use comments,

edit flag offensive delete link more

answered 2020-09-07 13:46:01 +0200

Opaque gravatar image

updated 2020-09-07 13:51:44 +0200


not sure whether the following sample file contains what you are looking for:



  • The sample file uses a helpers column called Flashcard
  • Column Formula shows the formula used to create related value in column Flashcard
  • If you finally want get rid of the formula, use Data -> Calculate -> Formula to Value on column C (and you may want to delete columns A and B

Hope that helps.

edit flag offensive delete link more


The not-to-be-replaced portions don't need to be grouped and explicitly stated in the replacement, REGEX() returns the full text replacing only the match.

erAck gravatar imageerAck ( 2020-09-07 14:00:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-07 12:34:43 +0200

Seen: 441 times

Last updated: Sep 07 '20