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

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?

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

=REGEX(A1;B1;"{{c1::"&B1&"}}")

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 Regular Expressions - Old location of the ICU User Guide 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 :wink:

=SUBSTITUTE(A1;B1;"{{c1:"&B1&"}}")

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

… ach komm’,wo ich es doch so schön kompliziert gemacht habe :slight_smile:

erAck mag halt REGEX, kann halt nicht jeder, ist auch besser so … :wink:

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

Hello,

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

Flashcard-Formatting.ods

Remarks

  • 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.

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.

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,