Converting digits to characters (123 to ABC)

[Amin]

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

 

[Helmut Meukel]

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.

 

[Rick Rothstein]

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

 

[Bernd P]

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

 

[J_Knowles]

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,

 

[Amin]

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,

 

[Amin]

Hi HTH,

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

Thanks,

 

[Helmut Meukel]

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.

 

[Rick Rothstein]

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

 

[Helmut Meukel]

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]

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

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.

 

[Helmut Meukel]

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.

 

[Helmut Meukel]

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

Helmut.

 

[Rick Rothstein]

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)

 

[Amin]

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