Functions

16 posts

Finding the Largest Negative Number in Excel

If you have a list of descending values and want to find the position of the largest negative number, how would you do it?

First of all, when talking about negative numbers, which number is greater and which is lesser? Is -8 greater than -3, or is it less than -3? The answer is -8 is less than -3. So when finding the largest negative number, the number closer to zero is greater than the number further from zero.

To sum it up, -3 is larger than -8. Keeping this in mind will help when trying to find the largest negative number in a list.

A question to ask is: When would you want to find the position of the largest negative number in a descending list?

When Would You Want to Find the Largest Negative Number?

Imagine that you have a starting balance that is reduced each week, and you want a formula to calculate what week the balance ends up being below zero. This would be the week that has the largest negative number. Now this can change – the starting balance could be different; the amount it is reduced each week could be different.

See the example below:

excel largest negative number

So you want to find the week number for the largest negative number. You can see in the example image that this is Week 17.

But now, how would you use a formula to find it automatically?

What Formula Can Find the Largest Negative Number?

The formula would need to ensure that all variables of are handled. The starting balance could change; the amount the balance is reduced each week could vary; the value that you want returned is to the left of the value you are checking. So a standard VLOOKUP wouldn’t work.

How can this be handled? The formula to calculate would include four separate functions – INDEX, MATCH, LARGE, and COUNTIF. And here it is:

=INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

Now that’s a lot of functions to combine, and the formula can be complex at first glance. However, breaking it makes it simpler and easier to understand what’s happening. Lets’s start breaking down the formula.

COUNTIF(B:B,”>=0″)

This function counts the number of numbers in all of column B that are greater than or equal to zero. This seems counter-intuitive since we want the largest number that is less than zero. But hold on…we’ll get to that later.

Using the data above, this function returns the value of 16, since there are 16 numbers in column B that are greater than or equal to zero.

LARGE(B:B,COUNTIF(B:B”>=0″)

This function returns the nth largest number in column B. The nth number that the LARGE function is being told to return is the result of the above COUNT function, which in this example, is 16.

The 16th largest number in column B is 5. Still not the number we are looking for – that comes next.

+1

This takes the number returned from the above COUNTIF function and adds one to it. Why do this?

Remember that the result of the COUNTIF function was 16, and this number was passed to the LARGE function. Without adding 1 to the 16 that is being passed to the LARGE function, the LARGE function would take the list of of descending numbers in column B and find the 16th largest number that is greater than or equal to zero.

That is not what is wanted. What is actually wanted is the first negative number (the greatest negative number).

That is why 1 is added to the 16. Since, in the list of descending numbers, there are 16 numbers greater than or equal to zero, the next largest number would be the largest negative number.

And that is the number that we want! In this example, the 17th largest number is -28.

Now, to get the matching week name. The example shows that the week name is in cell A18 and is Week17. Since the week name is in the column to the left of the searched value in column B, a standard VLOOKUP function won’t work. A combination of INDEX and MATCH will allow the value to the left to be returned.

MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0)

Here the MATCH function takes the result of the functions above, which is -28. It then looks in column B for that number and returns its position. The match type argument is 0. This tells the function that an exact match is wanted. The -28 in column B is in position 18.

Lastly, the resulting 18 is passed to the INDEX function part of the formula.

INDEX(A:A, MATCH(LARGE(B:B,COUNTIF(B:B,”>=0″)+1),B:B,0))

The index function now looks in the 18th cell of column A and returns that value, which is:

Week17

Here you have seen how to use a formula in Excel to find the largest negative number. While this formula uses four different functions, and does seem complex, it is very flexible.

How to Use the Excel MATCH Function [In Detail]

The MATCH function is part of Excel’s group of lookup functions. This includes INDEX, LOOKUP, VLOOKUP, and HLOOKUP.

Excel’s MATCH function lets you tell it what item to look for in a range, and it then returns the position of that item in the range. Note that it returns, not the item, but the position of the item in the range. This is different from the LOOKUP functions.

Again, it’s pretty simple and basic. However, it’s a powerful function that can be combined with the INDEX function to create very advanced lookup formulas. Here’s how to use it.

 

Start With the Details

Here’s the Excel MATCH function:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value = the value you want to find. It’s required.

lookup_array = the range of cells to search for the value. It’s required.

[match type] = tells the function how it matches the lookup_value in lookup_array. It’s optional. If you use it, you pass the values of either -1, 0, or 1 to the function.

