Unfortunately the Textjoin() function is only available in the Office 365/Excel 2016 version of Excel and so this will not work in previous versions of Excel.

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.

**Find replace formats:**

If you are selective about which formatting to reset, you can use Find Replace (Ctrl+H) to do that. Just follow below instructions.

- Press Ctrl+H to launch Find Replace
- Click on Options button
- Click on Format button against Find area.
- Now select “Choose from cell” option and point to the cell that contains the formatting you want to reset.
- Click on Format button against Replace area.
- Now select “Choose from cell” and point to a cell that contains the formatting you want.
- Pro tip: If you have no cells with default formatting, just click any blank cell, Excel will use default settings.

- Click on Replace All and bingo.

So there you go. Talk to you again from somewhere else.

PS: Here is a pic of all of us enjoying mesmerizing sun set on Lake Taupo. We are off to have fun on Rotorua gondola and luge.

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

**Bonus question: **Let’s say you have a list A1:C100, each row containing outcome of three conditions. How do you find out the total number of rows where ONLY two out of three conditions are met.

Go ahead and post your answers.

Related: XOR formula in Excel | Summing up neither A nor B values.

]]>**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 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 below.

Hmm, let’s investigate why.

By using CODE(LEFT(B4,1)), we can check the code for the very first character in the value ” 4,124,500.00 ”

It is 160.

*Hmm, 160, where have I met you before?*

Poking in the Insert > Symbol tells us that 160 is the * no break space. *What an appropriate name that is.

In fact **no break space is HTML character ***ie *spaces printed on a web page. (Just to be clear not every space on a web page is char 160, but sometimes you have instead of SPACE.)

Simple, we use SUBSTITUTE().

The formula =SUBSTITUTE(B4,CHAR(160),””) removes all the CHAR(160)s from B4.

**But we want the number…**

We can add one extra step to the above SUBSTITUTE() formula to convert text in B4 to number.

The below formula does the trick.

=SUBSTITUTE(SUBSTITUTE(B4,CHAR(160),””),”-“,””)+0

It replaces all CHAR(160)s and -s with blanks and then adds 0 to the resulting text thus forcing Excel to convert text to number. Bingo.

So there you go. For every byte of dirty data that sneaks in to Excel, there are eight ways to fight it out, *at least.*

If all this space business sounds like rocket science, then check out the **example workbook** to understand the formulas better.

If it is a one time job, I usually rely on Find Replace to get rid of any symbols, characters or phrases. But if I have lots of data or changing data, I use SUBSTITUTE, TRIM, CLEAN.

**What about you? **How do you remove untrimmable spaces? Please share your tips in the comments.

One of the new functions that Office 365/Excel 2016 allows is the **Textjoin()** function.

Textjoin takes an array or range of text and joins them together with an optional separator character

eg: =Textjoin(“,”,True,”C”,”h”,”a”,”n”,”d”,”o”,”o”,””,”.”,”o”,”r”,”g”) will return **Chandoo.org**

The use of Textjoin allows for a single celled formulaic solution to the previously impossible Reverse Text problem, as yet unsolved in previous versions of Excel without using VBA.

That is there was no way to reverse a string of text in Excel without using either helper cells or vba

This post looks at the construction of such a function.

The Textjoin() function has 3 components as shown in the Excel Help

Previously in Formula Forensics we have used a simple formula to extract each character of a text string into an array using:

=MID(B2,ROW(INDIRECT(“1:”&LEN($B$2))),1)

Assuming you have the text **Chandoo.org** in cell B2, Excel will return

={“C”;”h”;”a”;”n”;”d”;”o”;”o”;”.”;”o”;”r”;”g”}

so each character takes up a single location within the array

We can reverse the order of the array by changing the location of the character during extraction so that instead of taking characters from left to right we take them right to left, by using a small modification to the above formula:

=MID(B2,LEN(B2)-ROW(INDIRECT(“1:”&LEN($B$2)))+1,1)

Excel returns

={“g”;”r”;”o”;”.”;”o”;”o”;”d”;”n”;”a”;”h”;”C”}

Now we simply send the array into the Textjoin() function

=TEXTJOIN(“”,TRUE,MID(B2,LEN(B2)-ROW(INDIRECT(“1:”&LEN($B$2)))+1,1))

Excel returns

gro.oodnahC

It would be wonderful of the nice folk at Microsoft to add this and other new functions into future Excel 2013 and Excel 2016 updates!

You can download a copy of the above file and follow along, Download Here.

This is the 45th post in the Formula Forensics series.

You can learn more about how to pull Excel Formulas apart in the following posts

I need more ideas for future Formula Forensics posts and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

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

**About this year’s holiday card**

