Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

Adding “+” between words in Strings

Code numbers such as invoice or product codes are common in Excel. When you work with codes like these you often need to join multiple pieces of text (or ‘strings’) together, sometimes with a symbol between them such as a hyphen or +.

This example was sent to us from one of our customers:

She wanted to concatenate the three codes together with + symbols between them, resulting in codes like R+TG+340.

Using the concatenation operator (&)

The ampersand (&) is Excel’s ‘concatenation’ operator. You can use it in Excel formulas to join pieces of text together.

In the example shown above, the formula =A2&B2 would result in RTG.

We want + symbols to be shown between each of the codes, so the correct formula is:

=A2&”+”&B2&”+”&C2

Concatenation is covered in much greater depth in our Expert Skills Books and E-books.

Using the new TEXTJOIN function

TEXTJOIN is a new function that has been added to Excel 2016 via Office 365. If you don’t have an Office 365 subscription you will not be able to use the TEXTJOIN function.

TEXTJOIN does exactly the same thing as the concatenation formula, but makes the formula much shorter and easier to write:

=TEXTJOIN(“+”,TRUE,A2:C2)

Remember that TEXTJOIN will not be usable by anybody who bought the retail ‘perpetual’ version of Excel 2016. Only users of ‘Excel 365’ can use TEXTJOIN at present, but it will be included in Excel 2019 when it is released.

If you’re unfamiliar with Excel formulas and functions, you could greatly benefit from our free Basic Skills E-book, which will teach you the basics of Excel formulas.

Sample workbook

You can download a copy of the sample workbook to see the formulas in action.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Recent Articles

Excel protected view warning

Excel protected view: Be careful – files from the Internet can contain viruses. Unless you need to edit it’s safer to stay in Protected View. This article explains what this error message means.

VLOOKUP inexact match illustration (chicken and egg).

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

Clients

When you use The Smart Method you’re in good company Here are some of the companies that The Smart Method have helped empower with excellent

Microsoft Office update channels

Microsoft Office update channels are explained in this article. Beta, Current, Monthly Enterprise and Semi-annual enterprise are all covered.

Leave a Reply

Your email address will not be published.

fifteen − seven =