in excel office 365 TEXT function does not work correct

%3CLINGO-SUB%20id%3D%22lingo-sub-1786188%22%20slang%3D%22de-DE%22%3Ein%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786188%22%20slang%3D%22de-DE%22%3E%3CP%3ETEXT%20function%20has%20a%20bug%20in%20office%20365.%20How%20can%20that%20be%20fixed%20with%20workaround%20and%2For%20reported%20to%20Microsoft%3F%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E%3DTEXT(1234567%2C899%3B%22%20%E2%82%AC%230.00)%3C%2FP%3E%3CP%3Eexcel%20365%3A%20%23WERT!(sorry%2C%20German%20version%2C%20no%20idea%20how%20that%20is%20called%20in%20English)%3C%2FP%3E%3CP%3Eexcel%202013%3A%20%E2%82%AC1%2C234%2C567.90%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fde-de%2Foffice%2Ftext-funktion-20d5ac4d-7b94-49fd-bb38-93d29371225c%3Fui%3Dde-de%26amp%3Brs%3Dde-de%26amp%3Bad%3Dde%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fde-de%2Foffice%2Ftext-funktion-20d5ac4d-7b94-49fd-bb38-93d29371225c%3Fui%3Dde-de%26amp%3Brs%3Dde-de%26amp%3Bad%3Dde%3C%2FA%3E%3C%2FP%3E%3CP%3EIn%20this%20link%20above%20there%20is%20this%20link%20(%3CA%20href%3D%22http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F6%2FA%2F8%2F6A818B0B-06F4-4E41-80DE-D383A3B89865%2FTEXT%2520function%2520examples.xlsx)%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F6%2FA%2F8%2F6A818B0B-06F4-4E41-80DE-D383A3B89865%2FTEXT%2520function%2520examples.xlsx)%3C%2FA%3E%20to%20examples%20of%20text%20that%20now%20don't%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1786188%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786267%22%20slang%3D%22en-US%22%3ERe%3A%20in%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834292%22%20target%3D%22_blank%22%3E%40Walphi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhy%20don't%20you%20attach%20the%20file%20directly%20to%20your%20post%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20440px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227062i6B4784E07DF821B5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAs%20for%20the%20formula%20it%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20197px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227065iAE69F8312D4C8C69%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20Excel%20for%20Microsoft%20365%2C%20but%20I'm%20not%20sure%20which%20result%20do%20you%20expect.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786277%22%20slang%3D%22de-DE%22%3ERe%3A%20in%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786277%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20this%20view%20of%20the%20editor%20changes%20my%20typing%3A%20%3CBR%20%2F%3E%20Try%20it%20here%20again%2C%20added%20spaces%3C%2FP%3E%3CP%3ETEXT(1234567%2C899%3B%20%22%20%23%20.%200%20%2C%200%200%20%E2%82%AC%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786283%22%20slang%3D%22de-DE%22%3ERe%3A%20in%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786283%22%20slang%3D%22de-DE%22%3E%3CP%3Ehere%20is%20the%20xlsx-file%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786310%22%20slang%3D%22en-US%22%3ERe%3A%20in%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3ESorry%20this%20editor%20is%20really%20....%3CBR%20%2F%3EIf%20I%20change%20from%20German%20language%20to%20English%20the%20view%20is%20different%2C%20especially%20th%20eformula.%3C%2FP%3E%3CP%3EThe%20xlsx-link%20is%20working%2C%20to%20get%20the%20excel%20sheet%20please%20remove%20the%20last%20char%20%22)%22%20in%20your%20browser%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786374%22%20slang%3D%22en-US%22%3ERe%3A%20in%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834292%22%20target%3D%22_blank%22%3E%40Walphi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20English%20locale%20I%20see%20nothing%20wrong%20in%20the%20file%2C%20not%20sure%20about%20German%20localization.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786377%22%20slang%3D%22de-DE%22%3ERe%3A%20in%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786377%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20a%20screen%20shot%20...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786478%22%20slang%3D%22en-US%22%3ERe%3A%20in%20excel%20office%20365%20TEXT%20function%20does%20not%20work%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834292%22%20target%3D%22_blank%22%3E%40Walphi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20the%20format%20string%20is%20in%20double%20quotes%20it%20will%20not%20be%20'translated'%20into%20a%20German%20locale%20string.%3C%2FP%3E%3CP%3EYou%20have%20to%20change%20the%20formulas%20manually.%20Change%20every%20%2C%20to%20.%20and%20every%20.%20to%20%2C.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTEXT(A4%3B%22%23.%23%23%23%22)%0A%3DTEXT(A5%3B%220.000%2C00%22)%0A%3DTEXT(A6%3B%22%23.%22)%0A%3DTEXT(A7%3B%22%23.%23%23%23%2C0.%22)%0A%3DTEXT(A8%3B%220%2C0..%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EBTW%2C%20I%20did%20not%20get%20an%20%23WERT!%20error%20-%20just%20wrong%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

TEXT function has a bug in office 365. How can that be fixed with workaround and/or reported to Microsoft?

Example: (viewing problem, please remove all spaces in "..." or see attachment of formula)

 

 

=TEXT(1234567,899; "# . # # 0 , 0 0   €")

 

 

excel 365: #WERT!  (sorry, German version, no idea how that is called in English)

excel 2013: 1.234.567,90 €

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

In this link above there is this link (http://download.microsoft.com/download/6/A/8/6A818B0B-06F4-4E41-80DE-D383A3B89865/TEXT%20function%20... ) to TEXT examples which now don't work

10 Replies
Highlighted

@Walphi 

Why don't you attach the file directly to your post?

image.png

As for the formula it is

image.png

in Excel for Microsoft 365, but I'm not sure which result do you expect.

Highlighted

@Sergei Baklan 

Sorry this view of the editor changes my typing:
Try it here again, added spaces

TEXT(1234567,899; " # . # # 0 , 0 0  €")

Highlighted

here is the xlsx-file attached

Highlighted

@Sergei BaklanSorry this editor is really ....
If I change from German language to English the view is different, especially th eformula.

The xlsx-link is working, to get the excel sheet please remove the last char ")" in your browser

Highlighted

@Walphi 

In English locale I see nothing wrong in the file, not sure about German localization.

Highlighted

@Sergei Baklan 

here a screen shot ...

Highlighted

@Walphi 

Because the format string is in double quotes it will not be 'translated' into a German locale string.

You have to change the formulas manually. Change every , to . and every . to ,.

=TEXT(A4;"#.###")
=TEXT(A5;"0.000,00")
=TEXT(A6;"#.")
=TEXT(A7;"#.###,0.")
=TEXT(A8;"0,0..")

BTW, I did not get an #WERT! error - just wrong formatting.

 

Highlighted

@Detlef Lewin 

This is what I get and it was working in Excel 2013:

  result in German Excel356Excel 2013
1234567,89900=TEXT(A5;"#.##0,00 €")#WERT!1.234.567,90 €

 

Well please try the official TEXT example xls sheet from

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

below the table of examples on this page there is a link to it.

If I try to correct the formulas as you mentioned (change . with , and vis-versa) it does not work anymore on my side.  Especially if I enter the . in the format it is not working ...

Highlighted

@Detlef Lewin 

Btw, thanks for your answer

I was playing a little bit and there are interesting results on my side too:

A5=

1234567,89900

=TEXT(A5;"0,00 €")1234,57€
=TEXT(A5*1000;"0,00 €")1234567,90€
=TEXT(A5;"0,00")&" €"1234567,90 €
Highlighted

@Walphi 

I'm using Excel 365 and it's working fine.

No #WERT! error.