School-boy secret code help needed
 

Do you remember how we used to write secret code messages as kids whe -
A=1
B=2
C=3
D=4
etc...

So "How are you" would be written as "8,15,23 1,18,5 25,15,21"

Is there a way I could do this with excel where I would write the words in
separate cells and excel returns the corresponding numbers?

TIA

Hello
You could use the CODE function eg:
In A1:
"a"
In B1 formula:
=CODE(A1)
And so on.

HTH
Cordially
Pascal

"Anthony Slater" a écrit dans le
message de news: ...
Do you remember how we used to write secret code messages as kids whe -
A=1
B=2
C=3
D=4
etc...

So "How are you" would be written as "8,15,23 1,18,5 25,15,21"

Is there a way I could do this with excel where I would write the words in
separate cells and excel returns the corresponding numbers?

TIA

I think I'd use a User Defined Function instead:

Public Function SchoolBoy(sInput As String) As String
Dim nChars As Long
Dim i As Long
Dim sChar As String
Dim sTemp As String
nChars = Len(sInput)
If nChars = 0 Then
SchoolBoy = ""
Else
For i = 1 To nChars
sChar = UCase(Mid(sInput, i, 1))
If sChar Like "[A-Z]" Then
sTemp = sTemp & Asc(sChar) - 64 & ","
Else
If Right(sTemp, 1) = "," Then _
sTemp = Left(sTemp, Len(sTemp) - 1)
sTemp = sTemp & sChar
End If
Next i
SchoolBoy = Left(sTemp, Len(sTemp) + (Right(sTemp, 1) = ","))
End If
End Function

call as

=SchoolBoy(A1)

If you're not familiar with UDFs, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm



In article ,
"Anthony Slater" wrote:

Do you remember how we used to write secret code messages as kids whe -
A=1
B=2
C=3
D=4
etc...

So "How are you" would be written as "8,15,23 1,18,5 25,15,21"

Is there a way I could do this with excel where I would write the words in
separate cells and excel returns the corresponding numbers?

TIA

Thanks for your reply

I copied your code in to a new module in Personal.xls and returned to excel.

Am I right in thinking that my 'text' should be in cell A1 and then I enter
in another cell =SchoolBoy(a1)?

I tried this but it returned =#NAME?


"JE McGimpsey" wrote:

I think I'd use a User Defined Function instead:

Public Function SchoolBoy(sInput As String) As String
Dim nChars As Long
Dim i As Long
Dim sChar As String
Dim sTemp As String
nChars = Len(sInput)
If nChars = 0 Then
SchoolBoy = ""
Else
For i = 1 To nChars
sChar = UCase(Mid(sInput, i, 1))
If sChar Like "[A-Z]" Then
sTemp = sTemp & Asc(sChar) - 64 & ","
Else
If Right(sTemp, 1) = "," Then _
sTemp = Left(sTemp, Len(sTemp) - 1)
sTemp = sTemp & sChar
End If
Next i
SchoolBoy = Left(sTemp, Len(sTemp) + (Right(sTemp, 1) = ","))
End If
End Function

call as

=SchoolBoy(A1)

If you're not familiar with UDFs, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm



In article ,
"Anthony Slater" wrote:

Do you remember how we used to write secret code messages as kids whe -
A=1
B=2
C=3
D=4
etc...

So "How are you" would be written as "8,15,23 1,18,5 25,15,21"

Is there a way I could do this with excel where I would write the words in
separate cells and excel returns the corresponding numbers?

TIA

Try:

=personal.xls!schoolboy(a1)



Anthony Slater wrote:

Thanks for your reply

I copied your code in to a new module in Personal.xls and returned to excel.

Am I right in thinking that my 'text' should be in cell A1 and then I enter
in another cell =SchoolBoy(a1)?

I tried this but it returned =#NAME?

"JE McGimpsey" wrote:

I think I'd use a User Defined Function instead:

Public Function SchoolBoy(sInput As String) As String
Dim nChars As Long
Dim i As Long
Dim sChar As String
Dim sTemp As String
nChars = Len(sInput)
If nChars = 0 Then
SchoolBoy = ""
Else
For i = 1 To nChars
sChar = UCase(Mid(sInput, i, 1))
If sChar Like "[A-Z]" Then
sTemp = sTemp & Asc(sChar) - 64 & ","
Else
If Right(sTemp, 1) = "," Then _
sTemp = Left(sTemp, Len(sTemp) - 1)
sTemp = sTemp & sChar
End If
Next i
SchoolBoy = Left(sTemp, Len(sTemp) + (Right(sTemp, 1) = ","))
End If
End Function

call as

=SchoolBoy(A1)

If you're not familiar with UDFs, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm



In article ,
"Anthony Slater" wrote:

Do you remember how we used to write secret code messages as kids whe -
A=1
B=2
C=3
D=4
etc...

So "How are you" would be written as "8,15,23 1,18,5 25,15,21"

Is there a way I could do this with excel where I would write the words in
separate cells and excel returns the corresponding numbers?

TIA


--

Dave Peterson

Dave

Thanks, that worked a treat.

...and also a big thanks to JE McGimpsey for the excellent piece of code



"Dave Peterson" wrote:

Try:

=personal.xls!schoolboy(a1)



Anthony Slater wrote:

Thanks for your reply

I copied your code in to a new module in Personal.xls and returned to excel.

Am I right in thinking that my 'text' should be in cell A1 and then I enter
in another cell =SchoolBoy(a1)?

I tried this but it returned =#NAME?

"JE McGimpsey" wrote:

I think I'd use a User Defined Function instead:

Public Function SchoolBoy(sInput As String) As String
Dim nChars As Long
Dim i As Long
Dim sChar As String
Dim sTemp As String
nChars = Len(sInput)
If nChars = 0 Then
SchoolBoy = ""
Else
For i = 1 To nChars
sChar = UCase(Mid(sInput, i, 1))
If sChar Like "[A-Z]" Then
sTemp = sTemp & Asc(sChar) - 64 & ","
Else
If Right(sTemp, 1) = "," Then _
sTemp = Left(sTemp, Len(sTemp) - 1)
sTemp = sTemp & sChar
End If
Next i
SchoolBoy = Left(sTemp, Len(sTemp) + (Right(sTemp, 1) = ","))
End If
End Function

call as

=SchoolBoy(A1)

If you're not familiar with UDFs, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm



In article ,
"Anthony Slater" wrote:

Do you remember how we used to write secret code messages as kids whe -
A=1
B=2
C=3
D=4
etc...

So "How are you" would be written as "8,15,23 1,18,5 25,15,21"

Is there a way I could do this with excel where I would write the words in
separate cells and excel returns the corresponding numbers?

TIA


--

Dave Peterson