match_type valueDescription
-1Return the smallest value that is greater than or equal to the lookup_value. The data in lookup_array must be in ascending order.
0Return the first value that is equal to the lookup_value.
1Return the largest value that is less than or equal to the lookup_value. The data in lookup_array must be in descending order.

 

Using the Excel MATCH Function

The sample below will be used as the basis for the examples in this post. Using text for the lookup_value as well as using values for the lookup_value will be shown.

excel match function

Using Text for the Lookup_value

With the above example, enter the following formula:

=MATCH(“Green”, A2:A6).

This will return the result of “3”.

The above formula is looking for the position of the text “Green” in the area of cells A2 to A6. Since the text “Green” is the third cell, the result of “3” is returned.

The match is not case-sensitive, and will return the position, even you enter “0” as the match type.

Also, if you use text for the lookup_value, and also use “0” for the match_type argument, wildcard characters of question mark (?) and asterisk (*) can be used as part of the lookup_value. If you actually want to search for a question mark or asterisk, you will need to type a tilde character (~) before it.

For example, using the formula:

=MATCH(“Ye*”, A2:A6,0),

will return “2” because “Yellow” is in position 2 in the lookup_array range.

Changing the formula to:

=MATCH(“Bl*”, A2:A6,0),

will return “4”. Note in the sample, there are two values in the lookup_array range that contain start with “Bl”. These are both “Blue” in position 4 and “Black” in position 5. Since the “0” match_type argument will return the first result that matches, it returns “4”, since “Blue” is the first match.

This may result in unintended results, so you may have to change the lookup_value criteria to be more specific, depending on your requirements.

Using Values for the Lookup_value

Using the same sample data, enter the following formula:

=MATCH(80, C2:C6).

This will return the result of “4”.

The above formula is looking for the position of the value “80” in the area of cells C2 to C6. Since the value of “80” is the fourth cell, the result of “4” is returned.

Now, change the above formula to this:

=MATCH(79, C2:C6).

This will return the result of “3”.

The above formula is looking for the position of the value “79” in the area of cells C2 to C6. Note that there is no value of “79”.

As there is no entry for the match_type argument, the default match_type of “1” is used. The argument of “1” tells the function to find the largest value that is less than or equal to “79”. In the range above, that value of “50” is in the third cell, so the result of “3” is returned.

Now, change the formula to:

=MATCH(79, C2:C6, 0).

The match_type argument of “0” tells the function to find an exact match. Since there is no exact match, the function returns the error result of “#N/A”.

How about if you enter this formula:

=MATCH(79, C2:C6, -1)?

The match_type argument of “-1” tells the function to find the smallest value that is greater than or equal to the lookup_value. Since the lookup_value is “79”, it would be reasonable to expect the function to return “4”, since the smallest value greater than or equal to “79” in the range specified is “80”, which is in position “4”.

However, the function will return the error result of “#N/A”.

This is because the values in the lookup_array range have to be in descending order if you use the match_type argument of “-1”.

Also, if you use the match_type argument of “1” to get the largest value that is less than or equal to the lookup_value, the lookup_array range values need to be sorted in ascending order.

When to use the MATCH Function?

On its own, there aren’t that many uses for the Excel MATCH function. One use of the function on its own is to use the function to have a formulaic way to verify that a value or text exists in a list.

However, when combined with other functions, MATCH becomes more useful. Such as, when combined with INDEX, it provides a powerful, and flexible replacement for VLOOKUP.

 

Using the (Simple) Excel INDEX Function

When I hear the word “Index”, I tend to think of the dictionary definition. Merriam-Webster defines Index as “a list of items (such as topics or names) treated in a printed work that gives for each item the page number where it may be found”.

For example, an index in a printed book would be a list of topics, typically in alphabetical order, and would show what page the topic would be on.

This is a fitting description for the Excel INDEX function, except that the Excel INDEX function does this in reverse. You tell the INDEX function where to look, and it returns the value in that location.

Using the example of the book from the above paragraph, you would pick a page and then list what topics are found on it.

Excel’s INDEX function lets you lookup a value based on the row and column passed to the function.

Start With the Basics

Here’s Excel’s INDEX function:

INDEX(array, row_num, [column_num])

array = a range of cells or an array. It is required.

row_num = the row in the array to return a value from. This is optionally required. See the note below.

Note: If row_num is not included, column_num must be included.

[column_num] = the column in the array to return a value from. This is also optionally required. See the note below.

Note: If col_num is not included, row_num must be included.

