in excel office 365 TEXT function does not work correct

[Walphi]

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

[#2]

@Walphi 

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

As for the formula it is

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

[#3]

@Sergei Baklan 

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

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

[#4]

here is the xlsx-file attached

[#5]

@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

[#6]

@Walphi 

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

[#7]

@Sergei Baklan 

here a screen shot ...

[#8]

@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.

[#9]

@Detlef Lewin 

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

		result in German Excel356	Excel 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 ...

[#10]

@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 €

[#11]

@Walphi 

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

No #WERT! error.

[#12]

@Detlef Lewin 
After loading your xlsx-file, I see everything as it should be. After activation of "Bearbeitungsmode" (sorry don't know the English term here) there is still the same. If I click into each of the C and D columns into the formula and press enter then I see this:

... just click into the formula, go to the end of it and press enter.

The strange thing is that the German Text-function is named equal as the English version:

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

and the English version:
https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c

[#13]

@Detlef Lewin 
If I open your file than everything looks fine, after enabling "Bearbeitungsmode" still everything fine. If I click in any C or D formula go to the end and press Enter (as I would type it in), I can see again the #WERT! error.

 The English and the German TEXT command is named equal, the parameters especially the format of the string is different. This seems to be not working correct. If I try the exact examples of the official microsoft webpage ...
https://support.microsoft.com/de-de/office/text-funktion-20d5ac4d-7b94-49fd-bb38-93d29371225c

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

... both versions are not working, for the English example I need to adapt it a little bit, the '.' with ',' and ',' with ';'. But anyway both are not working:

[#14]

@Sergei Baklan 

I see that you are using the English Excel. This error occurs in the German version of Excel. The TEXT function is named equal, the format of strings is different. Please see my last comments here from today.

BR Walphi

[#15]

@Walphi 

Sorry. but I can't check - in English version it works, but in German one most probably format texts like "0,000.00" are to be manually adjusted to German locale. I don't know it's specific.