Alphabet Code Game
 

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

Alphabet Code Game
 

Try this:

=CODE(LOWER(A1))-96


--

HTH,

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

"James E Middleton" wrote in message
...
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

Alphabet Code Game
 

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!
================================================== ===

"RagDyeR" wrote in message
...
Try this:

=CODE(LOWER(A1))-96


--

HTH,

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

"James E Middleton" wrote in message
...
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

Alphabet Code Game
 

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" wrote in message
...
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

Alphabet Code Game
 

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" wrote in message
...
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!
================================================== ===

"RagDyeR" wrote in message
...
Try this:

=CODE(LOWER(A1))-96


--

HTH,

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

"James E Middleton" wrote in message
...
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

Alphabet Code Game
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"James E Middleton" wrote in message
...
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" wrote in message
...
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!
================================================== ===

"RagDyeR" wrote in message
...
Try this:

=CODE(LOWER(A1))-96


--

HTH,

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

"James E Middleton" wrote in message
...
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

Alphabet Code Game
 

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:

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.

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.




"R1C1" wrote in message
...
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" wrote in message
...
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

Alphabet Code Game
 

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

"James E Middleton" wrote:

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:

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.

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.




"R1C1" wrote in message
...
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" wrote in message
...
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

Alphabet Code Game
 

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.










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

"James E Middleton" wrote:

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:

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.

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.




"R1C1" wrote in message
...
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" wrote in message
...
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

Alphabet Code Game
 

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.

--
Regards

Roger Govier


"James E Middleton" wrote in message
...
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.










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

"James E Middleton" wrote:

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:

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.

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.




"R1C1" wrote in message
...
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" wrote in message
...
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

Alphabet Code Game
 

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

Alphabet Code Game
 

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" wrote in message
...
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:

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.

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.




"R1C1" wrote in message
...
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" wrote in message
...
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

Alphabet Code Game
 

20 5 1 18 19 15 6 10
15 25





Tears of joy!



Can't thank you enough.



Best regrads,



Jim




"R1C1" wrote in message
...
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" wrote in message
...
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:

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.

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.




"R1C1" wrote in message
...
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" wrote in message
...
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

Alphabet Code Game
 

Thanks, I'll try that one next.


"Bernd" wrote in message
oups.com...
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 -------------------------

Alphabet Code Game
 

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

Regards,

Alan


"James E Middleton" wrote in message
...

20 5 1 18 19 15 6
10 15 25





Tears of joy!



Can't thank you enough.



Best regrads,



Jim




"R1C1" wrote in message
...
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" wrote in message
...
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:

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.

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.




"R1C1" wrote in message
...
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" wrote in message
...
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

Alphabet Code Game
 

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



"James E Middleton" wrote in message
...
Thanks, I'll try that one next.


"Bernd" wrote in message
oups.com...
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 -------------------------