Additional Notes

  • If you include both row_num and column_num, the INDEX function returns the value where the two arguments intersect.
  • If you set row_num or column_num to zero, INDEX returns the value in the entire row or column.
  • If row_num or column_num refer to a location outside of array, the #REF! error will be returned.

How to Use Excel INDEX

using the excel index function

Using the above example, entering the formula:

=INDEX(A2:B6, 1, 2)

The A2:B6 tells the function to look in the range of cells from A2 to B6.

The image below highlights the area that it looks in.

excel index range
Highlighted Excel Index Range

The “1” passed to the function tells the function to look in row one of the range, which is equal to cells A2:B2.

The image below highlights the area that it looks in.

excel index row
HIghlighted Excel Index Row

Next, the “2” passed to the function tells the function to look in column two of the range, which is equal to cells B2:B6.

The image below highlights the area that it looks in.

excel index colunn
Highlighted Excel Index Column

Now, if you look at where the two red rectangles intersect, you will notice that there is one cell. This is the cell that is returned by the function.

The result of this function is the value in cell B6, which is “Large”.

excel index intersection
Highlighted Intersection of Excel Index

That’s how you use the function. Now why would you want to use it?

Why You Want to Use The INDEX Function

Now that how it works is explained, why would you want to use it?

Take a scenario where you have a list of items. You can use the INDEX function to look up a specific item in the list.

That’s about it. There’s not much more to say about this function.

But like so many of Excel’s functions, it can be combined with other functions to create powerful tools. Take, for example, combining the INDEX function with the MATCH function. These two, in combination, are an excellent replacement for the VLOOKUP function.

SUMMARY

Excel INDEX is a pretty straightforward function that does one basic task. It looks up a value based on what row and column is passed to it and returns that value.

It’s true power lies in its ability to be combined with other functions to create very powerful Excel tools.

If you have tips or comments on how you use the INDEX function, please let me know.

Finding the Excel Add-ins Folder Location

Excel Add-ins are used to expand on existing features or to provide new features in Excel. They must be installed and/or enabled to use them. The add-ins are saved in a specific folder.

Use the steps below to find the Excel Add-ins folder location:

1 – Click the Develop tab on the ribbon. If you don’t have the Developer tab, follow these steps to show the Developer tab.

2 – Click the Excel Add-ins button.

excel add-ins

3 – Click the Browse button on the dialog box that opens.

excel add-ins

4 –  Here is the location of the Excel add-ins on the computer. You can now note it, or right click the address bar and copy the address for later use.

excel add-ins

 

 

Live Transpose in Excel With an Array Formula

Excel’s transpose feature is great for changing data in a vertical format to a horizontal format, and from a horizontal format to a vertical format. Excel’s transpose, though, creates a static copy of the original data. If the original data changes, the transposed copy does not change.

This may be your desired result, but if you want the transposed copy of the data to update at the same time as the original data changes, there is a way create a transposed copy that changes along with the original data. This will create a “live” transposed copy.

The way to do this is by using an array formula. Check here if you want more information on array formulas. As a quick note, you do need to use CTRL + SHIFT + ENTER when entering an array formula for it to work.

Live Transpose Example

To illustrate how a live transpose in Excel works, take a look at the sample image below. It shows the original table of data that will be the source for the live transpose copy.

excel live transpose source data

Once you decide where you want the live transposed copy of the original data to be placed, you just need to highlight a range of cells that is equal to the transposed version of the original data. Note that it is the opposite size of the original data, since it is a transposed copy.

Therefore, In this example, since the original data is 14 columns wide by 9 rows high,when the data is transposed, its shape will change to be 9 columns wide by 14 columns high.

So you need to select a range that is 9 columns wide by 14 columns high.

highlight a range for live tranpose

Once you have highlighted the range where you want the live transposed copy of the data to be located, start to type the TRANSPOSE formula. You can either click in the formula bar first, or just start typing the TRANSPOSE formula. In the example here, it would be:

=TRANSPOSE(A1:N9)

And when you type CTRL + SHIFT + ENTER after typing it, it turns into this (NOTE the curly braces that were automatically added):

{=TRANSPOSE(A1:N9)}

And this is the resulting view:

a live transpose in Excel

Anywhere you click in the range, the formula in the formula bar does not change. It stays the same.

Now, if you change any values in the original data, the transposed version will automatically update to the new value you have entered.

Summary

Hopefully, this post has shown you how to use an array formula version of the TRANSPOSE function in Excel to create a “live” transposed version of the original data.

