Excel Short & Sweet Tip #6 (Shuffling a String) by David Hager

The ability to randomly scramble a string using Excel formulas does not appear to be possible without helper cells. So, a VBA procedure is needed to accomplish this.

Originally posted at:

https://www.mrexcel.com/forum/excel-questions/37340-word-scramble.html

this VBA function procedure uses a string as the argument and shuffles that string. Copy/paste this procedure into a module in the VBE.

Function ShuffleString(s As Variant)

On Error Resume Next

Dim CL As New Collection

Application.Volatile

ShuffleString = “”

Do Until CL.Count = Len(s)

R = Int(1 + Rnd * Len(s))

CL.Add R, CStr(R)

Loop

For i = 1 To CL.Count

ShuffleString = ShuffleString & Mid(s, CL(i), 1)

Next

End Function

So, the string in A1 is rearranged with =ShuffleString(A1) entered on the worksheet. For example, the string “evert” is shuffled to “rteve”.