Randomize numbers in cell?

[dsal24]

I have a column of 10 digit numbers I would like to randomize. How can I quickly do this?

For example:

1234567890 to -> 1852634790

 

[Worf]

Welcome to the Board

Team Objectives

*	J
28	1234567890
29	1 4 5 8 9 0 7 6 2 3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:105px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula J29 =showshuff(J28) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

' in a module
Function showshuff$(s$)
Dim v, charr
v = StrConv(s, vbUnicode)
charr = Split(Left(v, Len(v) - 1), vbNullChar)
showshuff = Shuffled(charr)
End Function

Function Shuffled$(InArray)
 Dim N As Long, Temp, J&
 Randomize
 For N = LBound(InArray) To UBound(InArray)
     J = CLng(((UBound(InArray) - N) * Rnd) + N)
     If N <> J Then
         Temp = InArray(N)
         InArray(N) = InArray(J)
         InArray(J) = Temp
     End If
 Next N
 Shuffled = Join(InArray)
End Function

 

[Rick Rothstein]

' in a module
Function showshuff$(s$)
Dim v, charr
v = StrConv(s, vbUnicode)
charr = Split(Left(v, Len(v) - 1), vbNullChar)
showshuff = Shuffled(charr)
End Function

Function Shuffled$(InArray)
 Dim N As Long, Temp, J&
 Randomize
 For N = LBound(InArray) To UBound(InArray)
     J = CLng(((UBound(InArray) - N) * Rnd) + N)
     If N <> J Then
         Temp = InArray(N)
         InArray(N) = InArray(J)
         InArray(J) = Temp
     End If
 Next N
 Shuffled = Join(InArray)
End Function

You can do this with just a single function working directly with the characters passed into the function (there is no need to create an array of individual characters just to shuffle them around)...

Function Shuffle(ByVal S As String) As String
  Dim N As Long, Idx As Long, Temp As String
[B][COLOR=#0000ff]  Static IsRandomized As Boolean
  If Not IsRandomized Then
    Randomize
    IsRandomized = True
  End If[/COLOR][/B]
  For N = Len(S) To 1 Step -1
    Idx = Int(N * Rnd + 1)
    Temp = Mid(S, Idx, 1)
    Mid(S, Idx) = Mid(S, N, 1)
    Mid(S, N) = Temp
  Next
  Shuffle = S
End Function

Note the section highlighted in blue... you get a better mix of randomized numbers if the Randomize function is run only once per session, which the code in blue guarantees will happen. See my mini-blog article here for more details...

[h=3]VB's Randomize Function Should Be Run Only Once Per Session[/h]