0

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?

enter image description here

2
  • 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 Tyler
    Aug 4, 2021 at 16:52

1 Answer 1

0

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

snapshot


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

screenshot


This answer is based on answers to Randomize cells in Google Sheets and Remove line break within cell google spreadsheet in StackOverflow.

3
  • 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.
    – DoraeP
    Sep 23, 2021 at 14:50
  • 1
    Refer the updated answer. BTW, if this answer is useful, you might consider accepting it.
    – Tedinoz
    Sep 24, 2021 at 1:12
  • Wow!!!!!!! The best! It's working. THANK YOU SO MUCH!!!!
    – DoraeP
    Sep 24, 2021 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.