Format decimal comma and point

[zovche]

Hi, my local settings for numbers are decimal comma (,) and point (.) for thousand separator.

I have one workbook now where I need to use decimal point instead, without thousand separator. But I don't want to change my regional setting nor Excel settings (File -> Options -> Advanced) because that way that option stays for any other excel which I don't want.

Can I somehow define formats just for this specific workbook and nothing else, or for specific cells with formula or VBA?

Thank you.

 

[oldbrewer]

you can custom format one or more cells....

 

[Joe4]

Can't you just highlight the whole section you want to apply this to (whole sheet, specific columns, etc), and just go to the Format menu and change the format to what you want (no VBA required!)?

 

[zovche]

if you mean on format cells and choosing custom, it's not working maybe I'm doing it wrong, but if I have number 34,05 and I custom it and type #.## it converts number into 34050 without decimals, as it recognize it as thousand separator. Maybe there's some trick, but as soon I write dot it understands it as kilo. But like I said maybe i'm just not familiar with a trick that would do it so

 

[Rhodie72]

if you mean on format cells and choosing custom, it's not working maybe I'm doing it wrong, but if I have number 34,05 and I custom it and type #.## it converts number into 34050 without decimals, as it recognize it as thousand separator. Maybe there's some trick, but as soon I write dot it understands it as kilo. But like I said maybe i'm just not familiar with a trick that would do it so

Maybe then what you want to do is divide the new result by 1000?

 

[zovche]

Maybe then what you want to do is divide the new result by 1000?

Hm, thank you for suggestion, but data is from external sources so I would have to multiply it in SQL line, and still not sure if it would take dot as a decimal point at the end. So far I did following:
I have macro to generate XML at the end of story, and for that XML I need decimal dots. So Before creating XML via macro I put

    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ""
    Application.UseSystemSeparators = False

and at the end of macro I returned

    Application.DecimalSeparator = ","
    Application.ThousandsSeparator = "."
    Application.UseSystemSeparators = True

So in single click it will do the trick, will save XML with dots as decimal and return back to regular settings, however it's pretty shame not to be able to change number format in any other way then changing Operating System Regional settings.

 

[oldbrewer]

34,05			3	5	34	0.05	34.05
			pos of ","	No of chars	left of comma	right of comma / 100
			or find "," replace with "."

<colgroup><col span="4"><col><col><col><col span="2"></colgroup><tbody>
</tbody>