Shuffling words in a list or range on Google Sheets

Other Questions Google Sheets
1

I’m trying to mix up words in Google Sheets. I have two setups:

  1. A cell with words split by commas (like cat, dog, bird, fish)
  2. Words in separate cells, not next to each other (like A1: cat, C1: dog, F1: bird, H1: fish)

I want a formula to shuffle these words automatically. I’m doing this because I’m comparing two sentences and finding mismatched words.

Here’s what I have now:

Original: The quick brown fox jumps over the lazy dog
Compare:  The _____ brown _____ jumps over the _____ _____

=SPLIT(REGEXREPLACE(A1, "[^A-Za-z ']", ""), " ")
=SPLIT(REGEXREPLACE(B1, "[^A-Za-z _']", ""), " ")
=IF(C1 <> D1, C1, "")

This gives me the different words, but they’re in order. How can I randomize them?

2

hey there! shuffling words can be tricky in sheets. have you tried the RANDBETWEEN function? it might help randomize your list. something like =INDEX(A1:A4, RANDBETWEEN(1,4)) could work for separate cells. for comma-separated words, maybe split them first then use a similar approach? Hope this helps!

3

I’ve tackled a similar challenge before, and here’s what worked for me. For the comma-separated list, you can use a combination of SPLIT and ARRAYFORMULA with RANDARRAY. Something like:

=ARRAYFORMULA(INDEX(SPLIT(A1, “,”), RANDARRAY(COUNTA(SPLIT(A1, “,”)), 1, 1, COUNTA(SPLIT(A1, “,”)), TRUE)))

For words in separate cells, you could use:

=INDEX(FILTER($A$1:$Z$1, NOT(ISBLANK($A$1:$Z$1))), RANDARRAY(COUNTA($A$1:$Z$1), 1, 1, COUNTA($A$1:$Z$1), TRUE))

These formulas will dynamically shuffle your words each time the sheet recalculates. Just make sure to adjust the cell ranges to match your specific setup. It took me some trial and error, but these solutions have been reliable for my projects.

4

I’ve encountered this issue before when working on language learning flashcards. For your comma-separated list, you could try this formula:

=JOIN(", ", SORT(SPLIT(A1, ", "), RANDARRAY(COUNTA(SPLIT(A1, ", ")))))

As for words in separate cells, this might work:

=ARRAYFORMULA(INDEX(FILTER(A1:Z1, NOT(ISBLANK(A1:Z1))), SORT(SEQUENCE(COUNTA(A1:Z1)), RANDARRAY(COUNTA(A1:Z1)))))

These should randomize your words effectively. Just replace A1 or A1:Z1 with your actual cell ranges. Remember to re-run the formula if you need a new shuffle, as it’s not dynamic. Hope this helps with your sentence comparison project!