HOW TO CREATE JUMBLE WORDS IN EXCEL?

INTRODUCTION

You must have played WORD JUMBLE at any point of time.

The game is challenging, a good pass time and it increases your knowledge too.

In this article, we’ll learn the process of creating jumble words in Excel.



WHAT ARE JUMBLE WORDS?

JUMBLE means confusion.

Jumble words are the words with scrambled letters. For Example,

if we scramble MORNING , it can be NINGMOR or NIMORNG or MONINRG and there can be so many other possibilities.

HOW TO PLAY WORD JUMBLE?

WORD JUMBLE is a game in which one player JUMBLES the letters of any word which he knows and asks the other player or players to solve.

Now, as the letters are scrambled, it is not easy to solve these. So the player who solves these wins.

HOW EXCEL WOULD HELP IN CREATING JUMBLE WORDS?

Excel would help us to create jumble words within fractions of seconds.

You can enter the word and the result will be a newly formed JUMBLE which you can ask other players to solve.

STEPS TO CREATE A JUMBLE WORD

For creating a jumble word, we’ll have to follow a few steps.

  1. Separate the Letters.
  2. Rearrange them Randomly.

Let us discuss the steps in details.

STEP 1: SEPARATE THE LETTERS OF THE WORD TO BE JUMBLED

The best function to extract the letters from any word are LEFT, MID and RIGHT trio.

In our situation, as we’d like to automate most of the operations, MID FUNCTION would be most appropriate for us.

MID FUNCTION lets us extract the letters inside any word from any location.

You can click on this line for complete information about MID FUNCTION [RECOMMENDED] 
The syntax formula of the MID FUNCTION is
=MID( THE CELL CONTAINING THE TEXT OR TEXT,STARTING CHARACTER LOCATION, NUMBER OF CHARACTER TO BE EXTRACTED )
The practical usage can be seen further in this article.

FOLLOW THE STEPS TO SEPARATE THE COMPLETE WORD INTO THE LETTERS

  • Select the Cell where you would write the Word which needs to be jumbled. For our example we’ll take the cell as C2.
  • Now,double click in any of the cell where you want the first letter of the separated word. Let us take C4 for the result.

ENTER THE FORMULA IN ANY CELL
  • Enter the formula as =MID(ABSOLUTE REFERENCE TO CELL CONTAINING THE WORD,COLUMN(A1)-(COLUMN($A1)-1),1).
  • For our example , we’ll use the formula as =MID($C$2,COLUMN(A1)-(COLUMN($A1)-1),1) where C2 contains the word MORNING.
  • As we put this formula, letter M would appear in the cell.
THE RESULT OF THE FORMULA SHOWING THE FIRST LETTER M

  • Drag the formula to the right and all the letter will be separated in the adjacent columns as shown in the picture below.

DRAG THE FORMULA TO THE RIGHT TO GET ALL THE LETTERS
  • All the letters will be separated as shown below.
  • For now, we need the formulas to be removed on the base of these letters, so copy the letters by selecting them and paste them again as values.

  • The process is shown below.
COPY THE LETTERS, RIGHT CLICK AND CHOOSE PASTE VALUES ONLY

STEP 2: SEPARATE THE LETTERS OF THE WORD TO BE JUMBLED

After we have already separated the word, it is time to jumble them.

Whenever we want to create random choices, Excel provides us with RAND FUNCTION which will help us to randomize the letters.

FOLLOW THE STEPS TO RANDOMIZE THE LETTERS FOR THE FINAL JUMBLED WORD

  • Just in the next line to our newly separated letters, Enter the formula as =RAND().
ENTER RAND() FUNCTION FOR THE FIRST LETTER
  • It’ll create a random number in the cell.

Hold the small plus at the right bottom[Drag handle] of the cell and drag it to the right till the last letter column of the separated word, i.e. till G as shown in the picture below.

DRAG THE FORMULA TO THE RIGHT TO GET RANDOM NUMBERS FOR ALL THE LETTERS

Now, we need to freeze the Random word so select the letters, press CTRL+C to copy them and again RIGHT CLICK and choose PASTE SPECIAL as shown in the animation below.

COPY THE NUMBERS, RIGHT CLICK AND PASTE AS VALUES

For sorting, select the random number cells, go to DATA TAB, click SORT.

SELECT BOTH THE ROWS, LETTERS AND RANDOM NUMBERS

  • In the opened dialog box, CLICK SORT OPTIONS.

GO TO DATA TAB> SORT BUTTON> OPTIONS

  • Choose SORT LEFT TO RIGHT.
CHOOSE SORT LEFT TO RIGHT
  • Select ROW 5 which contains the RANDOM GENERATED NUMBERS.

CHOOSE RANDOM NUMBERS ROW AND SORT
  • Click SORT.
  • The numbers will be sorted as per the random numbers along with the letters of our word.

You are done.

Delete the helper row.

The final result looks like the one shown in the picture below.

RESULT

FAQ USING THE GIVEN JUMBLE WORDS FORMAT

CAN I PLACE THE WORD SEPARATING FORMULA ANYWHERE?

Yes , the formula given is the generalized one. But use the formula in the way it is shown. This formula will separate the given word into letters easily without much fuss.