Concatenate Several Cells
December 10, 2021 - by Bill Jelen
![Concatenate Several Cells Concatenate Several Cells](/img/excel-tips/2021/12/concatenate-several-cells.jpg)
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.](/img/content/2021/12/PE1676.jpg)
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,""))](/img/content/2021/12/PE19lFig36.jpg)
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