Function Problems

How to Randomly Rearrange Excel Text Values into Unique Strings

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to randomly rearrange multiple formula-generated text values (such as 52 items in a row) and combine them into a single, uniquely randomized text string.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Randomizing the order of multiple text values across cells and concatenating them into a single text string.
Observed behavior: Standard RAND functions do not easily achieve dynamic array sorting and text concatenation in older versions without additional nesting or macros.

Before you start

Ensure your spreadsheet software supports dynamic array functions, as the solution relies on modern formulas like RANDARRAY and SORTBY to work efficiently without VBA macros.

Solution 1Recommended

Use Dynamic Array Formulas (TEXTJOIN, SORTBY, RANDARRAY)

Leverage modern dynamic array functions to instantly shuffle your text values and combine them into one text string.

This method utilizes the SORTBY function to sort your range of text values based on an array of random numbers generated by RANDARRAY. Finally, TEXTJOIN combines these randomized values into a single text string separated by spaces or any delimiter of your choice.

Select the destination cell

Click on the cell where you want the new randomized text string to appear.

Enter the randomization formula

Type the formula: =TEXTJOIN(" ",,SORTBY(A1:AZ1,RANDARRAY(1,COUNTA(A1:AZ1)))) into the formula bar. Be sure to replace 'A1:AZ1' with the actual range containing your 52 text values.

Apply the formula

Press Enter. The text values will be instantly combined in a random order into that single cell.

Recalculate for a new order

To generate a completely new random sequence, force a sheet recalculation by pressing the F9 key on your keyboard.

Compatibility Note: Functions like RANDARRAY and SORTBY require software that supports dynamic arrays. If you see a #NAME? error, your current spreadsheet version might not support these specific functions.

Advanced Spreadsheet Functions

Use WPS Spreadsheet for Dynamic Array Formulas

WPS Spreadsheet fully supports advanced array functions, making it incredibly easy to randomize and manipulate large datasets without requiring complex macros.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your text values.
2. Input the array formula: Highlight your target cell and input the TEXTJOIN and SORTBY formula.
3. Generate the unique string: Press Enter to seamlessly generate your randomly rearranged text string.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Native support for modern dynamic array functions like TEXTJOIN and SORTBYLightweight, fast, and completely free to useBuilt-in advanced data analysis and seamless interface

Frequently Asked Questions

Why is the RANDARRAY function returning a #NAME? error?

This error occurs if you are using an older version of your spreadsheet software that does not support dynamic array functions. You will need to update to a newer version or use an alternative like WPS Spreadsheet which supports advanced formulas natively.

How can I change the separator between the randomized text values?

You can modify the first argument in the TEXTJOIN function. For example, changing the space (" ") to a comma and space (", ") will separate your text values with a comma in the final string.

Will the randomized string change automatically?

Yes, because RANDARRAY is a volatile function. Every time you edit any cell in the workbook or force the spreadsheet to recalculate (using the F9 key), a brand new random order will be generated.

Can I randomize a vertical column of text values instead of a horizontal row?

Absolutely. Just change the range references in the formula to your specific column (e.g., A1:A52) and adjust RANDARRAY to generate rows instead of columns: =TEXTJOIN(" ",,SORTBY(A1:A52,RANDARRAY(COUNTA(A1:A52),1))).

Excel & Data Problems