How to randomize words contained in a cell separated by Alt+Enter [ char(10 ] , like a new line. I found a formula but what it does is create a range of columns first. But I can't do it because there is also data below it. In the image, Column A is the data entry and Column B is the randomized data for each cell. So for example, at cell A2, the words are randomized in cell B2. Can you help me?
-
I recommend that you share a link to a sample spreadsheet set up like your actual spreadsheet and containing enough realistic data for people here to work with. For instance, your post shows that you will always have exactly four words in each cell. Is that true of your real sheet? Your post also shows that each listing in each cell is one word in length. Is that true of your real sheet (or, for example, might one of the entries in a cell be two words like "solar system"? What does "there is data below it" mean? We only know what you show us, and every detail matters.– Erik TylerAug 4, 2021 at 16:52
-
Does this answer your question? In Google Spreadsheet, how do I make cells take a random string value from a list of strings?– TedinozAug 7, 2021 at 10:01
1 Answer
Try this formula in cell B2, copy down as required
=substitute(join(",",query({transpose(split(IF(REGEXMATCH(A2, "\r\n"),REGEXREPLACE(A2, "(\r\n)", ","),REGEXREPLACE(A2, "(\n)", ",")),",")), arrayformula(randbetween(0, 100000000000000000000 + row($F$1:$F$4)))}, "select Col1 order by Col2", 0)),",",char(10))
The reference to row($F$1:$F$4)
is an empty array of four cells which is used by the RANDBETWEEN
function. Any four contiguous cells can be substituted.
Example
UPDATE: variable number of words per cell
Try this formula in cell B2, copy down as required:
=substitute(join(",",query({transpose(split(IF(REGEXMATCH(A2, "\r\n"),REGEXREPLACE(A2, "(\r\n)", ","),REGEXREPLACE(A2, "(\n)", ",")),",")), arrayformula(randbetween(0, 100000000000000000000 + row(indirect("$G$1:$G$"&counta(SPLIT(A2,CHAR(10)))))))}, "select Col1 order by Col2", 0)),",",char(10))
The word count in Column C is purely for information, it does not form part of the formula.
Sample
This answer is based on answers to Randomize cells in Google Sheets and Remove line break within cell google spreadsheet in StackOverflow.
-
Thank you so much for this. This is very helpful. You really did put a lot of work here. But sorry if I haven't pointed it out at first. Maybe I forgot as well. Sometimes, there could be only 2, 3 or more than 4 data options. And I see that in the formula you made it is only useful when there are only 4 data options. Is there any way this could be applicable when there are 3, 5 or more data options to randomize? Thank you so much really.– DoraePSep 23, 2021 at 14:50
-
1Refer the updated answer. BTW, if this answer is useful, you might consider accepting it.– TedinozSep 24, 2021 at 1:12
-