Row by row find and replace words that match content of adjacent cells

[SteveLibreoffice]

I have a tab separated spreadsheet that I am working on in LibreOffice Calc. I am using it to create a set of language learning flashcards. It has about 4000 rows.

One column contains an example sentence. The next column has a key word that is found in that sentence. For example:

The dog jumped over the fox      |  jumped
The cat ate the food             |  ate
The fish swam in the bowl        |  swam

What I want to do is a one time search and replace function, that goes through each row, matches the key word in the second column with where it appears in the first column, and then replaces it with underscore characters. The end result should look like this:fish

The dog _____ over the fox       |  jumped
The cat _____ the mouse           |  ate
The fish _____ in the bowl       |  swam

Note that the underscore characters can be a consistent length, such as five underscores long like in the example above. The length of the underline does not have to match the length of the word being replaced.

Is this possible to do in LibreOffice Calc?

[PYS]

Hi

You can use the SUBSTITUTE function

substitute.ods

HTH - Regards

[LeroyG]

Taked the file @PYS, and added the functions REPT and LEN. Assuming the answer will be handwritten, *2 was added to give more space.

C1: =SUBSTITUTE(A1;B1;REPT("_";LEN(B1)*2))

Sample file flashcards.ods.

LibreOffice Help on SUBSTITUTE, REPT and LEN.

Edit your question if you want to add more information; also can comment an answer (Add Answer is reserved for solutions).

Check the mark () to the left of the answer that solves your question.