Excel function limit 8192 character

Question

I have more than 10,000 characters formula in one cell. I can't refer to multiple cell because the requirement limit it.

Example:

=IF(NOT(ISERROR(SEARCH(String1,IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))="","",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))),"")))),SUBSTITUTE(IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))="","",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))),""),String1,$G2),IF(NOT(ISERROR(SEARCH(String2,IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))="","",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))),"")))),SUBSTITUTE(IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))="","",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))),""),String2,$F2), IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))="","",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,"&","_"),"-","_")," ",""),":","_")),COLUMNS($K$1:K$1))),"")))

I am not sure if that formula might help. but that example piece of that formula.

In Excel got Named Range (Defined Names) which a formula might call cell based on name of that range.

is there anything like that for formula/function? since my function long because of nested formula.

Can I replace "SUBSTITUTE" with character of my own? like ex. SUBTE?

Have you considered VBA? Formulas with thousands of characters are a pain to debug. — John Coleman, Feb 07 '17 at 16:28
VBA quite messy for others who don't know it. Trying to explaining will triple my work. Plus, I am not that good with VBA. — silver_river, Feb 07 '17 at 16:30
@silver_river I couldn't disagree more. I have made no effort whatsoever to understand your formula *simply because of how messy it is in it's current state* — CallumDA, Feb 07 '17 at 16:31
What does your formula do? Also, is part of what you're looking for a way to explain the formula...? Why does it matter if trying to explain VBA to folks will triple the work? Also...how so? I think a nicely written VBA macro would be *much* easier, than explaining your `Iserror` (what happens if an error, go to the end after a ton of formulas in between), a bunch of stacked `Substitute` formulas, etc.) IMO, explaining your formula, if it's *over 10,000 characters* will take a much longer time to explain, and a nicely structured Macro, which tends to be more or less straightforward... — BruceWayne, Feb 07 '17 at 16:33
And explaining a 10,000 character formula that *begins* `=IF(NOT(ISERROR(SEARCH(String1,IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE` is easy to explain? Formulas like that are essentially write-only in the sense that almost no one but their author can understand them (and, often the author themselves don't, not completely) where as equivalent VBA is easy to understand and easy to share. — John Coleman, Feb 07 '17 at 16:34
But -- I don't think any downvote is deserved. It is still a good question. — John Coleman, Feb 07 '17 at 16:36
I am too far, and at the end of phase. Doing macro now is too late now, and also I am not that good with macro. In this example https://www.mrexcel.com/forum/excel-questions/399784-substitute-multiple-characters.html can see a long SUBSTITUTE to replace the alphabet, any good other way to replace it? Maybe use only one SUBSTITUTE? — silver_river, Feb 07 '17 at 16:41
Quite the contrary. If you explain the problem to us well we can help with the VBA code. Then it is just a case of putting the code into the right place and you'll be done. — CallumDA, Feb 07 '17 at 16:44

Vityata · Answer 1 · 2017-02-07T18:03:34.533

VBA is probably the best option. Still, if you cannot consider it, think about splitting the formula into 5 to 6 smaller formulas. It can be better and the people who are using the formula have more chances to understand what is happening:

E.g. like this: Here the formula on the second line is splitted into two other formulas on the first line.

Note: Wenn means IF

Furthermore - when you work with such big formulas, always try to format them in a meaningful way. E.g., something like this:

This is achieved with clicking on the place where you want the new line, pressing ALT+Enter. On behalf of all people, dealing with badly written formulas on a daily basis - thank you! :)

Edit: Anyhow, if VBA was an option, the worst case scenario would have looked like this:

Option Explicit

Public Function MyLongFormula() As String

    Dim strResult As String

    Application.Volatile

    strResult = "=IF(NOT(ISERROR(SEARCH(String1,IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),"
    strResult = strResult & "COLUMNS($K$1:K$1))="""","""",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),"
    strResult = strResult & "COLUMNS($K$1:K$1))),"""")))),SUBSTITUTE(IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),"
    strResult = strResult & "COLUMNS($K$1:K$1))="""","""",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),COLUMNS($K$1:K$1))),""""),"
    strResult = strResult & "String1,$G2),IF(NOT(ISERROR(SEARCH(String2,IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),"
    strResult = strResult & "COLUMNS($K$1:K$1))="""","""",INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),COLUMNS($K$1:K$1))),"""")))),"
    strResult = strResult & "SUBSTITUTE(IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),COLUMNS($K$1:K$1))="""","""","
    strResult = strResult & "INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),COLUMNS($K$1:K$1))),""""),String2,$F2),"
    strResult = strResult & "IFERROR(IF(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),COLUMNS($K$1:K$1))="""","""","
    strResult = strResult & "INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($J2,""&"",""_""),""-"",""_""),"" "",""""),"":"",""_"")),COLUMNS($K$1:K$1))),"""")))"

    MyLongFormula = Evaluate(strResult)

End Function

Public Function MyLongFormula2() As String

    Application.Volatile
    MyLongFormula2 = Evaluate("=sum(1,2)")

End Function

Just write =MyLongFormula2 to get a feeling how it is expected to work.

Good suggestion with the `ALT+Enter`. I've often been mystified that Excel's facilities for editing formulas is as poor as it is. — John Coleman, Feb 07 '17 at 17:03
Unfortunately OP says: *I can't refer to multiple cell because the requirement limit it* — CallumDA, Feb 07 '17 at 17:10
I think the problem with SUBSTITUTE nesting. Is there any way to replace nesting with 'or'? Example: `=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H2,"&","_"),"‌-","_")," ",""),":","_")),A1,"")` to replace with like `=SUBSTITUTE($H2,"&" or ":" or ",",A1,"")`. '**or**' in **old text** in SUBSTITUTE function. — silver_river, Feb 07 '17 at 17:47
@silver_river - without VBA this is probably the best option - http://stackoverflow.com/questions/22313965/how-can-i-combine-multiple-nested-substitute-functions-in-excel — Vityata, Feb 07 '17 at 18:05

score 3 · Answer 2 · answered Feb 07 '17 at 17:16

As previously suggested, the best solution is almost certainly to write a neat VBA function which does everything for you.

However, as you explicitly asked for it. Here is the code for a function which is just a SUBSTITITE but shorter in length. I couldn't see anywhere where you used the instance_num parameter so I didn't build it in.

You need to add the code below to a new module in the VBA editor and then you can use the SUBSTITUTE() function on the worksheet by just writing SU()

Function SU(txt As String, old_text As String, new_text As String) As String
    SU = Application.WorksheetFunction.Substitute(txt, old_text, new_text)
End Function

John Coleman · Answer 3 · 2017-02-07T17:20:54.793

The answer to your question is, unfortunately, "no", or at least "not quite". You can't literally name a function, but you can use named formulas. For example, you can't use names to create an alias, S, for the function SUM(), but you can create a named formula, S, that, when it occurs inside another formula will sum the 100 elements above the cell. With a certain amount of cleverness, you can usually use named formulas to shorten very long formulas, but not in a way as mechanical as introducting abbreviations of functions.

You could use VBA to create UDF functions which are abbreviated aliases for worksheet functions, but if you are going to use VBA you can usually replace the entire formula by a functionally equivalent UDF rather than simply replace some of the functions by aliases. See the answer of @CallumDA for what I mean by a VBA alias of a worksheet function.

Thank you. Will look into it. – silver_river Feb 07 '17 at 16:48 — silver_river, Feb 07 '17 at 16:48

Excel function limit 8192 character

3 Answers3