Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Untrimmable Spaces – Excel Formula

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.

Read This »

January 12, 2017

Formula Forensics 042: Reverse Text – A Formulaic Solution

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

Read This »

January 4, 2017

Merry Christmas & Happy New Year 2017 [Holiday Greeting]

A big, warm & pleasant hello to you.

I wish you a merry Christmas & Happy New Year 2017. May your holidays be filled with joy, togetherness, celebrations and fulfillment. May your new year be filled with hope, energy and awesomeness.

I want to tell you how thankful I am for all your support in this year. Every time you visit our website, read an article, leave a comment, enroll in a course, purchase a product, read one of my books, listen to a podcast episode, watch a video or tell your friends about Chandoo.org, I feel nothing but gratitude, thankfulness and amazement. 2016 has been a remarkable year in our journey and I owe this to you and your support.

Read This »

December 23, 2016

An odd lookup problem [Formulas]

Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.

And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.

Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).

So how would you write the lookup formula?

Read This »

December 6, 2016

Sorting to your Pivot table row labels in custom order [quick tip]

Pivot tables are lovely. But sometimes they are hard to work with. Let’s say you are analyzing some HR data and want to see number of weeks worked in each hour classification.

And you want this.

Except, there is a teeny tiny problem.

The sort order on the classification is all messed up.

Here is a quick fix to get custom sort order on your pivot table row labels.

Read This »

November 29, 2016

We Want Your Ideas

Chandoo.org is looking for your ideas!
What would you like to see discussed in future posts at Chandoo.org ?

Read This »

November 24, 2016

Finding the closest school [formula vs. pivot table approach]

First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.

Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.

There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.

Read This »

November 18, 2016