Convert number to letter 123 to ABC

[Amin]

Hello Experts,

(I've previously asked this question but didnt get any replies.)

At my shop I'm printing product labels from an Excel sheet.

I'd like to add the cost field to the label but that would allow everyone to
figure my cost for the item.

So I thought of encoding the cost filed as the following:

0 = Z
1 = A
2 = B
3 = C
..
..
9 = I

So if the cost in a cell is (322.40) it would translate to (CBB.DZ).

Any ideas?

Thanks in advance

 

[Mike H]

Amin.

How about a user defined function. Alt + F11 to open VB editor. Right click
'This Workbook' and insert module and paste the code below in. Close VB
editor and back on the worksheet call the function with

=Encode(A1)

where A1 contains your price


Function Encode(price As String) As String
Dim x As Long
Application.Volatile
For x = 1 To Len(price)
If Mid(price, x, 1) = "." Then
Encode = Encode & Mid(price, x, 1)
ElseIf Mid(price, x, 1) = "0" Then
Encode = Encode & "Z"
Else
Encode = Encode & Chr(64 + Val(Mid(price, x, 1)))
End If
Next
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Function Encode(rngTemp As Range)
If IsNumeric(rngTemp.Text) Then
For intcount = 1 To Len(rngTemp.Text)
Select Case Mid(rngTemp.Text, intcount, 1)
Case "."
Encode = Encode & "."
Case "0"
Encode = Encode & "Z"
Case Else
Encode = Encode & Chr(Mid(rngTemp.Text, intcount, 1) + 64)
End Select
Next
End If
End Function

 

[Rick Rothstein]

(I've previously asked this question but didnt get any replies.)

???? Not only did you get answers to your previous posting of this question,
but you answered some of the responses you got there!

Back in your original thread, Helmut Meukel suggested converting your codes
this way...

0 = Z(ero)
1 = O(ne)
2 = T(wo)
3 = (th)R(ee)
4 = F(our)
5 = (fi)V(e)
6 = (si)X
7 = S(even)
8 = E(ight)
9 = N(ine)

and he also suggested leaving the decimal point in place. He posted code
that did that. I followed up on that by suggesting the decimal point be
omitted to make it even harder to decipher a it as a price code. I then
posted these shorter code routines to do either of these suggestions....

' Leave the decimal point in the code
Function EncodeCosts(Costs As Currency) As String
Dim X As Long
EncodeCosts = CStr(Costs)
For X = 1 To Len(EncodeCosts)
If Mid(EncodeCosts, X, 1) <> "." Then Mid(EncodeCosts, X, 1) = _
Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
Next
End Function

' Omit the decimal point from the code
Function EncodeCosts(Costs As String) As String
Dim X As Long
EncodeCosts = Replace(CStr(Costs), ".", "")
For X = 1 To Len(EncodeCosts)
Mid(EncodeCosts, X) = Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
Next
End Function

 

[Rick Rothstein]

Helmut and I are still conversing back in the older thread; but, just in
case you still can't see the responses there, here is part of my latest
posting back there which contains a correction to my code (in response to a
comment by Helmut)...

Good point about the decimal point, but easily resolved using
Format$(0,"."), which will return the localized decimal point character. I
like the idea of using Format instead of CStr, but I changed the format
pattern slightly so that when the EncodeCosts function is used as a UDF
against empty cells, nothing will be displayed instead of 0.00 (which is
what your format pattern would display). I left the format pattern returning
0.00 for a price of zero, although I guess one wouldn't normally expect that
price in a cell; however, putting 0 after the second semi-colon in my format
pattern would force the return value of 0 instead of 0.00 if that turned out
to be the desired result for zero dollars. As for allowing the OP to change
the character from a decimal point to an asterisk (or any other text string,
whether one or more character in length), I added a new last statement to my
function... currently it is commented out (which means the decimal point is
retained), however "uncommenting" it and using whatever text you want in the
Replace function call's last argument (currently set up as your favored
asterisk symbol) will make the output use that text in place of the decimal
point instead.


Function EncodeCosts(Costs As Currency) As String
Dim X As Long, DecimalPoint As String
DecimalPoint = Format$(0, ".")
EncodeCosts = Format(Costs, "0.00;;0;")
For X = 1 To Len(EncodeCosts)
If Mid(EncodeCosts, X, 1) <> DecimalPoint Then Mid(EncodeCosts, _
X, 1) = Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
Next
'EncodeCosts = Replace(EncodeCosts, DecimalPoint, "*")
End Function

Just in case the OP turns out to want to adopt my suggestion of using no
separating symbol (knowing that the last two characters represents the
number of pennies), here is my modified code which should work for the
international community...

Function EncodeCosts(Costs As String) As String
Dim X As Long
EncodeCosts = Format(Costs, "0.00;;0;")
For X = 1 To Len(EncodeCosts)
Mid(EncodeCosts, X) = Mid("ZOTRFVXSEN", Mid(EncodeCosts, X, 1) + 1, 1)
Next
End Function

Just as a point of information, I have never had to deal with international
issues in my programming career, hence my stumbling around on the decimal
point matter.