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!

 

[SteveO59L]

you would need to swap the order of your letters/values,
but something along the lines of

=VLOOKUP(LEFT(A1,1)*1,$K:$L,2,FALSE)&VLOOKUP(MID(A1,2,1)*1,$K:$L,2,FALSE)&IF(MID(TEXT(A1,"0.00"),4,1)*1=0,"x",VLOOKUP(MID(TEXT(A1,"0.00"),4,1)*1,$K:$L,2,FALSE))&IF(RIGHT(TEXT(A1,"0.00"),1)*1=0,"y",VLOOKUP(RIGHT(TEXT(A1,"0.00"),1)*1,K:L,2,FALSE))

 

[István Hirsch]

This UDF does what you need for the samples given:

Function LetterCode(s As String) As String
 
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
LetterCode = x
End Function

Excel Workbook
	A	B
1	10.00	AJXY
2	15.00	AEXY
3	11.50	AZEY
4	55.50	EZEY
5	159.50	AEIEY
6	333.00	CZCXY
7	212.20	BABZY
8	1,068.00	AJFHXY
Sheet

 

[jboymeng]

Thank you for the reply Istvan,

I tried out your UDF but the results where close to what I needed.
The lettercode function didn't work on the decimals point zero zero (.00) but they did fine on other decimals not zero zero...
The Z worked great..

Any Idea how to get around this?

 

[jboymeng]

Hi Steve,

Interesting approach.

Would you mind walking me through your idea?
I'm not that good with the vlookup function.

Thank you very much.

 

[István Hirsch]

Thank you for the reply Istvan,

I tried out your UDF but the results where close to what I needed.
The lettercode function didn't work on the decimals point zero zero (.00) but they did fine on other decimals not zero zero...
The Z worked great..

Any Idea how to get around this?

At me the UDF works correctly as it is shown in the table in #3. The difference may be caused by the fact that I am in a different number system where the decimal separator is a comma, the thousand separator is a period. For example, in my system 15.00 is a string, not a number. If you always work with numbers with 2 decimals, try the UDF below:

Function LetterCode(s As String) As String
[COLOR=#ff0000]s = s * 100[/COLOR]
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
LetterCode = x
End Function

 

[jboymeng]

Works great, you're amazing!! Thank you very much István.


so we had an issue with the decimal... Im guessing you are from Germany. Great country.
also i just checked the link you gave on your previous post, I could not read it. Does it have a translate button?


I have a question about the UDF you gave me..


Since I use different codes for items received from a different country (let's say i buy from 2 countries china and japan)
how can I change the letter designated for the same value?




E.G.


CODE NUM 1 is the same as the code above.


CODE NUM 2


E=1
U=2
C=3
H=4
A=5
R=6
I=7
S=8
T=9
O=0


The twist is the same as the previous code.




I did not include this in the first question because I thought i could just edit the UDF a bit. Hehe :D

 

[pgc01]

Hi István

Not sure if it's relevant for this case, but It seems it won't work for

1.08

You are not testing the tenth's alone.

 

[Rick Rothstein]

Works great, you're amazing!! Thank you very much István.

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

Since I use different codes for items received from a different country (let's say i buy from 2 countries china and japan)
how can I change the letter designated for the same value?

CODE NUM 1 is the same as the code above.

CODE NUM 2

E=1
U=2
C=3
H=4
A=5
R=6
I=7
S=8
T=9
O=0

The twist is the same as the previous code.

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

 

[István Hirsch]

Works great, you're amazing!! Thank you very much István.


so we had an issue with the decimal... Im guessing you are from Germany. Great country.
also i just checked the link you gave on your previous post, I could not read it. Does it have a translate button?


I have a question about the UDF you gave me..


Since I use different codes for items received from a different country (let's say i buy from 2 countries china and japan)
how can I change the letter designated for the same value?




E.G.


CODE NUM 1 is the same as the code above.


CODE NUM 2


E=1
U=2
C=3
H=4
A=5
R=6
I=7
S=8
T=9
O=0


The twist is the same as the previous code.




I did not include this in the first question because I thought i could just edit the UDF a bit. Hehe :D

Thanks for the feedback.
My surname sounds German, but I am Hungarian.

I think you mean the link below my table. It automatically appears if someone uses Excel Jeanie, a useful tool to convert Excel spreadsheets to html.

If you are going to use different codings, say two, I suggest using the same UDF equipped with a part that „translates” the first code into the second code. The blue lines show the differences compared with the first UDF.

Function LetterCode2(s As String) As String
 
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")
x = Replace(Replace(x, "J", "O"), "{", "E")
LetterCode2 = x
 
End Function