Create One Large Formula by Combining Text from Several Cells

[mamero]

How do you construct and evaluate one large monster formula from the text in several cells?

Example:
Cell A1 reads: "Cell A1="&1+1+1&
Cell B1 reads: ", Cell B1="&2+2+2&
Cell C1 reads: ", Cell C1="&3+3+3

Intended Result
In cell D1 the intended merged formula should read: ="Cell A1="&1+1+1&", Cell B1="&2+2+2&", Cell C1="&3+3+3
and is evaluated to show the result: Cell A1=3, Cell B1=6, Cell C1=9

Note that in my sheet I will need to copy this down so that:
A1+B1+C1 is combined in D1
A2+B2+C2 is combined in D2
A3+B3+C3 is combined in D3
etc...

Thanks for the help!

[daffodil11]

It mostly revolves around the careful use of quotation marks. You can create a rolling range in A1:C1 by adding an = and using row to write formulas in D that correspond correctly.

="Cell A"&ROW(A1)&"="""&1+1+1
=", Cell B"&ROW(A1)&"="&2+2+2
=", Cell C"&ROW(A1)&"="&3+3+3

You could also write the formula with quotes within quotes that create an expression you would ultimately use Paste Values to correctly show.

[daffodil11]

Is the result of your formulas actually evaluating the contents of A1, B1, and C1 or are you just using strings to describe the results?

Knowing the actual application would help me give you an easier to understand solution. Using formulas to write formulas can get pretty dicey, but the results can save heaps of time.

It's all about double, triple, and quadruple quotation marks.


Here's a sample where I combined 53 formulas into one massive formula for a user:
holy concatenation batman.xlsx

And here's an example of using double and quadruple quotes to systemically write formulas that write hyperlinks:
hyperlink mass production.xlsm

[mamero]

Is the result of your formulas actually evaluating the contents of A1, B1, and C1 or are you just using strings to describe the results?

Thanks for the reply. The results in D1 are not evaluating A1, B1, and C1. D1 is simply taking the strings from those cells and combining them. The idea is essentially to "split up" a crazy formula in to more manageable segments. What would be a VERY long formula in a single cell (6-8 lines long) would instead span across several cells so that it can more easily read and altered. D1 mergers those segments into one and evaluates.

[daffodil11]

In order to merger multiple formulas into one, you'll need to copy and paste values the final product into an empty cell.

You would begin by reverse engineering what you need to type into a cell to make it's output "Cell A1="&1+1+1& and not be in text form so that the rows increment as you need, and so that the concatenation symbols are interpreted correctly and not just simply as text ampersands.

=T("Cell A"&ROW(A1)&"=")&1+1+1 is a good start. This will increment the 1 naturally and then convert to text but now we need to reverse engineer this so that this is the output.

This becomes: ="=T(""Cell A""&ROW(A1)&""="")&1+1+1"

Now we have a pattern we can follow.

A1: ="=T(""Cell A""&ROW(A1)&""="")&1+1+1&"
B1: =""", ""&T(""Cell B""&ROW(A1)&""="")&2+2+2&"
C1: =""", ""&T(""Cell C""&ROW(A1)&""="")&3+3+3"

D1: A1&B1&C1
E1: Copy D1 and Paste Values

Now to have it evaluate, hit F2 or double click the cell, and then hit Enter.

Now just drag it down as far as needed and watch as the references change as needed.

Lots and lots of quotations. See the attached if you get lost.

concatenation - formulas building formulas for mamero.xlsx