Forum Discussion

donnawoman's avatar
donnawoman
Copper Contributor
Jul 10, 2021

why doesn't Excel recognize the TEXTBEFORE() and TEXTAFTER() functions when I try to use them?

When I attempt to use these functions, Excel returns the #NAME? error message.

  • DaveG27's avatar
    DaveG27
    Copper Contributor

    donnawoman thanks to ChatGPT, I was given this answer which works as desired (in my case, my text was in A4, between a '.' and a '@'):

     

    =LEFT(MID(A4, FIND(".", A4) + 1, LEN(A4)), FIND("@", MID(A4, FIND(".", A4) + 1, LEN(A4))) - 1)

     

    Explanation:

    • FIND(".", A4): Finds the position of the first occurrence of "." in cell A4.
    • MID(A4, FIND(".", A4) + 1, LEN(A4)): Extracts the substring of A4 starting from one character after the first "." to the end.
    • FIND("@", MID(A4, FIND(".", A4) + 1, LEN(A4))): Finds the position of the "@" symbol within the extracted substring.
    • LEFT(..., FIND(...) - 1): Takes the left part of the extracted substring, ending just before the "@" symbol.

    This formula essentially extracts the text between the first "." and "@" symbols in cell A4.

  • AndreDuPlessis's avatar
    AndreDuPlessis
    Copper Contributor
    donnawoman, I was trying to figure this behavior out myself and realized I'll have to make alternative plans.
    The following work-around might get you going:
    It's not clear which version of Excel you are using.
    You can try the following as it works on Desktop editions, and probably on cloud-based versions of Office 365 as well:
    For this case, don’t Use: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) - It only shows CURRENT ACTIVE Sheet Names, regardless of where you might need it in the Workbook.

    "Do Use: =RIGHT(CELL(""filename"",A1),LEN(CELL(""filename"",A1))-FIND(""]"",CELL(""filename"",A1))) - It gives the data you need in the Cell Address Specified. (this case it's ""A1"").

    Paste the formula in the cell where you need it and simply change the Cell Addresses ""A1"" to the address of the cell you are using it in."

    Alternatively, you can create your own User Defined Function (UDF), call it whatever you want (as long as it's name doesn't clash with any existing UDFs or standard Excel functions).
    To learn how to do that is not too tricky, simply reference the UDF documentation for the relevant Excel version and type you are using. Making a UDF to suit all variations of Excel will be possible, but quite a bit more involved as it depends on a number of factors not directly related to the version of Excel you might be using.

    Hope it helps you or someone else.
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    As of today none of those functions exist, hence the #NAME? error you get
    Suggest you expose what you expect to do + upload a sample with the expected result

Resources