How to avoid double , , when combining cell contents?

[MsCynic]

Hello,

I am using this formula to join address details in one cell: =J2&", "&K2&", "&L2&""

When there is no data in column k or l, what is the technique to avoid having double commas in their place? I do want the comma to appear when there is data in those columns though, so removing it from the formula isn't the way, and nor can I see to do a find/replace for instances of , ,

Thanks in advance for tips.

 

[jtakw]

Hi,

Here's one way, formula copied down.

Book1
	J	K	L	M
2	Life	is	good	Life, is, good
3	Life		good	Life, good
4
5	Life			Life
6		is		is
7			good	good
8		is	good	is, good
Sheet136

Cell Formulas
Range	Formula
M2	=SUBSTITUTE(TRIM(J2&" "&K2&" "&L2)," ",", ")

 

[MsCynic]

Thanks for replying so quickly.

Unfortunately I'm now getting a comma wherever a space falls so it comes out like this:

57, Poplar, Drive, Avalon where I only want the comma between Drive and Avalon. Am I asking too much of Excel?

 

[fhqwgads]

how's this?

=J2&IF(NOT(K2=""),", ","")&K2&IF(NOT(L2=""),", ","")&L2
assuming column J will not be empty

 

[jtakw]

Thanks for replying so quickly.

Unfortunately I'm now getting a comma wherever a space falls so it comes out like this:

57, Poplar, Drive, Avalon where I only want the comma between Drive and Avalon. Am I asking too much of Excel?

Then try this:

Book1
	J	K	L	M
2	Life is	is good	is good	Life is, is good, is good
3	Life is		good	Life is, good
4
5	Life	is	is good	Life, is, is good
6	Life	is good	good	Life, is good, good
7	Life is	is	good	Life is, is, good
8	Life is	is	is good	Life is, is, is good
9	Life	is	good	Life, is, good
10	Life	is good	is good	Life, is good, is good
11	Life is	is good	good	Life is, is good, good
Sheet136

Cell Formulas
Range	Formula
M2	=SUBSTITUTE(SUBSTITUTE(" ,"&J2&", "&K2&", "&L2,",,","")," ,","")

 

[Eric W]

If you have Office 365, try:

M2: =TEXTJOIN(", ",TRUE,J2:L2)

 

[MsCynic]

Perfect, thanks so much!

 

[MsCynic]

Thanks, sadly I am stuck with Office 2013.

 

[jtakw]

You're welcome.