Converting digits to characters (123 to ABC)

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
0
Utf
5/18/2010 6:31:01 AM
excel.programming 6508 articles. 2 followers. Follow

15 Replies
1090 Views

Similar Articles

[PageSpeed] 16

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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
> 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


0
Helmut
5/18/2010 7:29:32 AM
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" <NoSpam@NoProvider.de> wrote in message 
news:eSrbdvl9KHA.4924@TK2MSFTNGP04.phx.gbl...
> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
>> 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
>
> 
0
Rick
5/18/2010 4:52:13 PM
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
0
Bernd
5/18/2010 6:09:41 PM
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
0
Utf
5/18/2010 11:30:16 PM
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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
> > 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
> 
> 
> .
> 
0
Utf
5/19/2010 9:22:01 AM
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
0
Utf
5/19/2010 9:30:01 AM
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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
news:8E6BA483-F87F-4E7C-AD52-749F0362FB99@microsoft.com...
> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
>> > 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
>>
>>
>> .
>>


0
Helmut
5/19/2010 11:05:58 AM
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" <NoSpam@NoProvider.de> wrote in message 
news:OsSOEN09KHA.1892@TK2MSFTNGP05.phx.gbl...
> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
> news:8E6BA483-F87F-4E7C-AD52-749F0362FB99@microsoft.com...
>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag
>>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
>>> > 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
>>>
>>>
>>> .
>>>
>
> 
0
Rick
5/19/2010 2:56:53 PM
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" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im Newsbeitrag 
news:utHZJO29KHA.4564@TK2MSFTNGP05.phx.gbl...
> 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" <NoSpam@NoProvider.de> wrote in message 
> news:OsSOEN09KHA.1892@TK2MSFTNGP05.phx.gbl...
>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
>> news:8E6BA483-F87F-4E7C-AD52-749F0362FB99@microsoft.com...
>>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag
>>>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
>>>> > 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
>>>>
>>>>
>>>> .
>>>>
>>
>>


0
Helmut
5/19/2010 6:09:42 PM
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" <NoSpam@NoProvider.de> wrote in message 
news:eQjf4539KHA.4308@TK2MSFTNGP04.phx.gbl...
> 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" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im 
> Newsbeitrag news:utHZJO29KHA.4564@TK2MSFTNGP05.phx.gbl...
>> 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" <NoSpam@NoProvider.de> wrote in message 
>> news:OsSOEN09KHA.1892@TK2MSFTNGP05.phx.gbl...
>>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
>>> news:8E6BA483-F87F-4E7C-AD52-749F0362FB99@microsoft.com...
>>>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag
>>>>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
>>>>> > 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
>>>>>
>>>>>
>>>>> .
>>>>>
>>>
>>>
>
> 
0
Rick
5/19/2010 6:54:46 PM
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" <NoSpam@NoProvider.de> wrote in message 
news:eQjf4539KHA.4308@TK2MSFTNGP04.phx.gbl...
> 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" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im 
> Newsbeitrag news:utHZJO29KHA.4564@TK2MSFTNGP05.phx.gbl...
>> 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" <NoSpam@NoProvider.de> wrote in message 
>> news:OsSOEN09KHA.1892@TK2MSFTNGP05.phx.gbl...
>>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
>>> news:8E6BA483-F87F-4E7C-AD52-749F0362FB99@microsoft.com...
>>>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag
>>>>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
>>>>> > 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
>>>>>
>>>>>
>>>>> .
>>>>>
>>>
>>>
>
> 
0
Rick
5/19/2010 6:59:58 PM
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" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im Newsbeitrag 
news:eRvxET49KHA.5476@TK2MSFTNGP06.phx.gbl...
> 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" <NoSpam@NoProvider.de> wrote in message 
> news:eQjf4539KHA.4308@TK2MSFTNGP04.phx.gbl...
>> 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.

0
Helmut
5/19/2010 10:23:33 PM
I caught it, but it was a so stimulative conversation ...

Helmut.

"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im Newsbeitrag 
news:eUhc%23V49KHA.5476@TK2MSFTNGP06.phx.gbl...
> 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)
>
>


