In my weekly email newsletter this week, I wrote about some Excel tricks that I was using in a spreadsheet.
One formula that I showed was a long Excel formula that I had formatted for more readability.
The full cell formula (which did a lot of concatenation) that I used was
=IF(ISBLANK(C15),
D15&E15&$F$5&F15&$H$5&RIGHT(H15,LEN(H15)-1)&$I$5&I15&$J$5&J15&$K$5&K15&$L$5,
D15&E15&$F$4&F15&$H$4&RIGHT(H15,LEN(H15)-1)&$I$4&I15&$J$4&J15&$K$4&K15&$L$4)
Tech Tip
What’s the Concatenate function? It’s similar to addition of numbers, but instead, it allows you to combine multiple text values into a single cell.
Let’s look at it in an example:
Cell A1 has the text string "This is" as its value
Cell B1 has the value "a test."
You can combine them in Cell C1 (or any other cell) using the following formula:
=Concatenate(A1;” “;B1)
to get the result "This is a test."
You can also use the shorter and easier concatenation operator like this to get the same result:
=A1&” “&B1
If you use Excel much, you know that as you type in the formula, Excel puts into one long, hard-to-read line. But, guess what? Excel stores and displays my formula with the carriage returns, which makes it immensely easier to understand.
I’ve even got spreadsheets where I use the maximum number of nested IF statements (which is 7) — and tricking Excel into displaying the formula this way helps ME write the formulas without getting confused.
But, how can you make Excel a cell formula formatted like that?
There are two tools that you need to do the editing: Excel (I used Excel 2003) and a text-processor (not a Word Processor, you don’t want the formatting it would do).
Notepad will work, but there’s a free open-source program that will work much better, called Notepad++.
Continued in HowTo: Using Notepad++ With Excel
and