How to randomize values in an excel cell?

Question

I'm trying to randomize a cell value for each cell. I want to keep the letters "tyot" at the front of the word, then randomize the 7 letters after it, which I did with this formula:

=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)

and after joining both cells with a formula like =A1&""&B1 I got the results that i want which it looks like this tyotXKWAE73. Now the next step is what i cant figure it out, which is randomizing the last six letter "tXKWAE73" and leaving "tyot"always on the front. Any ideas on how to do this last step?

So you have randomized six letters, and now you want to randomize them again? — GSerg, May 04 '18 at 17:20
In a different cell? in the same cell? Please show what you expected output should be. — Scott Craner, May 04 '18 at 17:26
tyot BJZZD63 tyotBJZZD63 tyot QBAPK10 tyotQBAPK10 tyot ARRNM94 tyotARRNM94 tyot JAFHH74 tyotJAFHH74 Every sequence ends with two numbers. I want the letters and numbers to switch around like tyotBJ6ZZD3 tyotQ1APK0 tyotARR4M9 tyotJA7HH4 — user3326780, May 04 '18 at 17:31
Possible duplicate of [Randomize word in VBA](https://stackoverflow.com/q/36557364/11683) — GSerg, May 04 '18 at 17:38

score 0 · Accepted Answer · answered May 04 '18 at 18:31

Enter the following functions in a standard module:

Public Function Shuffle(s As String) As String
    Dim ary(1 To 7) As String, ndex(1 To 7)
    For i = 1 To 7
        ary(i) = Mid(s, i, 1)
        ndex(i) = i
    Next i
    Call Shuffle2(ndex)
    Shuffle = ""
    For i = 1 To 7
        Shuffle = Shuffle & ary(ndex(i))
    Next i
End Function

Public Sub Shuffle2(InOut() As Variant)
    Dim i As Long, J As Long
    Dim tempF As Double, Temp As Variant

    Hi = UBound(InOut)
    Low = LBound(InOut)
    ReDim Helper(Low To Hi) As Double
    Randomize

    For i = Low To Hi
        Helper(i) = Rnd
    Next i

    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        J = J \ 2
    Loop
End Sub

Then in B1 enter:

=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)

In B2 enter:

="tyot" & shuffle(B$1)

and copy downward:

Each entry in column B is a "randomization" of B1 with a fixed prefix.

How to randomize values in an excel cell?

1 Answers1