Converting digits to characters (123 to ABC)
in [Programming]
|
Prev: SEARCH IS NOT WORKING
Next: cell formula question
From: Rick Rothstein on 19 May 2010 14:54 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(a)NoProvider.de> wrote in message news:eQjf4539KHA.4308(a)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(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 >>>>> >>>>> >>>>> . >>>>> >>> >>> > >
From: Rick Rothstein on 19 May 2010 14:59 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(a)NoProvider.de> wrote in message news:eQjf4539KHA.4308(a)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(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 >>>>> >>>>> >>>>> . >>>>> >>> >>> > >
From: Helmut Meukel on 19 May 2010 18:23 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(a)NO.SPAMverizon.net> schrieb im Newsbeitrag news:eRvxET49KHA.5476(a)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(a)NoProvider.de> wrote in message > news:eQjf4539KHA.4308(a)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.
From: Helmut Meukel on 19 May 2010 18:29 I caught it, but it was a so stimulative conversation ... Helmut. "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> schrieb im Newsbeitrag news:eUhc%23V49KHA.5476(a)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) > >
From: Rick Rothstein on 21 May 2010 23:41
> 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(a)NoProvider.de> wrote in message news:eiUssH69KHA.4308(a)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(a)NO.SPAMverizon.net> schrieb im > Newsbeitrag news:eRvxET49KHA.5476(a)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(a)NoProvider.de> wrote in message >> news:eQjf4539KHA.4308(a)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. > |