Let me know what you think, or if you have any tips or comments about this method.

Excel SUMPRODUCT Function

The Excel SUMPRODUCT function is a little known function. However it has a very fitting name because it multiplies and then returns the sum of the groups of multiplications, or creates a SUM of the PRODUCT.

Starting With The SUMPRODUCT Function

SUMPRODUCT(array1, [array2], [array3], )

  • array1 = the first array that you want to multiply and add together. This is required
  • array2, array3, … = the next 2 to 255 arrays of numbers to multiply and add together. This is optional

A few notes about the function:

  1. If you have more than one array of numbers and they are not the same size, the function will return the #VALUE! error
  2. If any arrays do not have numbers, they will be treated as a zero by the function
  3. If a single array is passed to the function, it will only sum. It will not multiply.

How to Use It

In the image below, sample data and four examples of the SUMPRODUCT function are shown.

Notice that both cells A3 and A4 have a single array in their functions, while cells A5 and A6 have two arrays in theirs.

sumproduct

What do you think the result will be of each of the different functions?

The first function in cell A3 is using only two cells in the SUMPRODUCT function, specifically A1 and B1. So will it multiply the 1 (cell A1) * 2 (cell B1) and return 2, or will it add the two value and return 3?

What about the function in cell A4 that is using four cells in its SUMPRODUCT function? Will multiply the four cells or add them?

And then, what about the functions in cells A5 and A6? What will they return?

Here you can see what the results are.

sumproduct

Is it what you expected? If not, hopefully the explanation below will help you work through the formulas to see how the results were achieved.

Notice that when the SUMPRODUCT function is only passed one array of numbers, it does not multiply the numbers, but it adds them.

The function in cell A3 is passed the values in cell A1 of 1 and cell B1 of 2, so it is adding the 1 and 2 to equal 3.

The function in cell A4 is passed the values in cells A1 through A4 of 1,2,3 and 4. It is adding the four amounts to equal 10.

However, the functions in both cells A5 and A6 are both being passed two arrays, so the function is multiplying and then summing the product of the multiplication.

But how is the function returning the answers of 11 in cell A5 and 94 in cell A6?

Remember cell A5 has the formula of

=SUMPRODUCT(A1:B1,C1:D1).

You may think that it would multiply A1 * B1, multiply C1 * D1, and then add those two results together. Actually what the function is doing is multiplying A1 * C1, and B1 * D1, and then adding those two results together. This results in (1*3) + (2*4), which equals 11.

And what about the function in cell A6? Notice how it also refers to two arrays, the same as the formula in cell A5. The difference is that the arrays in cell A6 are not just referring to one row of data, but to two rows instead.

So the function in cell A6 returns 96, which is the result of (1*3) + (2*4) + (5*7) + (6*8).

To summarize how the function works, if there is more than one array being passed to it is this:

Take the first value in each array and multiply them. Then do the same for each next value in the arrays. Then add them together.

Now you fully understand how to use the SUMPRODUCT function.

But the really big question is, Where is this function useful?

What to Do With SUMPRODUCT

One SUMPRODUCT use can be for creating summaries of data, totalling based on criteria. For example, if you have data showing sales for different locations and colors and want a total of each location, or a total of each color, or a total of each unique combination of location and color, you can use SUMPRODUCT.

See the image below.

sumproduct

In the above image, notice that there are several entries for each location, each color, and also for the unique combination of location and color. For example, “North” is found on six rows in column A, and “Red” is found on seven rows in column B. The combination of “North” and “Red” on the same row is found on four rows.

Now, in typical Excel fashion, there are several ways that you could create those totals. You could make the data into a table, add totals to it, and then filter based on what you needed. You could use SUMIF or SUMIFS. And there are probably other ways that you do it. Let me know in the comments if you know of other ways to create summaries.

What will be considered here, is how to use SUMPRODUCT to create summaries of the data. Here is a summary of the above data based on location.

sumproduct

Here’s a summary of the data based on color.

sumproduct

And finally, here is a summary of the data based on a combination of location and color.

sumproduct

Each of these three summaries of the data are using the SUMPRODUCT function to filter the data and add the amounts based on the criteria.

Here are the same summaries, but showing the actual SUMPRODUCT function.

sumproduct

sumproduct

sumproduct

Did you notice how the SUMPRODUCT is a bit different. For example, one of the SUMPRODUCT function formulas is:

=SUMPRODUCT((E2:E26)*(A2:A26=H2)).

The cell H2 contains the word “North”. An alternate to the above formula could be

