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?

Answer 1

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