0
Helmut
5/19/2010 10:29:41 PM
> 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" <NoSpam@NoProvider.de> wrote in message 
news:eiUssH69KHA.4308@TK2MSFTNGP04.phx.gbl...
> 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" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im 
> Newsbeitrag news:eRvxET49KHA.5476@TK2MSFTNGP06.phx.gbl...
>> 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" <NoSpam@NoProvider.de> wrote in message 
>> news:eQjf4539KHA.4308@TK2MSFTNGP04.phx.gbl...
>>> 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.
> 
0
Rick
5/22/2010 3:41:51 AM
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" <NoSpam@NoProvider.de> wrote in message 
> news:eQjf4539KHA.4308@TK2MSFTNGP04.phx.gbl...
> > 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" <rick.newsNO.SPAM@NO.SPAMverizon.net> schrieb im 
> > Newsbeitrag news:utHZJO29KHA.4564@TK2MSFTNGP05.phx.gbl...
> >> 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" <NoSpam@NoProvider.de> wrote in message 
> >> news:OsSOEN09KHA.1892@TK2MSFTNGP05.phx.gbl...
> >>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag 
> >>> news:8E6BA483-F87F-4E7C-AD52-749F0362FB99@microsoft.com...
> >>>> 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" <Amin@discussions.microsoft.com> schrieb im Newsbeitrag
> >>>>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E@microsoft.com...
> >>>>> > 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
> >>>>>
> >>>>>
> >>>>> .
> >>>>>
> >>>
> >>>
> >
> > 
> .
> 
0
Utf
5/27/2010 8:55:53 PM
Reply:

Similar Artilces:

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

me w/CERTIFICATE -Digital ID still blocked.
me add signed/approved CERTIFICATE and it show. me at Windows Mail/Tools/Internet Accounts/(*select account)/Poperties/Security/Signing Certificate/Select/(*Select Certificate)/Encrypting Preferences/Select/???/Select Default Account Digital ID/???(only "Cancel" depresion is available [because no CERTIFICATE DISPLAY]). the Algorithm/3DES. me problem lies at the ??? of the formula. me can-not cee now to aplies ze CERTIFICATE to Email Account Setting. ya\ *=item(s) r available. ty 4 yur replies. $$$ $$$ -- UNIVERSAL RULER; OWNER FEDERAL GOVERNMET; GOD OF UNITED STATES;...

how to convert excel's .cvf file to .csv file
...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

Problem converting from Quicken to M2005
My Quicken files are mostly investment related, and generally converted fine. However all bonds (regular and muni's) converted as Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) How do you change the Investment Type for an item? Thank you. In microsoft.public.money, Mike wrote: >My Quicken files are mostly investment related, and generally >converted fine. However all bonds (regular and muni's) converted as >Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) Money typically converts custom data types from Quicken into funds. I thou...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

Numbers converting to decimal
I a trying to figure out why when I type 11 and automatically converts it to .11, if I type 11. it will stay 11,if I change all the cells to text then back to number they willstay. I have checked the formatting of the cells, it even happens when I open a brand new worksheet. Any ideas? Thanks Dawn Hi Dawn, Tools>Option>Edit, uncheck Fixed Decimal -- Kind Regards, Niek Otten Microsoft MVP - Excel "DawnP" <anonymous@discussions.microsoft.com> wrote in message news:c3cf01c48a05$d75359d0$a501280a@phx.gbl... > I a trying to figure out why when I type 11 and &...

Converting Quicken 2004 to Money
Quicken 2004 has many bugs, and I have had it. The most recent being that it doesn't work AT ALL now that it is the year 2004. I have had to change the date on my computer today to open it. I want to get Money instead, however I do not know if Money can get my data from the 2004 version. Does anybody know for sure? Yes is the answer to the question you posed. No is the answer to the question you are getting to but didn't pose. M04 imports Q03 and earlier. If the past predicts the future M05 will import Q04. "Colin" <anonymous@discussions.microsoft.com> wrote ...

Data table
One of our accountants has a .123 file I've converted to Excel, but I'm having major problems with the data table. I've never used a data table before, so that could be the reason for my troubles; I've looked at every article I can find trying to solve this problem. The input cell used in the ..123 file references a cell on another sheet that is a Weekly Sales Projection, used in 3 formulas when the variables are recalculated. When I try referencing that cell as the Column Input cell, I get an error "Input cell reference is not valid." The first formula used...

Converting from AOL to Outlook
Is there an efficient way for me to convert my AOL address book over to Outlook? Before I buy Outlook, I want to be sure I cam make the conversion without loosing the large address book I have on AOL. "Herb Clader" <Herb Clader@discussions.microsoft.com> wrote in message news:44A9739C-A94F-49AB-BF08-31D78A86CCB7@microsoft.com... > Is there an efficient way for me to convert my AOL address book over to > Outlook? > > Before I buy Outlook, I want to be sure I cam make the conversion without > loosing the large address book I have on AOL. AOL's help p...

