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" wrote in message
...
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" schrieb im
Newsbeitrag ...
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" wrote in message
...
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.