Archive for January, 2017

We are on a tiki tour around NZ. So far we have been to Taupo & Rotorua. And we are doing what you do when you are on a holiday – being lazy, going on walks, swimming in lakes, eating copious amounts of food and getting lost. Of course, all this means, I have very little time to access to internet & my blog. So the updates will be slow for next two weeks. Here is a quick tip (well, two of them) to keep you busy and awesome.

How to remove ugly formatting from your workbooks?

Do you have a colleague or boss (shudder) that loves to apply their special touches to every workbook their mouse lands on? Do you constantly wince and whine when you have to work on that spreadsheet.

Here are two handy ways to restore your data to its original glory.

Clear formats:

Simple, select the data you want formatting gone from, go to Home > Clear > Formats.

And Excel will weave an expelliformat spell at your data and make it clean.

Here is a quick demo.

Time for some logic check.

Suppose, you have three logic values in A1:C1 (TRUE or FALSE values in each cell)

You need to find out if ONLY two of these values are TRUE.

How would you write the formula?

Got an answer? Awesome. Just post your formula in the comments. Let’s see how much variety we can get from all of our readers.

Let’s talk about the untrimmable spaces.

We all know that TRIM() removes extra spaces from the beginning, ending and middle of a text.

So for example, if A1 has ” something and    one   more    ”

TRIM(A1)

will give “something and one more”

We can use CLEAN() function to remove non-printable characters (like the ASCII codes 0 to 31).  Of course, SPACE is technically a printable character, so CLEAN() won’t remove spaces.

The untrimmable spaces…?

The other day Sreekanth emailed me a sample of data and asked, “how do I remove the spaces in this list and convert them to numbers?”

Naturally I tried to TRIM().

But the data won’t budge. See above.

Hmm, let’s investigate why.

Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.