concatenating w/ delimiters

**goji** · 09-21-2009, 02:16 PM

All,
I have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name.

In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.

I am using the formula below but it still allows the non-needed commas to appear:
=MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)

This is modded from another site and still cannot get it ... any thoughts would be appreciated.
thanks,
goji

**NBVC** · 09-21-2009, 02:20 PM

Try

=Substitute(Trim(E13&" "&E14&" "&E15&" "&E16&" "&E17&" "&E18&" "&E19)," ",",")

**goji** · 09-21-2009, 02:23 PM

Haha!!
Thats perfect! Thank you very much.
goji

**NBVC** · 09-21-2009, 02:34 PM

You are welcome,

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save