Cypher

[Bullocks]

Good day,

No, I'm not James Bond nor am I a criminal trying to hack into the
CIA... Just wanted a hand with a simple cypher if anyone can help. I
want to be able to change a series of letters and characters into
another for a specific task...

E.g.
A,B,C = 1
F,G,H = 2
J,K,L = 3
#,$,% = 3

If I enter BHL% I want to see 1234

I tried using IF comands as shown below but I can only nest 7... is
there an array or something I can use (I am hopless with VBA so if
anyone knows an excel formual I would be grateful)????
=IF(A1="A",1,IF(A1="B",1,IF(A1="C",1,IF(A1="F",2))))

 

[Luke M]

I really think you're going to need to use a VBA user-defined-function. From
Excel, press Alt+F11, Alt+I, Alt+M. You should now have a blank module. Paste
this UDF in:

============

Private Function CodeConvert(OriginalWord As String) As String
Dim test As String
Dim xCode As String
Dim TextLength As Integer

TextLength = Len(OriginalWord)

For t = 1 To TextLength
xLetter = Mid(OriginalWord, t, 1)

Select Case xLetter

'Add cases as needed
Case "A", "B", "C"
Value = 1
Case "F", "G", "H"
Value = 2
Case "J", "K", "L"
Value = 3
Case "#", "$", "%"
Value = 4

End Select
xCode = xCode & Value
Next

CodeConvert = xCode
End Function

==================
In the section with all the cases, this is where you can add additional
conditions. Note that like any good cypher, this is case-sensitive. Once you
have this setup, you can use it like a formula in your workbook. Simply input:

=CodeConvert("BHL%")
or a cell reference
=CodeConvert(A1)

and voila!

 

[Bullocks]

Thank you Luke!

You have saved me from a concussion caused by my head impacting with
my desk. Your kindess is beyond words my friend.

Cheers!

 

[Rick Rothstein]

This can be done using a formula. For the single character example the OP
posted...

=1+INT((SEARCH(A1,"ABCFGHJKL#$%")-1)/3)