# Randomize numbers in cell?

#### dsal24

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

For example:

1234567890 to -> 1852634790

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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>

 Cell Formula J29 =showshuff(J28)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Code:
``````' 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``````

Code:
``````' 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)...
Code:
``````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]

Last edited:

Replies
10
Views
346
Replies
2
Views
383
Replies
2
Views
185
Replies
5
Views
426
Replies
1
Views
174

1,206,921
Messages
6,075,586
Members
446,147
Latest member
homedecortips

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back