Excel 8192 character function restriction

Question

I have a formula of more than 10,000 characters in one cell. I cannot reference multiple cells because the requirement limits 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 this formula can help. but this example is a piece of this formula.

In Excel, a Named Range ( Defined Names ) is obtained , which a formula can call a cell based on the name of that range.

is there anything similar for a formula / function? since my function is long due to nested formula.

Can I replace “REPLACE” with my own character? like for example Subte

Answer 1

VBA, , . , , 5-6 . , , , , :

. : .

: Wenn IF

- , . , - :

, , ALT+Enter. , - !:)

: , VBA , :

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

=MyLongFormula2, , .

Answer 2

, VBA, .

, . , SUBSTITITE, . -, instance_num, .

VBA, SUBSTITUTE() , 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

Answer 3

, , "" , , " ". , named formula. , S SUM(), S, , , 100 . , , .

You can use VBA to create UDF functions that are abbreviated aliases for sheet functions, but if you intend to use VBA, you can usually replace the whole formula with functionally equivalent UDFs, and not just replace some of the functions with aliases. See @CallumDA's answer to what I mean by the VBA alias of a worksheet function.