How to print numbers with dots, regardless of locale

S

Stefano Gatto

Hello,

I need to print to a disk file, numbers that are stored in different cells
of a worksheet, by making use of the dot as decimal symbol. I thought of
loading them in variants first and printing from there a formatted string to
the file, using the FORMAT function.

However, format "0.00" will use the decimal symbol as defined in the locale
(Regional Settings), which is the opposite of what I need.

Does someone know if FORMAT can be instructed to invariably use a dot as
decimal symbol?

Thank you.

Stefano Gatto
Have no fear of perfection - you''ll never reach it (Salvador Dali)
 
J

joel

the results of the format is a string. You simply want to replaced th
3rd character from the right with a period.


MyStr as string

MyStr = format(activecell.value,"0.00")
MyStr = left(MyStr,len(MyStr) - 3) & "." & Right(MyStr,2)

This will work with any International settings. the file really store
the number as text so you won't have any problems
 
S

Stefano Gatto

Thank you Joel, but this will not work if the number is greater or equal to
100 (or -100).
However the idea of writing whatever comes and replacing it by a dot is not
a bad one, since we are from now on dealing with strings...
 
J

joel

My code will work with any size number as long as ther are two decima
places. That is why is used Len(NyStr) -
 
J

Jarek Kujawa

tried:

Application.DecimalSeparator = "."

then switch back to

Application.DecimalSeparator = ","

?
 
S

Stefano Gatto

Yes, I tried, but the FORMAT() function seems not to take care of that Excel
option.

....
 
P

Phil Hibbs

Yes, I tried, but the FORMAT() function seems not to take care of that Excel

Indeed, as stated in the other thread on the same subject, the FORMAT
() function uses the Windows setting, not the Excel setting.

Phil Hibbs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top