Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper Contributor

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

12 Replies
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
Oh because it’s for a beta program only and not rolled out for everyone 🫣

@Muhammad_J880 

I didn't check, but most probably it's available at least on Current channel. "Latest Microsoft 365" means practically nothing, more important is it latest for your channel (semi-annual, current, etc), subscription (consumer, business, which one) and platform (Windows, Mac, Android, etc)

I can confirm it's available in Current channel. I run Current Channel (version 2210) on two devices at work and those functions have been available for several weeks at least.

@Sergei Baklan thanks Sergie. Yes, my work laptop has 2202 (semi annual) and this function isn't available. but my personal laptop has version 2210 and its working :)

@Muhammad_J880 

Check which channel do you have on laptop, with 2210 that's most probably Current channel. Functions shall be available on this channel. With semi-annual you have to wait, perhaps till next spring. All depends on your subscription channel.

It appears to have been removed. It was there yesterday, but now the formula I created is broken and it doesn't show up in the list of functions. Such nonsense.
@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.

@AndreDuPlessis 

As a comment, both CELL("filename") and VBA don't work in Excel for web.

Thanks for the comment @Sergei Baklan. I'm not a cloud or web-based Office user myself, as mentioned, so it was pure conjecture, which you confirmed.