Formula to Replace Words in a Cell

[joewell]

Hi, I'm making an English teacher making quizzes based on lists of sentences with translations.

I want to make one formula that takes the text string of another cell and randomly replaces one word with an underscore ____.

I'd also like to make a formula that replaces all the words with underscores.

Examples below.

[I know it's possible to find words by counting spaces, my bigger challenge replace the found words.]


[TABLE="******* 581"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Input Sentence[/TD]
[TD]Output Question[/TD]
[/TR]
[TR]
[TD]I am happy.[/TD]
[TD]I ____ happy.[/TD]
[/TR]
[TR]
[TD]We all enjoy our work.[/TD]
[TD]We all _____ our work.[/TD]
[/TR]
[TR]
[TD]The team is very good.[/TD]
[TD]The team is ______ good.[/TD]
[/TR]
[TR]
[TD]It is my favorite.[/TD]
[TD]____ ____ ____ ____[/TD]
[/TR]
[TR]
[TD]They didn't come in yet.[/TD]
[TD]____ _____ ____ ____ _____.[/TD]
[/TR]
[TR]
[TD]Why are you going there with them?[/TD]
[TD]_____ ______ _____ ____ ____ ____ ____?[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum!

This might help. Copy C2 done as far as necessary.

	A	B	C
I am happy.	am
We all enjoy our work.	enjoy
The team is very good.	very
It is my favorite.
They didn't come in yet.
Why are you going there with them?

<colgroup><col style="******* 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Input Sentence[/TD]
[TD="bgcolor: #FFF2CC"]replace this one word (no spaces)[/TD]
[TD="bgcolor: #FFF2CC"]Output Question[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA"]I _____ happy.[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #E2EFDA"]We all _____ our work.[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA"]The team is _____ good.[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]_____ _____ _____ _____ .[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]_____ _____ _____ _____ _____ .[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]_____ _____ _____ _____ _____ _____ _____ ?[/TD]

</tbody>

Sheet55​

[TABLE="******* 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="******* 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="******* 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="******* 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(ISBLANK(B2),REPT("_____ ",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)&RIGHT(A2),REPLACE(A2,SEARCH(B2,A2),LEN(B2)+1,"_____ "))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

I realise that you want a randomiser, so I set about the task. This was irritating because two things complicate matters. Single-letter words (such as 'I' and 'a') are difficult to isolate. And the punctuation at the end of the sentence is difficult to handle. You can see from these formulas that text operations in Excel are seldom easy.

Try this. Copy B9 and C9 down as far as necessary. Press f9 to re-calculate the workbook and the B9 formula will randomly pick one of the words in A9 or a blank. The blank will force C9 to replace all the words with underscores.

I created some additional text data so I could conduct experiments. I would suggest that you omit sentences that have punctuation in the middle. All sentences in Column A must end with punctuation.

reference:

https://exceljet.net/formula/extract-nth-word-from-text-string

<tbody>
</tbody>

	A	B	C
The teacher is English.
The teacher is a good teacher of English!
Am I not a good teacher?
a e i u o y.
They didn't come in yet.

<tbody>
[TD="align: center"]9[/TD]

[TD="bgcolor: #C6E0B4"]English[/TD]
[TD="bgcolor: #C6E0B4"]The teacher is _____.[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #C6E0B4"]English[/TD]
[TD="bgcolor: #C6E0B4"]The teacher is a good teacher of _____![/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"]_____ _____ _____ _____ _____ _____ ?[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #C6E0B4"]a[/TD]
[TD="bgcolor: #C6E0B4"]_____ e i u o y.[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #C6E0B4"][/TD]
[TD="bgcolor: #C6E0B4"]_____ _____ _____ _____ _____ .[/TD]

</tbody>

Sheet55​

Worksheet Formulas Cell <tbody> [TH="align: left"]Formula[/TH] [TH="bgcolor: #DAE7F5"]B9[/TH] [TD="align: left"]=IFERROR( TRIM(MID(SUBSTITUTE(LEFT(A9,LEN(A9)-1)," ",REPT(" ",LEN(LEFT(A9,LEN(A9)-1)))), (RANDBETWEEN(0,LEN(A9)-LEN(SUBSTITUTE(A9," ",""))+1)-1)*LEN(LEFT(A9,LEN(A9)-1))+1, LEN(LEFT(A9,LEN(A9)-1)))),"" )

<tbody>

[TH="bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=IF( B9="",REPT("_____ ",LEN(A9)-LEN(SUBSTITUTE(A9," ",""))+1)&RIGHT(A9),LEFT(REPLACE(LEFT(A9,LEN(A9)-1)&" ",SEARCH(IF(LEN(B9)=1,B9&" ",B9),LEFT(A9,LEN(A9)-1)&" "),LEN(B9)+1,"_____ "),LEN(REPLACE(LEFT(A9,LEN(A9)-1)&" ",SEARCH(IF(LEN(B9)=1,B9&" ",B9),LEFT(A9,LEN(A9)-1)&" "),LEN(B9)+1,"_____ "))-1)&RIGHT(A9) )[/TD]

</tbody>

[/TD]

</tbody>

 

[joewell]

Wow, this is amazing. I'm speechless.

Just one question while I digest this: in column B does it *randomly* leave it blank 50% of the time, truly randomly, or only a certain %? Because when I've played with this it is only blank a small % of the time, maybe under 25% just eyeing it.

 

[joewell]

And, I forgot to say: thank you very, very much.

 

[DRSteele]

You're welcome.

I'm quite sure that it is random. The function RANDBETWEEN here selects a number between 0 and a figure dictated by the number of words in the sentence (a figure whose value is determined by adding 1 to the number of spaces in the sentence). When a 0 is encountered, an error condition is reported by the formula, which is useful as an input to function IFERROR. When such an error arises, the formula in C9 will simply mete out the same number of 'underscores+space' as there are words in the sentence and then append it all with whatever punctuation concluded the original sentence.

So a sentence with five words will result in blanks one-sixth of the time. A sentence with four words will result in blanks one-fifth of the time.