Turning numbers into letters with a twist at the end

[jboymeng]

Hello everyone!

This is my first post! and I already did some research but I couldn't find any formula or module that could completely solve this problem.

The numbers to letters was already solved, but the twist is really difficult..

I need the numbers in cell A1 to become coded letters in cell B1..

letters and their corresponding values as follows;

[TABLE="class: grid, width: 75, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]G[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]H[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]I[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]J[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

The twist

X – STANDS FOR THE TENTH PLACE ZERO ( .”0”0)
Y – STANDS FOR THE HUNDRETH PLACE ZERO (.0”0”|)
Z – STANDS FOR THE SAME NUMERICAL VALUE AS THE NUMBER BEFORE IT, GIVEN THAT IT IS NOT THE THIRD SAME NUMBER

E.G.

• 10.00 IS AJXY
• 15.00 IS AEXY
• 11.50 IS AZEY
• 55.50 IS EZEY
• 159.50 IS AEIEY
• 333.00 IS CZCXY
• 212.20 IS BABZY
• 1,068.00 IS AJFHXY

And BTW I am using MS Excel 2007

I hope you guys can help me..
Looking forward to hearing from you and thanks in advance!

 

[István Hirsch]

Hi PGC01 and Rick,

Thanks for the feedback. I supposed that the OP works with structures that are provided in post #1, so the UDF in this form was not created to process numbers like 1.08 nor numbers without decimals. However, I would like to know the OP's opinion, too.

 

[István Hirsch]

Sorry, my second UDF (post #10) correctly:

Function LetterCode2(s As String) As String
s=s*100
s = Replace(Replace(s, ",", ""), ".", "")
    For i = 1 To Len(s)
        x = x & Chr(Mid(s, i, 1) + 64)
    Next
If Right(x, 2) = "@@" Then x = Left(x, Len(x) - 2) & "XY"
If Right(x, 1) = "@" Then x = Left(x, Len(x) - 1) & "Y"
x = Replace(x, "@", "J")
    For i = 1 To Len(x)
        If Mid(x, i, 1) = Mid(x, i + 1, 1) Then x = Left(x, i) & "Z" & Right(x, Len(x) - i - 1)
    Next
[COLOR=#0000ff]x = Replace(Replace(Replace(Replace(x, "A", "{"), "B", "U"), "H", "S"), "E", "A")
x = Replace(Replace(Replace(Replace(x, "F", "R"), "I", "T"), "D", "H"), "G", "I")
x = Replace(Replace(x, "J", "O"), "{", "E")[/COLOR]
LetterCode2 = x
 
End Function

 

[jboymeng]

Sorry, my second UDF (post #10) correctly:

Function LetterCode2(s As String) As String
s=s*100
s = Replace(Replace(s, ",", ""), ".", "")
    For i = 1 To Len(s)
        x = x & Chr(Mid(s, i, 1) + 64)
    Next
If Right(x, 2) = "@@" Then x = Left(x, Len(x) - 2) & "XY"
If Right(x, 1) = "@" Then x = Left(x, Len(x) - 1) & "Y"
x = Replace(x, "@", "J")
    For i = 1 To Len(x)
        If Mid(x, i, 1) = Mid(x, i + 1, 1) Then x = Left(x, i) & "Z" & Right(x, Len(x) - i - 1)
    Next
[COLOR=#0000ff]x = Replace(Replace(Replace(Replace(x, "A", "{"), "B", "U"), "H", "S"), "E", "A")
x = Replace(Replace(Replace(Replace(x, "F", "R"), "I", "T"), "D", "H"), "G", "I")
x = Replace(Replace(x, "J", "O"), "{", "E")[/COLOR]
LetterCode2 = x
 
End Function

Hi István,

Worked like a charm.
Then I tried to mess around with the code by changing the repeater, (the "Z" is the repeater) to its real letter "G". That is when I hit a brick wall since it keeps converting it to "I" w/c stands for "7" in the code.

Other from that "Lettercode2" is very understandable and am fairly confident that I can edit it for future changes in the code words and twists.

P.S. XY is definitely used in every code, in case you were wondering if the real letters are different as well.

 

[Rick Rothstein]

@jboymeng,

Just wondering if you gave the function I posted in Message #9 a try (I think you will find using it is quite flexible and you won't have to modify the program for future changes in your letter codes)?

 

[jboymeng]

I was going to raise the same point about István's code as PGC did in Message #8.



Give the following function a try. It has two argument... the numbers you want converted and the letters (in order 1 thru 10) that you want to use or the conversion. So, if A1 contained the number you wanted to convert and the letters you show above are the ones you want to use for the conversion, then the formula in the cell would be...

=LetterCode(A1,"EUCHARISTO")

For your the original question, the formula would be this...

=LetterCode(A1,"ABCDEFGHIJ")

Okay, here is my UDF...

Function LetterCode(ByVal Numbers As String, Letters As String) As String
  Dim X As Long
  Numbers = Format(Numbers, "0.00") * 100
  Letters = UCase(Right(Letters, 1) & Left(Letters, Len(Letters) - 1))
  If Numbers Like "*0" Then Mid(Numbers, Len(Numbers)) = "Y"
  If Numbers Like "*0?" Then Mid(Numbers, Len(Numbers) - 1) = "X"
  For X = 1 To Len(Numbers)
    If X > 1 Then If Mid(Numbers, X, 1) = Mid(Numbers, X - 1, 1) Then Mid(Numbers, X) = "Z"
    If Mid(Numbers, X, 1) Like "#" Then
      LetterCode = LetterCode & Mid(Letters, Mid(Numbers, X, 1) + 1, 1)
    Else
      LetterCode = LetterCode & Mid(Numbers, X, 1)
    End If
  Next
End Function

Hi Rick,

Thank you very much for your post.

The code worked like a charm and is easily understandable and highly flexible for future changes in code words.

Here is the only problem, secrecy of the code that will be used.
Since worksheet templates will be given for encoder use (people who do not know the actual code word).

I tried protecting the sheet and hiding the formulas but I could not drag the formula for the entire column.
It only copied the contents of the top most cell. I have no idea why.

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

Hey,

I Could not finish the above reply earlier since I had somewhere to be in a hurry and could only reply to Istvan.

Sorry About that.

 

[jboymeng]

Hi PGC01 and Rick,

Thanks for the feedback. I supposed that the OP works with structures that are provided in post #1, so the UDF in this form was not created to process numbers like 1.08 nor numbers without decimals. However, I would like to know the OP's opinion, too.

What you supposed is correct. I work with 2 decimal structured numbers and never is there an instance of no decimals.

 

[István Hirsch]

If you want to change the „repeat code” „Z” to „G”, try this UDF (the red line contains the changes).

Switching from one coding to another needs special attention in the case of these UDFs: for example, if you replace „A” with „E” then „E” with „A”, you should replace „A” with a temporary (not used) character first, otherwise both „old” „A” and „E” become „new” „A”. If you convert „Z” to „G” before „G” to „I” then „Z” becomes „I”.

These problems do not arise using Rick’s UDF, which offers a convenient way of code changing, so I highly recommend its using instead of my UDF.

Function LetterCode2(s As String) As String
s = s * 100
s = Replace(Replace(s, ",", ""), ".", "")
    For i = 1 To Len(s)
        x = x & Chr(Mid(s, i, 1) + 64)
    Next
If Right(x, 2) = "@@" Then x = Left(x, Len(x) - 2) & "XY"
If Right(x, 1) = "@" Then x = Left(x, Len(x) - 1) & "Y"
x = Replace(x, "@", "J")
    For i = 1 To Len(x)
        If Mid(x, i, 1) = Mid(x, i + 1, 1) Then x = Left(x, i) & "Z" & Right(x, Len(x) - i - 1)
    Next
x = Replace(Replace(Replace(Replace(x, "A", "{"), "B", "U"), "H", "S"), "E", "A")
x = Replace(Replace(Replace(Replace(x, "F", "R"), "I", "T"), "D", "H"), "G", "I")
[COLOR=#ff0000]x = Replace(Replace(Replace(x, "J", "O"), "{", "E"), "Z", "G")[/COLOR]
LetterCode2 = x
End Function

 

[Rick Rothstein]

Hi Rick,

Here is the only problem, secrecy of the code that will be used.
Since worksheet templates will be given for encoder use (people who do not know the actual code word).

Here are two things you can do to hide the codes. Add a new sheet, name the sheet "Codes" (exact name that you use isn't important), put your letter codes in cells A1, A2, etc., the hide the sheet using this code line in the VB editor's Immediate Window...

Sheets("Codes").Visible = xlSheetVeryHidden

After that, the sheet will be very hidden and can only be recovered for editing purposes (to add/remove letter codes for example) using this code line...

Sheets("Codes").Visible = xlSheetVisible

You can then hide your code behind a password using the Protection tab on the VB editor's "Tools/VBAProject Properties" menu bar item. Finally, you can protect your workbook's structure with a password by clicking the "Review" tab on Excel's Ribbon, then clicking the "Protect Workbook" button within the "Changes" group... put a checkmark in the "Structure" checkbox and type your password into the provided field. Now, none of the above protections are totally foolproof to someone intent on subverting them, but they will keep the vast majority of users out.

Oh, forgot to tell you how to access the letter codes. What I would do is give each letter code on the very hidden worksheet a defined name and then use those defined names in the worksheet formulas that do the decoding. So, let's say you gave "ABCDEFGHIJ" the defined name "Main" and "EUCHARISTO" the defined name "Alternate", then you would use this formula to access the first letter code...

=LetterCode(A1,Main)

and this to access the second...

=LetterCode(A1,Alternate)

Of course, you would need to keep a master list of all the defined names for yourself so you could refer to them without having to unhide the very hidden worksheet each time you needed to look one up.

 

[jboymeng]

Oh, forgot to tell you how to access the letter codes. What I would do is give each letter code on the very hidden worksheet a defined name and then use those defined names in the worksheet formulas that do the decoding. So, let's say you gave "ABCDEFGHIJ" the defined name "Main" and "EUCHARISTO" the defined name "Alternate", then you would use this formula to access the first letter code...

=LetterCode(A1,Main)

and this to access the second...

=LetterCode(A1,Alternate)

Of course, you would need to keep a master list of all the defined names for yourself so you could refer to them without having to unhide the very hidden worksheet each time you needed to look one up.

Wow, I learned a lot in just one post. No wonder you have "MVP" under your avatar.

I have tried it out on a variety of codes and numbers and works great.

Thank you very much this will be of great use.

 

[jboymeng]

This post is for Rick Rothstein and István Hirsch.
Thank you both for your help. As a small tribute the modules used will be named after you guys. It is the only way I can thank you and remember the guys who helped me out at the same time.

I will be using István's UDF for the Eucharist code and I will use Rick's for all other codes.

Not a lot of people can do what you do and at the same time be as helpful as you.
You guys are great.

Have a nice day ahead.