I'm only trying to delete some CONCATENATE()
function from some formulas in Excel.
What I have; here 'Something' could be a lot of things (digits, <, >, texts, etc…
CONCATENATE("Something = "; SUM(A1:A30))
What I want:
SUM(A1:A30)
And it seems very complicated!
I've tried with the search and replace functionality but even as stated here and here the wildcard \number
doesn't seem to work.
What I've tried:
Search:
CONCATENATE("*= ";*))
Replace:
\2
But it only replace with exactly \2
and not the SUM(...)
I've also tried to 'group' the matching characters with surrounding parenthesis : CONCATENATE("(*)= ";(*)))
but it doesn't find anything.
I thought I could do this in 2 steps : first just replace the begining with nothing CONCATENATE("*= ";
and then get rid of the final parenthesis. But Excel won't accept this and I got "There's a problem with this formula" alert dialog.
Edit: Trying to follow @Mayukh’s instructions:
LAMBDA()
withEvaluate()
which is aMacro 4.0 Formula
also requires to save the file asMacro enabled
. Could try by defining in the name manager asEVAL()
which refers to=LAMBDA(α,Evaluate(α))
and then use=EVAL(TEXTAFTER(TEXTBEFORE(C2,")",-1),"; "))
also you can refer this post. Here note hereC2
refer toFORMULATEXT()
returned=CONCATENATE("Something = "; SUM(A1:A30))
=LAMBDA(α,Evaluate(α))
seems not to be an ok formula... I have the "There's a problem with this formula"MS365
! What is yourExcel Version
?Name Manager
--> Click onNew
-->Name
it asEVAL
and in refers to write=LAMBDA(α,EVALUATE(α))
and now in the worksheet, apply the formula as=EVAL()
within the brackets enter the formula returned value which is enclosed within=TEXTAFTER(TEXTBEFORE(FormulaTextReturned,")",-1),"; "))
note the last delimiter forTEXTAFTER()
may differ, because i have taken it assemicolon
as per the post, it may be different