=SUMPRODUCT((E2:E26)*(A2:A26=”North”)).

So you can type in criteria, or use cell references as the criteria.

The first array is the column that you want to sum, the second array is the column that you want to filter on. This formula will filter on one criteria. To filter on more than one criteria, you just need to add more arrays and the filter for each one.

For example, the formula

=SUMPRODUCT((E2:E26)*(A2:A26=”North”)*(B2:B26=”Red”))

will add the amounts in column E that have “North” in column A and “Red” in column B.

Summary

SUMPRODUCT is not a commonly used function, but it can be used to multiple and add arrays of numbers. A probably surprising use of SUMPRODUCT is to create filtered subtotals of numbers.

If you have of other ways to use SUMPRODUCT let me know in the comments below.

 

 

 

How to Use the Excel CLEAN Function

The Excel CLEAN function removes non-printable characters from a string of text. This function is useful to clean imported data from a database or system that may contain non-printable characters. It can be used as a formula on a worksheet in an Excel workbook.

The Excel CLEAN Function

CLEAN(text)

  • text = string of characters that need non-printable characters removed

Please note, the CLEAN function does not remove all non-printable characters from a string. There are some Unicode characters (127, 129, 141, 143, 144 and 157) that this function will not clean.

So, depending on what non-printable characters are in your data, you may need to use further methods to clean the data you’ve imported. You’ll need to use the SUBSTITUTE function combined with the CLEAN function to clean them.

Example

excel clean

The cell A1 has the formula “=CHAR(1)&”string”&CHAR(4)”. The CHAR(1) and CHAR(4) are non-printable characters that will be cleaned from the string. Note how result of the formula of “=CLEAN(A1)” in cell C1 shows just the printable text of “string” in the cell.

How to Use the Excel COLUMN Function

The Excel COLUMN function returns the referenced column as a number. Here’s a quick explanation of how to use the function.

COLUMN Function

COLUMN([reference])

  • reference = The cell or range of cells that you want to have the column numbered returned from. This is optional.

Here are a few notes about the function:

  1. The reference can’t refer to multiple ranges
  2. If no range is entered in the function, it assumes that you want to know the column of the cell that has the function
  3. If you use an array in the reference (by selecting the cell with the COLUMN function, pressing F2, and then CTRL + SHIFT + ENTER), the function will return the column number as a horizontal array
  4. If the reference is a range of more than one cell, and a horizontal array formula is not used, the function will return the left-most column number

Examples

Here you will see some examples of the COLUMN function including using it as a horizontal array formula. The image below shows the results of using the function.

column function
Result View of COLUMN Function

Now, here is the same spreadsheet showing the formulas:

column function
Formula View of COLUMN Function

Notice that the three formulas are all different, yet they return the same result.

=COLUMN() returns 1, since the function is located in column 1

=COLUMN(A1) also returns 1, since it is referring to cell A1, which is in column 1

=COLUMN(A1:B1) also returns 1 even though the range is over two columns. This is because it always returns the left-most column.

Now, you will see the difference by using horizontal array formulas.

In the image below, the results of the COLUMN function is shown. There are three more formulas added to help show the comparison.

column function
Result View of COLUMN Functions Including Horizontal Array Formulas

Here is the formula view of the above spreadsheet. Note that array formulas will not show the curly brace brackets in the cells, but only in the formula bar. As a result, the formulas in cells A4, B4, A5 and B5 all look identical. However, the formulas in A5 and B5 are both entered as horizontal array formulas.

column function
Formula View of COLUMN Functions Including Horizontal Array Formulas

The image below shows how the formula in cell B5 appears in the formula bar with the curly braces to indicate it is a horizontal array formula.

column function
Horizontal Array Formula

All You Need to Know About COMBIN

Your first question may be – What is COMBIN? And your second question is probably – Why would I need to know about it?

COMBIN is a math function in Excel that calculates all the possible unique combinations of a set of numbers.

For example, say you have a group of four people, and you want to know how many different groups of two that you can make out of that four. This is where you would use the COMBIN function.

COMBIN Function

Excel’s COMBIN function looks like this:

COMBIN(number, number_chosen)

  • number = total number
  • number_chosen = number in each combination

So, for the example mentioned above of four people total, and the unique number of groups of two, the formula would be:

=COMBIN(4, 2)

And the answer is: 6

Looking at this example further, the four people are: Bob, Mary, Ralph and Daniel. The possible combinations are:

