Oct 15 2020 02:59 PM - edited Oct 15 2020 04:06 PM
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 €
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
Oct 15 2020 03:14 PM
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.
Oct 15 2020 03:19 PM
Sorry this view of the editor changes my typing:
Try it here again, added spaces
TEXT(1234567,899; " # . # # 0 , 0 0 €")
Oct 15 2020 03:29 PM
here is the xlsx-file attached
Oct 15 2020 03:47 PM
@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
Oct 15 2020 04:09 PM
In English locale I see nothing wrong in the file, not sure about German localization.
Oct 15 2020 04:13 PM
here a screen shot ...
Oct 15 2020 05:04 PM
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.
Oct 21 2020 02:48 PM
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
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 ...
Oct 21 2020 02:56 PM
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 € |
Oct 21 2020 03:03 PM
Feb 02 2021 03:03 PM - edited Feb 03 2021 12:38 PM
@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
Feb 03 2021 01:36 PM - edited Feb 03 2021 01:43 PM
@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:
Feb 03 2021 01:51 PM
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
Feb 03 2021 02:04 PM
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.