0

Here have some information in cell A1.

Then cell B1 has a formula (called "Formula1") to modify A1, resulting in B1 value.

Then cell C1 has a formula (called "Formula2") to modify B1 whose value was created after applying its formula to A1, resulting in C1 value.

Is there a way that I can write a formula which asks him in a single cell to do first formula 1 then formula 2 without needing to write 2 different formulas in different cells?

I tried writing in the formula box like this but it is comparing one to another instead of doing one then the other with the result of the first one:

=Formula1

=Formula2

Also tried =Formula1&Formula2 and =Formula1+Formula2, but they do other things...

How could I write formulas in a single cell saying:

=Formula1 THEN Formula2 THEN Formula3, etc. (each higher formula being done to the result of the former one)

Thanks

Example

Input Cells

A1:Michael.John$Josh.Andrews

A2:Michael%Josh.Andrews

A3:Michael John

A4:Michael.John

Formula1 for column B: =LEFT(A1,FIND("$",A1& "$")-1)

Formula2 for column C: =IF(SUMPRODUCT(LEN(B1)-LEN(SUBSTITUTE(B1,"$","")))=1,LEFT(B1,FIND("$",B1)-1),IF(SUMPRODUCT(LEN(B1)-LEN(SUBSTITUTE(B1,"%","")))=1,MID(B1,FIND("%",B1)+1,LEN(B1)-FIND("%",B1)),IF(SUMPRODUCT(LEN(B1)-LEN(SUBSTITUTE(B1,".","")))>=1,B1,"")))

I am having to write column A, Then do formula1 for getting column B, Then again do formula2 on top of column B value after its formula 1 result, for just then to get column C. This is making the excel chart display information that I dont want it to appear nor calculate by parts, but show only final result calculated at once.

I would like to have only columns A and B in which A is the input, and B has all the formulas calculated at once. How do I do one single formula with those 2 formulas to be done first formula1 and then formula2 applied to formula1 result directly?

Thanks

enter image description here

Improve this question
1

Answer to your original question, "Is there a way that I can write a formula which asks him in a single cell to do first formula 1 then formula 2 without needing to write 2 different formulas in different cells?" - NO, it doesn't exist until the latest releases of Excel 2016.

There are many effective ways of writing formulas is excel, and based upon the examples you have provided, I believe the below should serve your needs

=IF(ISERROR(FIND(".",MID(A1,IFERROR(FIND("%",A1)+1,1),LEN(LEFT(A1,FIND("$",A1& "$")-1))-IFERROR(FIND("%",A1),0)))),"",MID(A1,IFERROR(FIND("%",A1)+1,1),LEN(LEFT(A1,FIND("$",A1& "$")-1))-IFERROR(FIND("%",A1),0)))

Since you have not actually stated your requirements, I can only assume that you are trying to achieve the below:

  1. Truncate any text after $ symbol, if present, AND THEN
  2. Truncate any text before % symbol, if present, AND THEN
  3. Check for the presence of a "." symbol, if not present blank out else accept the output of the previous step

Have a go and let us know your thoughts.

Improve this answer
16
  • I also noticed a trivial but important detail in your original formula, you are checking for exactly '1' occurrence of the "%" symbol before dropping off any text before that "%" symbol, If not, then you move on to checking the presence of a ".". There may be a flaw in this approach compared to what you intend to achieve. – Bharat Anand Jan 19 '18 at 0:42
  • Hello Bharat, What I need to do is as follows in this order: * If $ exists, get whats behind the first $ that appear on cell only (excluding $), only if it has points inside the text (not after, not before the text). * If didnt get result yet, then check for the appearance of the first % in the cell, if it exists, get result whats after it (excluding %) only if it has points inside (not after nor before the text). * If $ nor % exist, then copy the cell as result only if it has point inside (not before nor after text). If there is no point, result is blank. Thanks – Joao Jan 19 '18 at 0:47
  • You need to elaborate your requirements more clearly, with all ifs and else-ifs covered before we continue to work further on this, or state what is not met in the solution that I have provided. At present you seem to be drip-feeding and developing your requirements which is proving to be counter-productive for many of us – Bharat Anand Jan 19 '18 at 0:56
  • Hello Bharat, thanks for your help. If you could check following details please, would be perfect, as follows: 1) After all the formula did, if still appears in the result a $ then to that result, delete the $ and whats after it to the right. Example: Cell: MichaelMichael%John.John$This is a test for a long sentence Result now is: John.John$This is a test (its overpassing the $) Result should be: John.John (after it gets this final result of this % searching, delete whatever comes after a $ including it) – Joao Jan 19 '18 at 1:33
  • 2) Also, the formula is cutting some part of the text when $ and % happen: Example: Cell: MichaelMichael$John.John%This is .a test for a long sentence Result now is: This is .a tes (result would be correct if it didnt stop in half of the sentence, not printing all characters) Result should be: This is .a test for a long sentence (in here its grabbing correct information but cutting it for some reason) Rest is all perfect. Thanks – Joao Jan 19 '18 at 1:34
