Word Scramble

jasgot

New Member
Joined
Jul 16, 2002
Messages
30
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
I want to make a word scramble for my Nephew.

Lacking a Scramble() function :) I looked at Rand() and made no progress.

I would like to take the word in column A and have it scrambled in column B. Like so.

dragonfly gfoadryln

I would also like to do it with two words;

Ice Cream rmciaeec

And one last requirement; keep font formatting. One letter will be red in word or phrase, the red letters will spell a clue about the scrambled words. ie If you read down the column, the red letters might spell 'fun at the zoo'

Any ideas? I can't seem to find a way to scramble or shuffle the letters.....
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
with just the "scrambling", you could use formulas. with the extra font requirement, this is definitely vba...in which case, care to be a bit more specific about how your data's set up etc...

paddy
 
Upvote 0
Hi jasgot:

As Paddy said, the first part is doable with the formulas. See the following worksheet simulation for dragonfly ...
Book1
ABCDEFGHIJ
1
2dragonflydragonfly
3randomnumbers714538296
4fdgoalrynfdgoalryn
5
Sheet6
</SPAN>

I have utilized the MRAND function from the MoreFunc add-in to create a dynamic variation. One can also choose to create a number of static arrays of the random numbers to create various alternates consisting of the characters in the base word.

As for preserving the font color of one of the characters, you will have to employ VBA.
 
Upvote 0
Wow! Who would of thought Excel counldn't do this on it's own.

Where do I get MoreFunc, and, If I use your equation in b2,c2,d2 ...., which all appear to be the same, they all generate a the first character, shouldn't column b have the first char and column c have the second char and so on?
 
Upvote 0
MRAND() gives me a #NUM! error

This is my cell:
=MRAND(14,1)

the result is #NUM!

There must be something wrong...
 
Upvote 0
Hi Jasgot,

I have created a Custom Function called (Scramble).

Paste this code in a standard module in your Workbook or Personal.xls:<pre>
Public Function scramble(s As Variant)

On Error Resume Next
Dim CL As New Collection
Application.Volatile
scramble = ""

Do Until CL.Count = Len(s)
R = Int(1 + Rnd * Len(s))
CL.Add R, CStr(R)
Loop

For I = 1 To CL.Count
scramble = scramble & Mid(s, CL(I), 1)
Next

End Function</pre>

If you want to randomlly change the Font color of a single charactere within a Scrambled string then you could place a Comandbutton (Comandbutton1)on the worksheet and assign it the following code:<pre>
Public Sub CommandButton1_Click()


For Each CEL In Me.Range("B3:B13").Cells
CEL.Font.Color = vbBlack
CEL.Value = scramble(CStr(CEL))
CEL.Characters(Int(1 + Rnd * Len(CEL)), 1).Font.Color = vbRed
Next


End Sub</pre>

Now everytime you hit the CommanButton,the Font Color of a single charactere in a specified String.This charactere changes at Random each time.


To illustrate this,See the following Sample Worksheet :
sheetcontrol.xls
ABCDE
1OriginalScrambled And********OriginalSrambled
2Font Color ChangedStringwith Formula
3JAAFARRFJAAAJAAFARAJFARA
4COBOSOOCSBJasgotsagtJo
5JUAN GONZALESSNNAGOEJZUA LYogi AnandaYidngnAo
6ARMANDONRMADAOPaddyDDPdday
7MOHAMEDEADMOHM 
8JOHN GIBBARDHAB NROIGBJD
9MARKAMKR
10CLAIRECAIRLE
11JoshohsJ
12MARK ROWRMAOWRK
13
14
Sheet3



Note that within each name in the Range B3:B12 there is actually a Random Charactere whose Font Color is Red.This changes every time the CommandButton is Clicked.
Unfortunately,when converted to HTML,these colored characteres don't show.


Hope this helps.

Jaafar.
This message was edited by rafaaj2000 on 2003-02-05 19:51
 
Upvote 0
Wow! Who would of thought Excel counldn't do this on it's own.

Where do I get MoreFunc, and, If I use your equation in b2,c2,d2 ...., which all appear to be the same, they all generate a the first character, shouldn't column b have the first char and column c have the second char and so on?
----------------------------
MRAND() gives me a #NUM! error

This is my cell:
=MRAND(14,1)

the result is #NUM!

There must be something wrong...

Hi jasgot:

The MoreFunc add-in can be downloaded from ...

http://longre.free/fr/english/

The formula in my post is an array formula -- you enter the formula in cell B3, highlite cells B3:J3, then do CTRL+SHIFT+ENTER (i.e. while holding the CTRL and SHIFT keys, press the ENTER key).

You may also want to readup about use of array formulas in Excel Help.
 
Upvote 0
Well done, Jaafar.

In Excel 97-SR2, the ME command in the following line results in a compile error:

For Each CEL In Me.("B3:B13").Cells

However, if you change ME to ActiveSheet, the code works perfectly.

Regards,

Mike
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top