Bob & MaryBob & RalphBob & DanielMary & RalphMary & DanielRalph & Daniel

This example was a simple one. Now imagine that the amount of people has increased to 10, 50, 100, or 200. How many possible combinations do you think can you get from those amounts of people?

The images below shows the formula and the result:

COMBIN
COMBIN Function Formula
COMBIN
COMBIN FUNCTION Result

As the number of choices increase, the possible results of unique combinations greatly increases.

Further Considerations

Only numbers can be used. If a non-numeric argument is used, the function will result in the #VALUE! error.

A #NUM! error will result if you use zero or less as one of the arguments, or if number is less than number_chosen.

Conclusion

The COMBIN function is probably not a function that you will commonly use. However, when you need to know the possible combinations of numbers, it will be very useful for that purpose.

For further information of the COMBIN function, see the Microsoft article.

If you have any questions or comments on this function, let me know.

 

 

 

 

Here’s a Quick Way to Concatenate Text Strings

When you need to concatenate text strings together in Excel, there are a few ways of doing it. There’s the old standby CONCATENATE function, there’s the ampersand (&) in the formula method, and with Excel 2016 there’s now the CONCAT function and TEXTJOIN function.

Here’s a summary of the ways to concatenate text strings. I’ll use the same text strings for each method to show the difference.

CONCATENATE Function

Excel’s standard CONCATENATE function looks like this:

CONCATENATE(text1, [text2], )

  • text1 = the first string to join. It’s required
  • [text2] = the second string to join to the first. It’s optional and there’s a maximum of 255 strings, or 8,192 characters

Here’s how the formula would look:

=CONCATENATE(“Hello world.”, ” “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

The Ampersand Method

The ampersand (&) method is what I would use instead of the CONCATENATE function. I found it quicker to type either the couple of strings or cell references with the ampersand (&) between rather than typing out CONCATENATE.

It’s probably just a personal preference though. With the new functions added to Excel 2016, I just may change my method.

Here’s how the formula would look with the ampersand (&) method:

=”Hello world.” & ” ” & “Today is” & ” ” & “Monday.” & ” ” & “There” & ” ” & “are” & ” ” & “no” & ” ” & “appointments” & ” “& “in your calendar.”

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

So, the same result as the CONCATENATE function, but looking at the two, I actually find this method of joining text harder to read the formula.

CONCAT Function

CONCAT is one of the two new Excel 2016 functions that you’ll see here. It has replaced CONCATENATE, though CONCATENATE is still available for backwards compatibility.

The CONCAT function looks like this:

CONCAT(text1, [text2], )

  • text1 = the first string to join. It’s required
  • [text2] = the second string to join to the first. It’s optional and there’s a maximum of 253 strings, or 32,767 characters

Here’s how the formula would look:

=CONCAT(“Hello world. “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)

Here’s the result of the formula:

Hello world. Today is Monday. There are no appointments in your calendar.”

TEXTJOIN Function

TEXTJOIN is the other new text function added to Excel 2016. Like CONCATENATE and the new CONCAT, it combines text. However, it also includes the ability to add a text delimiter and some logic to the joining of text.

The TEXTJOIN function looks like this:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], )

  • delimiter = a text string in double quotes
  • ignore_empty = TRUE or FALSE. If the function should ignore empty values
  • text1 = the first string to join. It’s required
  • [text2] = the second string to join to the first. It’s optional and there’s a maximum of 252 strings, or 32,767 characters

=TEXTJOIN(“”,TRUE,”Hello world.”, ” “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

Notice, though, that the same empty characters are between the text. That’s not using the full power of the TEXTJOIN function.

If you remove the space strings between the words, and instead use the TEXTJOIN function’s ability to insert the space strings, the formula would look like this:

=TEXTJOIN(” “,TRUE,”Hello world.”, “Today is”, “Monday.”, “There”, “are”, “no”, “appointments”, “in your calendar.”)

Here’s the result of the formula:

“Hello world. Today is Monday. There are no appointments in your calendar.”

The result is the same, but the formula is now shorter.

This new TEXTJOIN function now allows you flexibility in how to use and manipulate text strings. You can specify what you want between each string, and the formula is shorter, and easier to read.

Conclusion

The new functions of CONCAT and TEXTJOIN that have been added to Excel 2016 have greatly increased the amount of characters that can be concatenated.

As well, the TEXTJOIN function has increased the flexibility available to concatenate text strings in Excel. This should give you more freedom and power to quickly concatenate text strings.

Let me know what you think about the new text functions in Excel 2016.