0

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

Improve this question
8
  • 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))
    – Mayukh Bhattacharya
    Commented Feb 15 at 23:03
  • 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"
    – Seba99
    Commented Feb 15 at 23:15
  • The said formulas works with MS365 ! What is your Excel Version?
    – Mayukh Bhattacharya
    Commented Feb 15 at 23:16
  • I have Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20194) 64 bits
    – Seba99
    Commented Feb 15 at 23:35
  • 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
    – Mayukh Bhattacharya
    Commented Feb 15 at 23:38

1 Answer 1

Reset to default
0

Not using VBA but using Excel4.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 Excel4.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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .