CODEWITHSUNDEEP
vbaexcelexcel-formula
silver_river
silver_river

Reputation: 169

Excel function limit 8192 character

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?

Upvotes: 4

Views: 2164

Answers (3)

Vityata
Vityata

Reputation: 43595

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: enter image description here 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:

enter image description here

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.

Upvotes: 3

John Coleman
John Coleman

Reputation: 52008

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.

Upvotes: 2

CallumDA
CallumDA

Reputation: 12113

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

Upvotes: 3

Related Questions