Forum Discussion
donnawoman
Jul 10, 2021Copper Contributor
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.
- DaveG27Copper 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.
- AndreDuPlessisCopper Contributordonnawoman, 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.As a comment, both CELL("filename") and VBA don't work in Excel for web.
- AndreDuPlessisCopper ContributorThanks for the comment SergeiBaklan. I'm not a cloud or web-based Office user myself, as mentioned, so it was pure conjecture, which you confirmed.
- LorenzoSilver ContributorAs 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- Muhammad_J880Copper Contributor
https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29
I also have same issue and I am using latest 365
- Muhammad_J880Copper ContributorOh because it’s for a beta program only and not rolled out for everyone 🫣