How to mix up digits into a new number?

[Ed]

I can put single digits into separate columns and concatenate them into one
number. Is there a worksheet function or formula that will randomly mix up
those same digits into a different concatenated numbers? EX: turning "123"
into "231"?

Ed

 

[kounoike]

I'm not sure if i get it correctly, presume A1 has a value "123", then will
=randnum(A1) get what you want?

Function randnum(rng As Range)
Dim a, b
Dim i As Long, l As Long, n As Long, up As Long
ReDim a(Len(rng.Value) - 1)
For i = 0 To UBound(a)
a(i) = Mid(rng.Value, i + 1, 1)
Next
up = UBound(a)
ReDim b(up)
i = 0
Do While (i <= UBound(a))
Randomize
n = Int((up + 1) * Rnd)
b(i) = a(n)
i = i + 1
For l = n To up - 1
a(l) = a(l + 1)
Next
up = up - 1
Loop
randnum = Val(Join(b))
End Function

keizi

 

[Ed]

Thank you!!

I'm not sure if i get it correctly, presume A1 has a value "123", then
will =randnum(A1) get what you want?

Function randnum(rng As Range)
Dim a, b
Dim i As Long, l As Long, n As Long, up As Long
ReDim a(Len(rng.Value) - 1)
For i = 0 To UBound(a)
a(i) = Mid(rng.Value, i + 1, 1)
Next
up = UBound(a)
ReDim b(up)
i = 0
Do While (i <= UBound(a))
Randomize
n = Int((up + 1) * Rnd)
b(i) = a(n)
i = i + 1
For l = n To up - 1
a(l) = a(l + 1)
Next
up = up - 1
Loop
randnum = Val(Join(b))
End Function

keizi

 

[kounoike]

If the orignal number is like "120", this always returns as number, so you
could'nt get the number like "012".
if you want to get a number like this,

change
randnum = Val(Join(b))
to
randnum = Join(b, "")

then, this will return the number as string.

keizi