Hi Max,
Here is a UDF you could use.
It takes two arguments...
the first is the text to scramble
the second argument is optional.
Any entry there causes the function to scramble each time the sheet is calculated.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
' Apr 14, 2002 - Created - James Cone - San Francisco USA
' Scrambles the order of the data in a single-cell.
' Nov 02, 2003 - Last update
'--------------------------------------
Function SCRAMBLE(Optional ByRef UserText As Variant, _
Optional ByRef Everytime As Variant) As String
On Error GoTo Scorched
Dim i As Long
Dim Num As Long
Dim NewPosition As Long
Dim Temp As String
If IsMissing(UserText) Then
SCRAMBLE = "No data"
Exit Function
' No quotes automatically generates an error from the worksheet.
ElseIf IsError(UserText) Then
SCRAMBLE = "Error - try adding quote marks around your entry."
Exit Function
End If
Application.Volatile (Not IsMissing(Everytime))
If TypeName(UserText) = "Range" Then UserText = UserText(1).Value
Num = Len(UserText)
If Num > 0 Then
For i = 1 To Num
Temp = Mid$(UserText, i, 1)
NewPosition = Int(Num * Rnd + 1)
Mid$(UserText, i, 1) = Mid$(UserText, NewPosition, 1)
Mid$(UserText, NewPosition, 1) = Temp
Next ' i
SCRAMBLE = UserText
Else
SCRAMBLE = "No data" 'Can result from entering ""
End If
Exit Function
Scorched:
SCRAMBLE = "Error " & Err.Number
End Function
'--------------------
"Max" <
[email protected]>
wrote in message
Col A contains source text strings which may include leading zeros and
whitespaces (leading/in-between/trailing). I'm looking for ways to randomly
scramble col A's contents in col B preferably using only standard worksheet
functions or with a UDF.
Col A Col B (random scramble of col A)
1234 4321
099 000 9 09000
opx123 1o2px3
opx 123 o p123x
Thanks.
