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

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

To add to Helmut's suggestion (which I like, except I would use all letters
all the time)... since your money amounts always have two decimal places,
there is no need to include the decimal point in your encoded price... just
leave it out and assume the penny's amount is the last two letters. For
example...

832.05 = ERTZV
96.74 = NXSF

--
Rick (MVP - Excel)



"Helmut Meukel" wrote in message
...
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

On 18 Mai, 07:31, Amin wrote:
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

Hello,

Why not buying a barcode reader and printing prices with a barcode
font?

Regards,
Bernd

How about using numbers:

reverse the numbers and drop the decimal
cost is 322.04 the field would show 40223
cost is 569.38 the field would show 83965
cost is 256.02 the field would show 20652

or use a decimal out of place with reversed numbers
(decimal moved 2 places)
cost is 322.04 the field would show 4022.3
cost is 569.38 the field would show 8396.5
cost is 256.02 the field would show 2065.2


HTH,

--
Data Hog


"Amin" wrote:

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

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


.

Hi HTH,

Sounds nice but how would I apply to all the items in the list I have? :)

Thanks,


"J_Knowles" wrote:

How about using numbers:

reverse the numbers and drop the decimal
cost is 322.04 the field would show 40223
cost is 569.38 the field would show 83965
cost is 256.02 the field would show 20652

or use a decimal out of place with reversed numbers
(decimal moved 2 places)
cost is 322.04 the field would show 4022.3
cost is 569.38 the field would show 8396.5
cost is 256.02 the field would show 2065.2


HTH,

--
Data Hog


"Amin" wrote:

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

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


.

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


.

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


.

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


.

Oh, by the way, I'm not sure if you caught it or not, but we may be having
this conversation solely between us<g... the OP posted the same question
again today and started his message out with this line...

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

Yes, I know the OP has participated in this thread... twice... and I pointed
this out to him in my response to his newer posting; I have no explanation
for his comment as he has not responded back in his newer thread yet.

--
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


.

Rick,
thanks a lot. I didn't know the trick with Format$(0,".").
I once ran into that decimal point problem while working on a
customers site in the german - sort of - speaking part of Switzerland.
I used a utility dll and on my (german) system it worked, but on the
customers PC it bombed. I had a routine in this dll for high accuracy
of Pi which used
mvarPi = CDec("3,1415926535897932384626433832")
and on a swiss system CDec expected a decimal point.
I solved it by using
mvarPi = CDec("3" & Mid$(CStr(1.2), 2, 1) _
& "1415926535897932384626433832")
You see my solution is clumsy compared to Format(0, ".").

Helmut.


"Rick Rothstein" schrieb im Newsbeitrag
...
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.

I caught it, but it was a so stimulative conversation ...

Helmut.

"Rick Rothstein" schrieb im Newsbeitrag
...
Oh, by the way, I'm not sure if you caught it or not, but we may be having
this conversation solely between us<g... the OP posted the same question
again today and started his message out with this line...

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

Yes, I know the OP has participated in this thread... twice... and I pointed
this out to him in my response to his newer posting; I have no explanation for
his comment as he has not responded back in his newer thread yet.

--
Rick (MVP - Excel)

thanks a lot. I didn't know the trick with Format$(0,".")

This is one of those tricks that you just want to kick yourself for not
seeing it immediately on your own. Here, the Format function is being asked
to apply the pattern string "." to the numerical argument 0 (although any
number would do). And what is the pattern? Why it is just the decimal point
without any any accompanying digits or text specified... since the Format
function is locally aware, it dutifully reports what you asked for... the
localized decimal point without any accompanying digits or text. For those
who are interested, you can use this same trick to retrieve the date
separator character...

DateSeparator = Format(0, "/")

again, because the Format function is locally aware. Unfortunately, we
cannot use this trick directly for the thousands separator; instead, we need
to do it this way...

ThousandsSeparator = Mid(Format(0, "0,000"), 2, 1)

--
Rick (MVP - Excel)



"Helmut Meukel" wrote in message
...
Rick,
thanks a lot. I didn't know the trick with Format$(0,".").
I once ran into that decimal point problem while working on a
customers site in the german - sort of - speaking part of Switzerland.
I used a utility dll and on my (german) system it worked, but on the
customers PC it bombed. I had a routine in this dll for high accuracy
of Pi which used
mvarPi = CDec("3,1415926535897932384626433832")
and on a swiss system CDec expected a decimal point.
I solved it by using
mvarPi = CDec("3" & Mid$(CStr(1.2), 2, 1) _
& "1415926535897932384626433832")
You see my solution is clumsy compared to Format(0, ".").

Helmut.


"Rick Rothstein" schrieb im
Newsbeitrag ...
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.

Hi Rick,

I'd really like to thank both you and Helmut.

When I first posted the original post I was sure I ticked the "notify me of
replies" option but when I didn't get any and I tried looking for my post
back in the pile of new posts I thought I lost it.

I made those replies to the old thread after starting the new one.

I'd like to thank you experts again for your input.

Regards,
Amin



"Rick Rothstein" wrote:

Oh, by the way, I'm not sure if you caught it or not, but we may be having
this conversation solely between us<g... the OP posted the same question
again today and started his message out with this line...

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

Yes, I know the OP has participated in this thread... twice... and I pointed
this out to him in my response to his newer posting; I have no explanation
for his comment as he has not responded back in his newer thread yet.

--
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


.





.