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.