Excel function TEXT with custom number format using "."

%3CLINGO-SUB%20id%3D%22lingo-sub-1803006%22%20slang%3D%22en-US%22%3EExcel%20function%20TEXT%20with%20custom%20number%20format%20using%20%22.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1803006%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20formula%20like%20%3DTEXT(value%3B%22000000000000.00%22)%20and%20it%20properly%20works%20in%20my%20EXCEL%202016%20the%20value%20of%2012345%20is%20presented%20as%20123.45%26nbsp%3B%20but%20do%20not%20work%20in%20my%20customer's%20environment%20(also%202016)%20there%20is%20na%20%23ARG!%20error.%20Point%20as%20a%20coma%20separator%20is%20not%20allowed%20there.%20In%20fact%20in%20my%20environment%20it%20is%20not%20treated%20as%20a%20coma%20separator%20--%26gt%3B%2012345%20is%20presented%20as%20123.45.%20I%20have%20checked%20many%20settings%20(region%2C%20language%2C%20numbers%20format...)%20It%20is%20the%20same%2C%20so%20%3CSTRONG%3Ewhat%20determines%20acceptable%20custom%20number%20formats%3C%2FSTRONG%3E%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1803006%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1803281%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20function%20TEXT%20with%20custom%20number%20format%20using%20%22.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1803281%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F840448%22%20target%3D%22_blank%22%3E%40AndrzejuZ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3D%20TEXT%20(1234%2C%20%220000000%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EAdding%20zeros%20(0)%2C%20e.g.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EB.%200001234%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId-580978412%22%20id%3D%22toc-hId-580978439%22%3E%3CFONT%20size%3D%224%22%3ETEXT%20function%3C%2FFONT%3E%3C%2FH1%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ftext-function-20d5ac4d-7b94-49fd-bb38-93d29371225c%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Ftext-function-20d5ac4d-7b94-49fd-bb38-93d29371225c%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

I am trying to use formula like =TEXT(value;"000000000000.00") and it properly works in my EXCEL 2016 the value of 12345 is presented as 123.45  but do not work in my customer's environment (also 2016) there is na #ARG! error. Point as a coma separator is not allowed there. In fact in my environment it is not treated as a coma separator --> 12345 is presented as 123.45. I have checked many settings (region, language, numbers format...) It is the same, so what determines acceptable custom number formats?

2 Replies
Highlighted

@AndrzejuZ 

 

= TEXT (1234, "0000000")

Adding leading zeros (0), e.g. B. 0001234

TEXT function

https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c?ui=en-...

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

 

Highlighted

@Nikolino 

Thanks for quick response.

I know this support information, but it doesn't help in this case

I need to set format "000000000000.00" so for amount 12345,67 I need text 000000012345.67

It means leading zeros and point instead of coma. This format is available in my Excel but not available in another environment so I asked which excel or windows settings determine formats availability?

At the moment I did a workaround by dividing the amount and joining separate texts. It works but I just wonder how it happens.