If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cypher
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)) )) |
#2
|
|||
|
|||
Cypher
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! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bullocks" wrote: 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)) )) |
#3
|
|||
|
|||
Cypher
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! |
#4
|
|||
|
|||
Cypher
This can be done using a formula. For the single character example the OP
posted... =1+INT((SEARCH(A1,"ABCFGHJKL#$%")-1)/3) -- Rick (MVP - Excel) "Luke M" wrote in message ... 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! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Bullocks" wrote: 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)) )) |
Thread Tools | |
Display Modes | |
|
|