I lost my DSLR during transit to New Zealand. All our picture have been selfies since July this year. We took this silly-face selfie during a bush walk near our house in Wellington. Did I tell you that there are tons of amazing walks and bicycle rides in Wellington? As long as you don’t mind hills, winds, unpredictable weather you will love them.

This year, I have been very lazy with the blog. I don’t see a reason why I should suddenly be productive. So see you in the new year.

**Our forum:**

Our Excel forum will be up and running during the holiday season. That said, I would not expect quick help for any problems as most of our regular members would be away on holiday break. You have higher chance of getting snow in Wellington than finding someone to answer your Excel question during next few days. You see, unlike most of the world, NZ (and few other countries) have their summer in December. So be patient and enjoy the holidays.

**Online store:**

Our online store will be open. Any purchase you make for training programs, templates or eBooks will be delivered as promised. If you join our course on Christmas eve or day or on new year day, please expect to receive your password by the end of next working day. If you have any support query, please expect to hear a resolution by 9th of January.

**Emails:**

If you are sending me an email, please expect a reply only after 9th of Jan. If you have something urgent to say, you can call me (find my number on the site).

On behalf of my family, our staff, volunteers and well wishers, let me wish you a very pleasant holiday season and an awesome start to 2017.

PS: Here is some holiday fun – Christmas card & snow flakes made in Excel.

]]>**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?**

Let’s assume your data starts at B4 and goes for 200 cells (*ie *100 employee and 100 manager names), and the employee name you want to lookup is in cell F4.

We can find the position of the employee using below MATCH formula. (Array formula, so press Ctrl+Shift+Enter after typing it)

`=MATCH(F4,T(OFFSET($B$4,ROW($A$1:$A$100)*2-2,,1,1)),0)`

This will give us the position of employee (ie 3 for Andrea Nichols in the sample data image above).

Once we have the position, we can use below INDEX formula to get the manager’s name.

`=INDEX($B$4:$B$203,`

*position**2)

**How do these formulas work?**

The INDEX formula is fairly obvious. So let’s dig deep in to the MATCH formula.

**First we extract all the odd cells**in the range B4:B203 using`OFFSET($B$4,ROW($A$1:$A$100)*2-2,,1,1))`

part. The`ROW(A1:A100)`

portion generates an array of numbers from 1 to 100 which we then convert to even numbers using simple arithmetic.**We then use**to convert the odd cell values in to an array of text values.`T()`

formula- Finally MATCH() looks for the employee name in F4 against this list to find the matching position.

Please refer to OFFSET tutorial & INDEX formula tutorial to understand the syntax and array usages.

**How to make these formulas generic:**

In our formulas above, we use a fixed range A1:A100 to generate the even numbers. If you have different sized list, you can use below generic version of the ROW formula.

*Assuming your list is named list:*

Replace the ROW() formula above with below version:

`ROW($A$1:OFFSET($A$1,COUNTA(list)/2,,))`

**Click here to download example workbook for odd lookup problem**. Play with the formulas in cells F5 & F6 to learn more.

Bad data is everywhere. Recently, I have come across a data set that is precisely like this. I ended up using the above pattern to find the answer quickly.

* What about you? How would you lookup odd values? *Please share your approach in the comments section.

Don’t let dirty data drag you down. Check out below resources to learn more.

- Introduction to Excel VLOOKUP formula
- Multi-condition lookups
- VLOOKUP second or third (or nth) match
- Lookup the answer in matrix
- Lookup discounted pricing tier
- Range lookup – find out which range contains the answer

]]>

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

OK, I lied, There are actually two ways to do this.

* Drag and drop the row labels to re-arrange them*. Pivot table will remember this order even when you refresh. Of course there is a downside. In case you add some new values in the row label area, they will be at the bottom.

- First define the sort order in a list. Let’s say this list is in $I$3:$I$12
- Now, add an extra column to the original data.
- Using MATCH formula, find the order of each row label (in our case, classification) in the sort order list. Assuming classification is in D3, use =MATCH(D3, $I$3:$I$12, 0)
- Create a pivot table with data set including sort order column.
- Add sort order column along with classification to the pivot table row labels area.
- Add the usual stuff to values area.
- Set up pivot table in tabular layout.
- Remove sub totals
- Finally
**hide**the column containing sort order. - Your new pivot report is ready.

*Good news for people with Excel 2013 or above:*

- Once you have the sort orders table, just link to your original data set thru data model.
- You can then use sort order column in the pivot report directly. No need to write MATCH() formula.
- Refer to our relationship advice for Excel 2013 or above users.

If all of this instruction is sort of tricky for you to follow, **click here to download the example workbook**.

If my data is in Power Pivot, I rely on the *excellent *sort by feature. It is a god send. But when my data is in Excel (or I can’t use Power Pivot), I rely on the approach outlined in this post.

**What about you? **How do you deal with messed up sort order problems in your pivot tables? Please share your tips in the comments.