0

Yes, you'll build a complex formula, using substitution of the prior formula's as inputs. Assuming something like:

A1 =2 (your input)
B1 =A1*2 (your first operation)
C1 =B1/12 (your second operation)

A single formula might be C1 =(A1*2)/12, using () to force an order of operation.

Improve this answer
3
  • Hello Dav, in your example, it works fine because your formulas are made up only with numbers not with functions. But if inside B1 the formula is IF, FIND, SUBSTITUTE, LEN, MID, LEFT, RIGHT, SUMPRODUCT, etc. multiple things and then C1 needs to do some lots of things to B1 result, then how to merge all so it makes one and the other and instead of having cells A1, B1, C1, only have A1 with input and B1 whith the whole stuff to calculate and then calculate on top of the results of first calculation? In other words, how do you write C1 =(A1*2)/12 using B1 cell in this formula instead of (A1*2)? – Joao Jan 18 '18 at 21:31
  • 1
    @Joao it would be easier to answer if you gave a real life example of the input and what you expect as the output, as well as the actual formula you have tried. – Scott Craner Jan 18 '18 at 21:38
  • Hello Scott, have added example above in original text. thanks – Joao Jan 18 '18 at 22:10
0

Since you’ve accepted an answer that doesn’t answer the question you asked, I’ll offer another one.

To rephrase your question, you have two functions, which you call "Formula1" and "Formula2"; I’ll call them f() and g().  You are currently defining B1=f(A1) and C1=g(B1), and you want to define B1=g(f(A1)), eliminating the middle man.

It would be nice if you explained why you want to do this.  After all, this is such a common technique that we often recommend it in answers here.  If a problem requires a particularly complicated expression, we frequently advise the asker to create a “helper column” to hold an intermediate result.  The final, desired result is then generated using the helper column as input.

But, if you just don’t want to see the B1 (i.e., f(A1)) value on your spreadsheet,

  • The simple fix is to hide Column B.
  • The more complicated fix is to move the f(A1) formula from B1 to Z1 (or some other out-of-the-way column), and then set B1=g(Z1).  If you want, you can hide Column Z.

  • An even more advanced approach is to implement g() as a VBA user-defined function.  Then you can define B1 as

    =g(LEFT(A1,FIND("$",A1&"$")-1))

    using your Formula1 in worksheet formula form.

  • Or you could take this to its logical conclusion: implement f() and g() as VBA user-defined functions, and define B1 as

    =g(f(A1))

    You can, of course, give the functions names that reflect what they do.

Improve this answer
-2

Actually, the ability to obtain the formula has existed in Excel for 30 years.

The Excel 4 macro function GET was able to do so, as one of the MANY things it could do, and one used it like one does the better known, nowadays, EVALUATE function.

It's pointless to expand further on that, since FORMULATEXT() now exists.

But for the record, it could be done and easily, for a very, very long time. Perhaps why MS was so very, very long in producing a function like FORMULATEXT()? And similarly, perhaps why MS is so, apparently, resistant to the very obvious need for a simple cell-side formula to convert a text string directly into a formula. Yes, EVALUATE works fine-ish, (emphasis on "-ish"), but it isn't cell-side, and it is difficult, usually, to use inside a formula, due to the indirect way of using it. So no formula snippet tables for instance from which one builds a formula from text snippets and a lookup based upon factors in the data at hand. Therefore, no simple formulas in cells with easy to maintain snippets. (Also, no, INDIRECT() is utterly not something useful for what I describe.)

But Bharat Anand was incorrect in stating it oculd not be done back then. GET function, option 6.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.