Microsoft Office Training London 020 7920 9500
Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. In this article we’ll be looking at the CONCAT function and the TEXTJOIN function. We’ll also examine the TEXT function, which is an oldie but a goodie as it’s crucial when you need to format dates or numbers in your formulas.
This is the old method. We have the entries “Bill” in cell B3 and “Ben” in cell C3. We want to join them together to return the result “Bill and Ben”. We’ll use a formula because the entries might change and we still want the concatenation to work.
You can use either the ampersand (&), which is the concatenation operator, or the CONCATENATE function. Which one you use is your call.
Put the formula together in easy stages. The first job is to join the two values together, like this:
=B3&C3
This returns the result, “BillBen”. Not quite finished as we still need to have the word “and” and its associated spaces between “Bill” and “Ben”. Just edit the formula and include the plain text “ and ” into the concatenation. Like this:
=B3&” and “&C3
Plain text constants must be enclosed in quotation marks. Notice that the leading and trailing spaces required are included in the text.
The first job is to join the two values together, like this:
=CONCATENATE(B3,C3)
And then include the “ and ”, like this:
=CONCATENATE(B3,” and “,C3)
Not too difficult and there’s not much to choose between the two methods. The main disadvantage is that you may not use a range reference so when you need to join a range of multiple cell values together you have to reference each cell in the range individually.
This is the first new text function to examine. It’s just like CONCATENATE, only much better because It supports range references as well as cell references and constants. The CONCAT function automatically ignores empty cells. Many people have been saying “Concat” for years instead of “Concatenate” as it’s much less of a mouthful. Now we’ll never have to say it again.
We need to join the cell values in the range B3:D3. The formula is:
=CONCAT(B3:D3)
Which returns: “BillTedBen”
If cell D3 is empty, the formula =CONCAT(B3:E3) still returns: “BillTedBen”.
But CONCAT does not give you a way of including a delimiter, like a comma, between the values. For that you need the TEXTJOIN function…
TEXTJOIN can do everything CONCAT can do but it’s so much better because it also lets you control delimiters and empty cells. This works wonders when you’re preparing data for CSV format.
TEXTJOIN has three required arguments:
In this example we have a range of cells to join but ones of the values is missing. Our formula is
=TEXTJOIN(“,”,FALSE,B3:E3)
and returns: “Bill,Ted,,Ben”
In the next example, we have four columns: First Name (column B), Middle Name (column C), Last Name (column D) and Title (column E). We need to join them together, separated by spaces and with the title entry coming first.
The formula is:
=TEXTJOIN(” “,TRUE,E3,B3:D3)
A range reference joins the data together in the same order as it is in the range. Because we needed the Title to come first, two text arguments were required. E3 for the Title and B3:D3 for the continuous range containing the three names.
This is a great function that’s been around for years. You really need this one when you have dates or numbers in your concatenation formula which you need to format. Dates in particular. And you’ll need an appreciation of basic Excel date and number format codes. The function works like this:
=FORMAT(value, format_text)
Value is what you are formatting and format_text is the number format code in quotation marks.
Here’s an example. We want to have a heading in our worksheet that reads “Report dated ” – and then include today’s date on the end. My first attempt was this:
=”Report dated “&TODAY()
Nice idea but no coconut! That date appears as a date serial value. You can have a date in a cell and then format the cell as a date. But the date is in the formula, so you must apply the format in the formula using the TEXT function:
=”Report dated “&TEXT(TODAY(),”d mmmm yyyy”)
Follow the link below if you’re not sure about Excel number format codes. The TEXT function supports Custom codes.
To get the best out of Excel formulas you’ll need to have a good, working knowledge of Excel functions. Come along on our dedicated Excel functions course and discover what Excel formulas can do for you.
Call 020 7920 9500 now for details.
The SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, it’s one of the most powerful and flexible filter functions in Excel. And so much better than SUMIF or SUMIFS.
It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. Excel will not help you fill in the DATEDIF function interval values, you need to see the list here.
Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them.
Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.
I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…
You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy. See how to sort the results automatically when the data changes…