Converting digits to characters (123 to ABC)
in [Programming]
|
Prev: SEARCH IS NOT WORKING
Next: cell formula question
From: Amin on 19 May 2010 05:22 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(a)discussions.microsoft.com> schrieb im Newsbeitrag > news:97262BCA-C7A0-410E-BB5C-D88639D9D14E(a)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 > > > . >
From: Amin on 19 May 2010 05:30 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
From: Helmut Meukel on 19 May 2010 07:05 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(a)discussions.microsoft.com> schrieb im Newsbeitrag news:8E6BA483-F87F-4E7C-AD52-749F0362FB99(a)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(a)discussions.microsoft.com> schrieb im Newsbeitrag >> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E(a)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 >> >> >> . >>
From: Rick Rothstein on 19 May 2010 10:56 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(a)NoProvider.de> wrote in message news:OsSOEN09KHA.1892(a)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(a)discussions.microsoft.com> schrieb im Newsbeitrag > news:8E6BA483-F87F-4E7C-AD52-749F0362FB99(a)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(a)discussions.microsoft.com> schrieb im Newsbeitrag >>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E(a)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 >>> >>> >>> . >>> > >
From: Helmut Meukel on 19 May 2010 14:09
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(a)NO.SPAMverizon.net> schrieb im Newsbeitrag news:utHZJO29KHA.4564(a)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(a)NoProvider.de> wrote in message > news:OsSOEN09KHA.1892(a)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(a)discussions.microsoft.com> schrieb im Newsbeitrag >> news:8E6BA483-F87F-4E7C-AD52-749F0362FB99(a)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(a)discussions.microsoft.com> schrieb im Newsbeitrag >>>> news:97262BCA-C7A0-410E-BB5C-D88639D9D14E(a)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 >>>> >>>> >>>> . >>>> >> >> |