Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
the results of the format is a string. You simply want to replaced the 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 stores the number as text so you won't have any problems. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149382 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
posted twice by mistake...
-- Stefano Gatto Have no fear of perfection - you''ll never reach it (Salvador Dali) "Stefano Gatto" wrote: 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
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... -- Stefano Gatto Have no fear of perfection - you''ll never reach it (Salvador Dali) "joel" wrote: the results of the format is a string. You simply want to replaced the 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 stores the number as text so you won't have any problems. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149382 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
My code will work with any size number as long as ther are two decimal places. That is why is used Len(NyStr) - 3 -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149382 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
tried:
Application.DecimalSeparator = "." then switch back to Application.DecimalSeparator = "," ? On 30 Paź, 11:28, Stefano Gatto wrote: 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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
You're right!
:-) -- Stefano Gatto Have no fear of perfection - you''ll never reach it (Salvador Dali) "joel" wrote: My code will work with any size number as long as ther are two decimal places. That is why is used Len(NyStr) - 3 -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149382 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
Yes, I tried, but the FORMAT() function seems not to take care of that Excel
option. .... -- Stefano Gatto Have no fear of perfection - you''ll never reach it (Salvador Dali) "Jarek Kujawa" wrote: tried: Application.DecimalSeparator = "." then switch back to Application.DecimalSeparator = "," ? On 30 Paź, 11:28, Stefano Gatto wrote: 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) . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print numbers with dots, regardless of locale
Yes, I tried, but the FORMAT() function seems not to take care of that Excel
option. Indeed, as stated in the other thread on the same subject, the FORMAT () function uses the Windows setting, not the Excel setting. Phil Hibbs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Print varied page numbers in a single print job without col | Excel Programming | |||
Leading dots...trailing dots | Excel Discussion (Misc queries) | |||
Locale issue | Excel Programming | |||
Print Preview is normal, but numbers do not print, just narrative | Excel Worksheet Functions | |||
concatenate locale bug? | Excel Programming |