I am not sure how much more of this you might need but I have been continuing to work on some English language resources. If they are not interesting or useful for you, just ignore them. I have had some positive feedback, however, so please humour me!

See ‘n’ Say

I created a Flash Card worksheet but I have called it See ‘n’ Say! What I have done is to take the list of words I created yesterday: the words that come from an earlier exercise. I turned that into a LOOKUP table and then created a new sheet to look like this:

Screen Shot 2014-04-10 at 04.03.28

Press the F9 key and the word in cell A4 changes RANDOMLY to another word in the list. The user only sees this worksheet and not the list or the formula,

=VLOOKUP(RANDBETWEEN(1,62),see_say_list!A2:B63,2,0).

Please note, because it is based on a random function, the same word might appear twice or even three times in succession … it does happen!

At the time of writing, there are just 62 words in the list. Expand the list as you go along and you will have a very powerful Flash Card, See ‘n’ Say resource.

Rearrange the Words

I haven’t automated any of this rearrange the words worksheet yet so what you see has all been done manually.

Take a sentence like this, the cat sat on the mat

Use Data Text to Columns, Delimited, Spaces to split the sentence into individual words in individual cells

Rearrange the words by dragging the cells around to give you something like this: on cat the sat mat the

As I was working with several sentences, I did all of this in one go, I did not do it one sentence at a time and I ended up with this, which I printed out for each student:

Screen Shot 2014-04-10 at 04.11.34

Crack the Code

As a boy I loved to crack codes so it should not be a surprise that I created a crack the code worksheet. All I did was to give a number to each letter in the alphabet: A = 1, B = 2 and so on.

Using another list of words, or even the same list I mentioned above, I split the words letter by letter by using a combination of the LEFT() and MID() functions, for example:

=LEFT(J18,1)

=MID($J18,M$17,1)

Then apple became a p p l e with each letter in its own cell

Then I used the following formula together with a LOOKUP range for the alphabet to change letters to numbers automatically:

=IFERROR(VLOOKUP(L18,$J$29:$K$54,2,0),””)

I then used CONCATENATE to recombine each word. For example

apple became a p p l e which became 1 16 16 12 5 … which I CONCATENATED into one cell like this:

=S18&” “&T18&” “&U18&” “&V18&” “&W18&” “&X18&” “&Y18

The complete worksheet looks like this:

Screen Shot 2014-04-10 at 04.17.51

Print that out for each student and they have no idea that all I do now, with any word up to seven letters long is to type a word in a cell and automatically Excel will use the code to generate the worksheet you see there. By the way, it would take only a short time to change this to allow for eight letter words, nine letter words … any number of letters in a word. I chose seven letters as my limit because I am dealing with beginners and not advanced learners.

You can even change the coding structure: not A = 1, B = 2 … but, for example, A = 6, B = 7 … or any other change you wish.

To prove a point, I have just spent one minute copying that worksheet and changing all of the words to give you these new codes, based on the same coding structure: A = 1, B = 2 … In this case, all of the words relate to bookkeeping and accounting:

Screen Shot 2014-04-10 at 04.23.10

Crack the Code Advanced

This is a crack the code and find the word exercise. In this case, I have changed the coding structure. A does not equal 1, B does not equal 2 … In this exercise, you have to find the values for A and B and so on and then find the words. This is not MI5 or CIA levels of coding, so think simple!

In this case, the words all relate to computers

Screen Shot 2014-04-10 at 04.35.24

 

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: