Show HN: Simple method to create complex Excel formulas

Let's say I have the following cells

| A | B | C |

1|mary| |Jane|

In D1 I want to concatenate the cell values if the cell contains text. First I make a formula to check if the cell contains text somewhere in a cell on the sheet. Let us go with A6.

A6: =ISTEXT(A1)

result=TRUE ; Hooray!

Then, if A6 is true, I want to display the text from A1 because I cannot concatenate "true" as I will be doing later on:

A7: =IF(A6=true,A1,"")

result=mary ; Yippy!

I do the same thing for each cell:

A8: =ISTEXT(B1)

result=FALSE ; Sweet!

A9: =IF(A8=true,B1,"")

result=blank ; Thank goodness!

A10: =ISTEXT(C1)

result=TRUE ; Sweet!

A11: =IF(A10=true,C1,"")

result=jane ; Thank goodness!

I know I am going to ultimately combine them with concatenate like so:

A12: =CONCATENATE(A7," ",A9," ",A11)

result=mary jane

Right now it is a mess, but it is easy to follow and create each formula. Now I just copy the formula from the correct cell into the final concatenation (A12)

To start, I will replace "A7" in the A12 formula with the formula from A7 minus the "=" sign:

A12: =CONCATENATE(IF(A6=true,A1,"")," ",A9," ",A11)

result=No change ; Perfect!

I continue that process with A9 and A11 in cell A12 formula to get this:

A12: =CONCATENATE(IF(A6=1,A1,"")," ",IF(A8=1,B1,"")," ",IF(A10=1,C1,""))

result=No change ; 100% success so far!

Now I keep copying the referred cells with formulas(A6, A8, & A10) until I have only the cells with data left(A1, B1, & C1) in the A12 formula:

A12: =CONCATENATE(IF(ISTEXT(A1)=1,A1,"")," ",IF(ISTEXT(B1)=1,B1,"")," ",IF(ISTEXT(C1)=1,C1,""))

result=No change ; Phew...

Plug that formula from A12 into D1 and it is finished. Using this method, I find it very easy to work out more complex formulas. I wish I had figured this out on day 1.