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" <(E-Mail Removed)> schrieb im Newsbeitrag
news:97262BCA-C7A0-410E-BB5C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:97262BCA-C7A0-410E-BB5C-(E-Mail Removed)...
>> 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 <A...@discussions.microsoft.com> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:97262BCA-C7A0-410E-BB5C-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> schrieb im Newsbeitrag
news:8E6BA483-F87F-4E7C-AD52-(E-Mail Removed)...
> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
>> news:97262BCA-C7A0-410E-BB5C-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:8E6BA483-F87F-4E7C-AD52-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
>>> news:97262BCA-C7A0-410E-BB5C-(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
>> news:8E6BA483-F87F-4E7C-AD52-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
>>>> news:97262BCA-C7A0-410E-BB5C-(E-Mail Removed)...
>>>> > 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
>>>>
>>>>
>>>> .
>>>>
>>
>>