Concatenate Several Cells

December 10, 2021 - by Bill Jelen

Problem: I have to concatenate several cells.

Strategy: The February 2016 release of Office 365 now offers TEXTJOIN and CONCAT functions. Both functions are able to accept a range of cells. CONCAT smashes all of the text together. TEXTJOIN allows you to specify text to go between each value and if Excel should ignore empty cells.

There are 7 state names in A1:A7. The new =CONCAT(A1:A7) returns a result without spaces: ArizonaCaliforniaFloridaHawaii. By using =TEXTJOIN(", ",True,A1:A7), you get comma space between each state. — Figure 216. TEXTJOIN is a great addition to Office 365.

Additional Details: The third argument of TEXTJOIN can accept an array or a 3-D reference. In the figure below, an IF function returns only the people with RSVP=Yes.

Names in A2:A10 with RSVP of Yes or No in column B. The formula returns only the people who said Yes: =TEXTJOIN(", ", True, IF(B2:B10="Yes",A2:A10,"")) — Figure 217. Use IF inside of TEXTJOIN to choose only people who meet a criterion.

Gotcha: You have to be subscribing to Office 365 to access TEXTJOIN. If you have an earlier version of Excel, download the free MoreFunc add-in and use MCONCAT.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jonny Caspari on Unsplash