Replace with wildcards in Excel or batch edit some wrapping formula

Question

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:

enter image description here

• Mayukh Bhattacharya: Probably using LAMBDA() with Evaluate() which is a Macro 4.0 Formula also requires to save the file as Macro enabled. Could try by defining in the name manager as EVAL() which refers to =LAMBDA(α,Evaluate(α)) and then use =EVAL(TEXTAFTER(TEXTBEFORE(C2,")",-1),"; ")) also you can refer this post. Here note here C2 refer to FORMULATEXT() returned =CONCATENATE("Something = "; SUM(A1:A30))
• Seba99: Hmmm I don't really get what you suggest ... I've tried the worflow on the link you provided to "define Name" But =LAMBDA(α,Evaluate(α))seems not to be an ok formula... I have the "There's a problem with this formula"
• Mayukh Bhattacharya: The said formulas works with MS365 ! What is your Excel Version?
• Seba99: I have Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20194) 64 bits
• Mayukh Bhattacharya: Great! The first goto Name Manager --> Click on New --> Name it as EVAL 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 for TEXTAFTER() may differ, because i have taken it as semicolon as per the post, it may be different

Answer 1

Not using VBA but using Excel^4.0 Macro Functions with Modern Excel Functions - MS365 is one way of doing this. Although the workbook needs to be saved as either .xlsm or .xlsb

---

enter image description here

---

• Formula used in cell C4

=EVAL(TEXTAFTER(TEXTBEFORE(C2,")",-1),"; "))

---

Where EVAL() is a defined-named function which refers to:

=LAMBDA(α,EVALUATE(α))

---

Reference to a solution posted earlier in StackOverflow --> Text Maths Equation To Formula which is commented above.

---

Also read here on the same MSFT Documentations -- Exel 4.0 Macro Functions

---

List of all the Excel^4.0 Macro Functions can be found here

---

Note: Separators may differ as per one's regional settings. For me it is comma, but for OP as it seems could be semi-colon --> ; or dot --> . or Slash --> \ . For more clarity one can read here on International-Delimiters

---