Customizing "Convert Campaign Response"
We have created custom Fields in the Campaign Response entity that we would like to populate on the Lead Entity when we do a conversion. However it doesn't seem like this is possible from the Data Map. Is there a way to modify what fields are carried forward during a conversion, or do I need to find a different method? Thank you in advance. Hi, As I had some time left, I tried to figure this one out. To my surprise I can't find ANY direct link between a converted campaign response and a new lead. There is only an indirect link, through the campaign entity. But that does not ...

Problem with AFX_THREAD_STATE class converting from VS6.0 to VS.NET 2003
I have inherited some code that works in VS6.0. It has code snippets like the following: _AFX_THREAD_STATE* pThreadState = AfxGetThreadState(); pThreadState->m_nLastHit = 0; and _AFX_THREAD_STATE* pThreadState = AfxGetThreadState(); CToolTipCtrl* pToolTip = pThreadState->m_pToolTip; It then does things with these values. In VS.NET, there is no member variable in _AFX_THREAD_STATE that corresponds to m_nLastHit or m_pToolTip. Have these been replaced by something similar in VS.NET 2003 that I can use? #if (_MFC_VER < 0x0700) _AFX_THREAD_STATE* pThreadState = AfxGetThrea...

Convert English language document to Hindi
Is there any software available to convert an English language text document to Hindi language document by using dictionary based translation (not transliteration). For example: The sentence : My name is Joseph Should be converted to : ???? ??? ????? ?? ...

Converting Hours an minutes just into minutes #2
Is thier a formula to use to change hous and minutes, just into minutes ex: 01:30 (1 hour and 30 minutes) into 90 minutes. And how do you format the cells. Thank you Select the cell and change the format to: [m] -- Gary's Student "Six Sigma Blackbelt" wrote: > Is thier a formula to use to change hous and minutes, just into minutes ex: > 01:30 (1 hour and 30 minutes) into 90 minutes. And how do you format the > cells. > > Thank you ...

converting chart to spreadsheet?
how can i revert back to my spreadsheet from a chart? thanks You mean you have a chart but don't have the data it was created from? This article in the Microsoft knowledge base describes a macro that will extract data from a chart. http://support.microsoft.com/default.aspx?scid=kb;en-us;137016 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "excel chart" <excel chart@discussions.microsoft.com> wrote in message news:7E415558-89B6-457B-A340-2A8CE2BA5521@microsoft.com... > how can i revert back to my s...

graphics converter not working
I am using Publisher 2000 to create a newsletter. It has been going well but now that I'm ready to insert pictures from files, it won't work. I get the message saying that Publisher can't convert this picture (it's either that it doesn't recognize the format or there was an error installing the graphics converter and to go to to Start > Control Panel > Add/Remove Programs > Highlight the Publisher program and choose "Change". When prompted, choose "Add Features". Then be sure to select to run ALL from your computer so that everything is...

Convert VC6.0 CString::Init() code to VC7.1.
How to convert the following code to VC7.1? // Need to declare a descendant of CString to allow access to the Init() member class CStackString : public CString { public: CStackString & operator=(CString const &s); CStackString & operator=(CStackString const &s); using CString::Init; // Make public }; inline CStackString & CStackString::operator=(CString const &s) { CString::operator=(s); return *this; } inline CStackString & CStackString::operator=(CStackString const &s) { CString::operator=(s); return *this; } class CStackValue...

Converting Lotus 123 to Excel
Hi there Is there a staightforward way of converting Lotus 123 worksheets to Excel 2000 without using any third Party Apps and without losing any of the Formula functionality from 123 ? "Paul Duane" <paul.duane@leaseplan.ie> wrote... >Is there a staightforward way of converting Lotus 123 >worksheets to Excel 2000 without using any third Party >Apps and without losing any of the Formula functionality >from 123 ? ..123 files? There are only 3 pieces of software that do anything with them: Lotus 123 itself (32-bit versions), Quattro Pro 10, and DataViz's Conv...

converters
a few years ago I did all my proposals in Apple. Now I want to retrieve some of the data using my Dell and Windows tells me the Apple 31/2 floppy is not formatted! anyone know of a conveter so I can open these floppies? thanks Can u not open these up with a (borrowed, hired or purchased) Apple then eMail / upload those files over the Internet to whatever you need? Hany "WOT231" <jkcorrie@mts.net> wrote in message news:148001c37f19$39d44b30$a001280a@phx.gbl... > a few years ago I did all my proposals in Apple. Now I > want to retrieve some of the data using my Del...