formula to combine cell

[tonywatsonhelp]

Hi Everyone,
I need a formula to combine the data from B2:B10 but include

"|"​

between each value, but also cope if some cells don't have a value

So
=B2&"|"&B3&"|"&B4&"|"&B5&"|"&B6&"|"&B7&"|"&B8&"|"&B9&"|"&B10 would work but I need it to deal with empty cells?

Tony

 

[Peter_SSs]

Hopefully you are using Excel 2016 via Office 365, then

=TEXTJOIN("|",TRUE,B2:B10)


Otherwise it might be helpful to know something about what could be in those cells (numbers, single word, multiple words, complete mixture?)

 

[wideboydixon]

In Excel 2016, you could use TEXTJOIN but in earlier versions you'll need CONCAT with an array formula:

Book1
	B	C	D
2	a		a|b|d|f|g|i
3	b		a|b|d|f|g|i
4
5	d
6
7	f
8	g
9
10	i
Sheet1

Cell Formulas
Range	Formula
D2	=TEXTJOIN("|",TRUE,B2:B10)
D3	{=MID(CONCAT(IF($B$2:$B$10="","","|"&$B$2:$B$10)),2,1024)}
Press CTRL+SHIFT+ENTER to enter array formulas.

The 1024 is just an arbitrary big number.

WBD

 

[Peter_SSs]

In Excel 2016, you could use TEXTJOIN but in earlier versions you'll need CONCAT with an array formula:

@ WBD
Note that for TEXTJOIN, your 2016 Excel must be through Office 365, not the stand-alone version.
Also, my understanding is that CONCAT was also only introduced for the same version.
References:
https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
https://support.office.com/en-us/article/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

@ Tony
I asked the additional questions in case you didn't have the appropriate Excel version.

If the cells are blank or contain numbers or single words (ie no spaces) then try C3 below

If the cells can contain text with spaces then try F2

Excel Workbook
	B	C	D	E	F
2	a	a|b|c|d|f		a dog	a dog|a cat|99|three mice|f
3		a|b|c|d|f
4	b			a cat
5	c			99
6	d			three mice
7
8
9	f			f
10
Join