School-boy secret code help needed

Do you remember how we used to write secret code messages as kids where: -
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" <[email protected]> a écrit dans le
message de news: [email protected]...
> Do you remember how we used to write secret code messages as kids where: -
> 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 <[email protected]>,
"Anthony Slater" <[email protected]> wrote:

> Do you remember how we used to write secret code messages as kids where: -
> 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 <[email protected]>,
> "Anthony Slater" <[email protected]> wrote:
>
> > Do you remember how we used to write secret code messages as kids where: -
> > 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 <[email protected]>,
> > "Anthony Slater" <[email protected]> wrote:
> >
> > > Do you remember how we used to write secret code messages as kids where: -
> > > 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 <[email protected]>,
> > > "Anthony Slater" <[email protected]> wrote:
> > >
> > > > Do you remember how we used to write secret code messages as kids where: -
> > > > 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
>