How to avoid double , , when combining cell contents?

MsCynic

Board Regular
Joined
May 21, 2006
Messages
122
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi,

Here's one way, formula copied down.


Book1
JKLM
2LifeisgoodLife, is, good
3LifegoodLife, good
4
5LifeLife
6isis
7goodgood
8isgoodis, good
Sheet136
Cell Formulas
RangeFormula
M2=SUBSTITUTE(TRIM(J2&" "&K2&" "&L2)," ",", ")
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
how's this?

=J2&IF(NOT(K2=""),", ","")&K2&IF(NOT(L2=""),", ","")&L2
assuming column J will not be empty
 
Upvote 0
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
JKLM
2Life isis goodis goodLife is, is good, is good
3Life isgoodLife is, good
4
5Lifeisis goodLife, is, is good
6Lifeis goodgoodLife, is good, good
7Life isisgoodLife is, is, good
8Life isisis goodLife is, is, is good
9LifeisgoodLife, is, good
10Lifeis goodis goodLife, is good, is good
11Life isis goodgoodLife is, is good, good
Sheet136
Cell Formulas
RangeFormula
M2=SUBSTITUTE(SUBSTITUTE(" ,"&J2&", "&K2&", "&L2,",,","")," ,","")
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top