In C4:C13 I have a list of from 0 to 10 names. Each cell will either have a name or be blank. I want to concatenate the results, adding a comma and space to each entry. My formula is:
=C4&", "&C5&", "&C6&", "&C7&", "&C8&", "&C9&", "&C10&", "&C11&", "&C12&", "&C13
My issues is that if five of the cells are blank I get 5 commas at the end of my list. How do I stop the commas from being generated when they're unneeded?
=substitute(c4&", "&c5&", "&c6&", "&c7&", "&c8&", "&c9&", "&c10&", "&c11&", "&c12&", "&c13,", ","",counta(c4:c13))
it is wrong, working formula is lower
Last edited by tim201110; 02-22-2017 at 12:21 PM.
This proposed solution employs a helper column, which could be hidden for aesthetic purposes and/or moved if needed. The formula that populates the helper column (pasted into D4 and copied down to D13) is:The end result is then placed in another cell by =D13Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Tim,
I think I see the idea, but the application failed. Before, my formula displayed 7 extra commas (using only 3 names), after applying your solution the formula displayed 6 commas.
JeteMC,
That is a workable solution, but I'd rather do it without a helper column if there is a way.
Last edited by jomili; 02-22-2017 at 12:08 PM.
=substitute(c4&", "&c5&", "&c6&", "&c7&", "&c8&", "&c9&", "&c10&", "&c11&", "&c12&", "&c13,REPT(", ",10-counta(c4:c13)),"")
sorry, misunderstanding of the function
And we have a winner! That REPT was the trick! Thanks Tim.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks