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.

--
Rick (MVP - Excel)



"Helmut Meukel" wrote in message
...
Rick,

you are right about trailing zeroes, I should have used
EncodeCosts = Format(Costs, "0.00")
instead of CStr(Costs), but your solution won't work on my
system or on others in countries with a decimal comma without
changing the code.
I admit your code is shorter. From his questions I guessed Amin
isn't an experienced programmer and I think my code is easier
to understand and to adjust to his needs.
By changing one character in my code the value 832.25
would produce
ERT-TV or ERT*TV or ERT TV instead of ERT.TV
Personally I like the "*" best.

Helmut.


"Rick Rothstein" schrieb im
Newsbeitrag ...
You need to change the data type for your Costs argument to String... if
you leave it as a numeric data type, trailing zeroes will be lost after
the decimal point.

With that said, here is your another (slightly shorter) approach to do
what your code does...

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

We can reduce the amount of code a little bit more if we remove the
decimal point from the encoded number, displaying nothing in its place
(as I suggested as a possible encoding method in my prior response in
this thread)...

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 (MVP - Excel)



"Helmut Meukel" wrote in message
...
Amin,

try this:

Function EncodeCosts(ByVal Costs As Currency) As String
Dim strCosts As String, strTmp As String
Dim l As Integer, i As Integer
Dim vArr As Variant

vArr = Array("Z", "O", "T", "R", "F", "V", "X", "S", "E", "N")

strCosts = Trim(CStr(Costs))
l = Len(strCosts)
EncodeCosts = Space(l)
For i = 1 To l
strTmp = Mid(strCosts, i, 1)
If strTmp = "." Or strTmp = "," Then
Mid(EncodeCosts, i, 1) = "."
Else
Mid(EncodeCosts, i, 1) = vArr(Val(strTmp))
End If
Next i
End Function

The above works regardless of the locale. Because CStr is localized,
it returns a string containing the local decimal sign. The code always
returns a string with a dot, but you can easily replace it with any
other
sign or character. If you remove the line
Mid(EncodeCosts, i, 1) = "."
you'll get a space instead of the decimal point.

Helmut.


"Amin" schrieb im Newsbeitrag
...
Hello Helmut,

Thanks for your response.

It doesn't really matter which logic to use I still need the CODE to do
it,
I have an item list with more than 1000 items.

Any ideas about the code?

Thanks again,


"Helmut Meukel" wrote:

Hmm,

one problem with encoding is to obscure the value, so guessing
the real value isn't easy and nonetheless make it siple for YOU
to read the real value. Just using A to I for 1 to 9 makes decoding
for others easier than necessary.
How about using
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)
I would probably leave one number as a number (3) and use
A for 8.
e.g. 832.05 = A3T.ZV
96.74 = NX.SF

Helmut.


"Amin" schrieb im Newsbeitrag
...
Hello Experts,

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

I'd like to add the cost field to the label but the problem
everybody would
be able to know my cost for an item unless I encode it, so I thought
of
converting numbers to characters for example:

0 = Z
1 = A
2 = B
3 = C
4 = D

So, if the cost of an Item is 322.04 the field would show CBB.ZD

Any thoughts?

Thanks in advance


.