=SUBSTITUTE formula limit

Question

I've got a long nested =substitute formula and would like it to be longer, but excel is telling me it's too long. Googling, I found the limit for the number of functions in nested formulas is 64 but I've only got 24. Any ideas why I can't make mine longer than 24?

Answer 1

I found the limit for the number of functions in nested formulas is 64 but I've only got 24. Any ideas why I can't make mine longer than 24?

Refer to https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 .

There are also limits on the length of a formula. Ostensibly, 8192 characters. But also an "internal length" of 16,384.

I don't know how the "internal length" is determined. Perhaps it is simply 16 bits per character instead of 8 bits. But it could refer to the internal encoding of a formula. For example, function names are probably replaced by an index into a name table. Strings might also be replaced by an index into a table of constants.

Be that as it may....

---

This is the largest that Excel will accept.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"AGCH ",""),"OTCH ",""),"CT ",""),"RACH ",""),"MACH ",""),"MACH2 ",""),"MACH3 ",""),"MACH4 ",""),"MACH5 ",""),"MACH6 ",""),"GCH ",""),"PACH ",""),"PACH2 ",""),"PACH3 ",""),"DC ",""),"GCHB ",""),"GCHS ",""),"GCHG ",""),"GCHP ",""),"DC ",""),"TC ",""),"AFC ",""),"FC ",""),"CH ","")

First, that is "only" 548 characters.

Second, I (using Excel 2010) have no problem adding at least one more level, to wit:

=SUBSTITUTE(SUBSTITUTE(...,"CH ",""),"foobar ","")

In fact, using 6-character strings to match (like "MACH3 "), I can add up to 41 levels of the form SUBSTITUTE(...,"xxxxx ",""), for a total of 64 nested functions plus the outer function (65 total functions). The total length is 1532 characters.

---

So again, it would behoove you to upload an example Excel file that demonstrates the problem to a file-sharing website, and post a download URL that does not require that we log in.

I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.

Also, show us the additional SUBSTITUTE expressions that you are attempting to append.

---

In the meantime, I think we should try to come up with a better solution for you.

To that end, are you amenable to a solution that depends on new Office 365 Excel features such as LET, LAMBDA, etc?

Are you amenable to a VBA solution?

Answer 2

You have not answered my question. In the absence of which i cannot help you.

Answer 3

Here is the nested formula I am using. It's working well but I would like to add a bit more to it. This is the largest that Excel will accept. If I add another 'nest', I get told it's too big.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"AGCH ",""),"OTCH ",""),"CT ",""),"RACH ",""),"MACH ",""),"MACH2 ",""),"MACH3 ",""),"MACH4 ",""),"MACH5 ",""),"MACH6 ",""),"GCH ",""),"PACH ",""),"PACH2 ",""),"PACH3 ",""),"DC ",""),"GCHB ",""),"GCHS ",""),"GCHG ",""),"GCHP ",""),"DC ",""),"TC ",""),"AFC ",""),"FC ",""),"CH ","")

Answer 4

Hi,

Share some data, describe the question and show the expected result.

Answer 5

Hello Laura Wright

Welcome to the Microsoft community.

Based on your current scenario, it seems that the =SUBSTITUTE nested formula was prompted too long , the limit for the number of functions in a nested formula could be affected by several factors, including the version of Excel you are using and the complexity of the formula. If you are using an older version of Excel, the limit may be lower.

Additionally, other factors such as the size of your worksheet, the amount of memory available, and the processing power of your computer can also impact the limit for nested formulas.

To resolve the issue, please try to share your formulas or files and some details of what you are going to get, it may help us to investigate it further.

Best Regards,

Chandy |Microsoft Community Support Specialist