Alphabet Code Game

[James E Middleton]

I am looking for an easy was to make a alphabet code game for my students.

I want to give them words or sentences to 'decode'

For example; 2-1-3-11 = back

8-5 12-9-11-5-19 3-1-20-19. = He likes cats.

Something as simple as Col A 1-4: b,a,c,k outputs in Col B 1-4: 2,1,3,11

I don't mind formatting it after into something useable, however, if it was
possible:

In Row1 type the word or sentence, one letter in each cell, numbers are
output in Row2.

Thanks!

PS. if this can be done, is it possible to have the letters generate any
number as a code?

For example, a = 26, z = 1, or a = 10, b=20, etc

 

[RagDyeR]

Try this:

=CODE(LOWER(A1))-96


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am looking for an easy was to make a alphabet code game for my students.

I want to give them words or sentences to 'decode'

For example; 2-1-3-11 = back

8-5 12-9-11-5-19 3-1-20-19. = He likes cats.

Something as simple as Col A 1-4: b,a,c,k outputs in Col B 1-4: 2,1,3,11

I don't mind formatting it after into something useable, however, if it was
possible:

In Row1 type the word or sentence, one letter in each cell, numbers are
output in Row2.

Thanks!

PS. if this can be done, is it possible to have the letters generate any
number as a code?

For example, a = 26, z = 1, or a = 10, b=20, etc

 

[RagDyeR]

Missed the second part of your question.

Just play with the "-96" to get any number you wish.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Try this:

=CODE(LOWER(A1))-96


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am looking for an easy was to make a alphabet code game for my students.

I want to give them words or sentences to 'decode'

For example; 2-1-3-11 = back

8-5 12-9-11-5-19 3-1-20-19. = He likes cats.

Something as simple as Col A 1-4: b,a,c,k outputs in Col B 1-4: 2,1,3,11

I don't mind formatting it after into something useable, however, if it was
possible:

In Row1 type the word or sentence, one letter in each cell, numbers are
output in Row2.

Thanks!

PS. if this can be done, is it possible to have the letters generate any
number as a code?

For example, a = 26, z = 1, or a = 10, b=20, etc

 

[R1C1]

Create a named range with your alphabet in one column and the numbering in
another column, say AA1:AA26 = A-Z and AB1:AB26 = 1-26. Highlight the range
(AA1:AB26), choose Insert, Name, Define, type in CodeTable in the window,
click Add, click OK.

In cell B1 enter the following formula:

=IF(ISNA(VLOOKUP(A1,CodeTable,2,FALSE)),"",VLOOKUP(A1,CodeTable,2,FALSE))

Select B1, drag the formula across the row as far as you want, as long as
you don't pass Column AA which is your table. You can create your named
range, CodeTable, anywhere you want of course.

Working with children, I would suggest unlocking all the entry cells in Row
1, then protect the worksheet. This will allow the children to enter in the
cells you choose and the formulas for the lookup will remain in tact.

Regards,

Alan

 

[James E Middleton]

Thanks!

Looks like that will get me started. It's really helpful...

I have another question but I'm off to a meeting.... but I wanted to say,
'Thanks' right away...

 

[Ragdyer]

You're welcome.
And if that "other" question is pertaining to this subject, stick with this
thread.

 

[James E Middleton]

R1C1

Very cool, does exactly what RagDyeR's code does but give me complete
control over the numbers is the code.

I teach English at in Japan and listening to numbers comparatively, or being
able to understand large numbers is key for their listening tests. I'll be
able t omake some fun activities easily with this.

I do have one question...

In my original post:

In your response, you mentioned creating a named range all the way over in
AA, AB, which I did.

The formula looks at column A, but you mentioned: 'Select B1, drag the
formula across the row as far as you want, as long as you don't pass Column
AA which is your table.'

So now, I can type the words or sentences down column A, and get the output
in column B.

Along with being able to define a number for each letter is the perfect
solution in itself.

I'm just curious if I misunderstood and there is a way to type in row 1 and
get the code in row 2, instead of column A and B.

Thanks again.

 

[Guest]

If your letter is in cell A1, enter R1C1's formula in cell A2 and drag across.

 

[James E Middleton]

Geez, I guess I don't get it, or I'm not explaining myself clearly - sorry
if I sound stupid...

Currently, the formula from RagDyeR or R1C1 works like this:

Col Col
A B



I 9



l 12

i 9

k 11

e 5



p 16

i 9

z 26

z 26

a 1



It's simple enough to copy column B, Paste Special, Transpose, Values - and
come up with something like this:



9 12 9 11 5 16 9 26 26 1



If I could, I'd like to type this in row 1:



I l i k e p i z z a



and have this appear in row 2:



9 12 9 11 5 16 9 26 26 1



I know you guys say to drag the formula across the rows, but it's written in
reference to A1, so the only way I can figure out how to fill the formula is
to select, drag, and copy it in column B.



Actually it does EXACTLY what I need to do, only vertically instead of
horizontally....



Geez, I know why I don't teach math, but I can't even understand
English.....



Anyway, I really appreciate your help and especially your patience.

 

[Roger Govier]

Hi James

If you do what JMB suggests, it will work - just try it.
Type your message in row 1.
Copy R1C1's formula into A2 and copy across through B2:Z2

In the formula, A1 is relative, so if you copy down, it changes to A2,
A3 etc. but if you copy across, A1 changes to B1, C1 etc
The lookup table is fixed as a range name, so that doesn't matter at
all.

 

[Bernd]

Hello James,

I suggest to use a user-defined-function:
1. Put the code shown below into a macro module (press ALT + F11,
insert a module, paste the code shown below into that module, go back
to worksheet)
2. Enter into cells B1:C26:
A 1
B 2
C 3
....
Z 26
3. Enter into cell A1 the text you want to encipher:
Hello
4. Enter into cell A2:
=caesariancipher(A1,B1:C26)

Now you can change your translation table to any 1:1 code
transformation you want.

18-5-7-1-18-4-19,
Bernd

------------------ snip here -------------------------
Function caesariancipher(s As String, _
r As Range) As String
Dim i As Long, sr As String, sd As String

For i = 1 To Len(s)
sr = sr & sd & _
Application.WorksheetFunction.VLookup( _
UCase(Mid(s, i, 1)), r, 2, False)
sd = "-" 'delimiter
Next i
caesariancipher = sr
End Function
------------------ snip here -------------------------

 

[R1C1]

Sorry James,

I did not type what my mind was thinking, lol. The formula goes in A2 not
B1.

A1 through Z1 are the entry cells, one letter entry per cell.
A2 through Z2 is the formula for the lookup of the table. (Not B1 as I
stated earlier)

Enter the formula in A2 and drag it across to match the number of entry
cells in Row 1.

AA1:AB26 is the lookup table range, CodeTable.

Regards,

Alan

 

[James E Middleton]

20 5 1 18 19 15 6 10
15 25





Tears of joy!



Can't thank you enough.



Best regrads,



Jim

 

[James E Middleton]

Thanks, I'll try that one next.

 

[R1C1]

You are most welcome. Glad to help and good luck.

Regards,

Alan

 

[James E Middleton]

Guys, I don't know if any one is following this thread, but I wanted to come
back and say thanks to everyone!

I have a functional code maker to make prints for my 7th grade students. I'm
sure they will enjoy it!

I have yet to try Bernd's method but I will.

Regards,

Jim