Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #2  
Old 12-21-2015, 03:32 AM
Debaser's Avatar
Debaser Debaser is offline CONCATENATE formula, Comma seperate Windows 7 64bit CONCATENATE formula, Comma seperate Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

Change the formulas in columns DM to DP to this:
DM5:
=SUBSTITUTE(TRIM(CONCATENATE(BS5&" "&BW5&" "&CA5&" "&CE5&" "&CI5&" "&CM5&" "&CQ5&" "&CU5))," ",",")
DN5:
=SUBSTITUTE(TRIM(CONCATENATE(BT5&" "&BX5&" "&CB5&" "&CF5&" "&CJ5&" "&CN5&" "&CR5&" "&CV5))," ",",")
DO5:
=SUBSTITUTE(TRIM(CONCATENATE(BU5&" "&BY5&" "&CC5&" "&CG5&" "&CK5&" "&CO5&" "&CS5&" "&CW5))," ",",")
DP5:
=SUBSTITUTE(TRIM(CONCATENATE(BV5&" "&BZ5&" "&CD5&" "&CH5&" "&CL5&" "&CP5&" "&CT5&" "&CX5))," ",",")

and similarly with the formulas in columns BN:BQ, then your formulas in DS and DT will work as they are.

TRIM removes any leading and trailing spaces, and replaces any consecutive spaces within the text with a single space. SUBSTITUTE then replaces those single spaces with commas.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
CONCATENATE formula, Comma seperate concatenate the currency too isasa74 Excel Programming 24 12-20-2015 06:01 AM
CONCATENATE formula, Comma seperate Add a comma mbesspiata Excel 7 06-09-2014 03:36 AM
Adding comma to a custom type. mcook Excel 1 08-13-2011 03:08 AM
adding a comma in end of each line in word juanb007 Word 0 07-23-2010 01:28 PM
CONCATENATE formula, Comma seperate thousands comma separator taylormayd Excel 3 02-20-2009 03:46 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:36 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft