Excel Tricks

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 readibility.

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

Thank you for visiting my site — I hope you found the site and articles helpful. If you did, please consider supporting my efforts by making a purchase (if you have one to make) via one of the links in my articles, one of my recommendations, or in my "Ads by Terry" to purchase the item. You can also shop via these links to major Internet retailers
Amazon.com, Buy.com and NewEgg.com or this Shopping page...

Computer Tips

More Tips

Free Newsletters

Shopping

About This Site

Excel Tricks