1

I am working on a way of building out a formula rather than a bunch of if/then statements. Is there a way I can convert the text from one cell and use that text as the formula of another?

Example:

A1 = 1
A2 = 2

B1's formula reads =A1+A2, resulting in the value of 3 in cell B1

In C1, if I have the text value of A1+A2, is there a way in cell D1 I can reference the text of C1 to display the value of 3?

I tried ="=" & indirect(C1) and =indirect("=" & C1) but both fail.

Improve this question
5
  • 1
    Have you read Convert a text string to a formula in a spreadsheet? Four years old, which is a long time, but off the top of my head, I can't think of a better solution. (which possibly means there IS a better solution) ;-)
    – Tedinoz
    Commented Aug 24, 2020 at 21:45
  • First time seeing it. I am sure I am not using the correct search terms. That one from what i can see is using google scrip to make it work. So i am guessing that I cant do what i want in a formula only then?
    – PY_
    Commented Aug 24, 2020 at 21:58
  • Is the formula as predictable as in your example scenario?
    – Tedinoz
    Commented Aug 24, 2020 at 22:47
  • @Tedinoz The answer you gave worked best. I am using that solution.
    – PY_
    Commented Sep 8, 2020 at 18:38
  • @Tedinoz Can you post it up as an answer so i can mark it?
    – PY_
    Commented Sep 8, 2020 at 18:52

1 Answer 1

Reset to default
1

Try this formula in cell D1:

=(indirect(left(C1,find("+",C1)-1)))+(indirect(right(C1,len(C1)-find("+",C1))))

The logic is explained in the screenshot

Sample

Sample

Sequence Task Value Formula Consolidated formula
#1 find "+" 3 =find("+",C1)
#2 left of "+" A1 =left(C1,C5-1) =left(C1,find("+",C1)-1)
#3 overall length 5 =len(C1)
#4 right of + A2 =right(C1,C7-C5) =right(C1,len(C1)-find("+",C1))
#5 value left 1 =indirect(C6) =indirect(left(C1,find("+",C1)-1))
#6 value right 2 =indirect(C8) =indirect(right(C1,len(C1)-find("+",C1)))
#7 sum 3 =C9+C10 =(indirect(left(C1,find("+",C1)-1)))+(indirect(right(C1,len(C1)-find("+",C1))))
Improve this answer
2
  • While an image might be helpful, text and formulas are better to be shown using text. Besides regular readability, think of people using small screens and screen readers.
    – Rubén - Volunteer Moderator - ♦
    Commented Sep 9, 2023 at 17:51
  • 1
    @Rubén Thank you for the wake-up call; and very easy to do using tablesgenerator.com.
    – Tedinoz
    Commented Sep 10, 2023 at 0:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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