Forum Discussion

Walphi's avatar
Walphi
Copper Contributor
Oct 15, 2020

in excel office 365 TEXT function does not work correct

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) ...
excel
Walphi's avatar
Walphi
Copper Contributor
Oct 21, 2020

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-de&rs=de-de&ad=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 ...

  • Walphi's avatar
    Walphi
    Copper Contributor
    Feb 03, 2021

    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:

     

  • Walphi's avatar
    Walphi
    Copper Contributor
    Feb 02, 2021

    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-93d29371225chttps://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

Resources