concatenate and delimiters redux

**goji** · 09-21-2009, 03:01 PM

All,
Someone was good enough to solve my initial problem, many thanks! It was being able to concatenate a list of names with commas between names.

The resolution was:

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

This worked great until my boss said "I want first and last names. Now someone whose name makes the list is shown as john, smith.

I am pretty new to Excel - any assistance is appreciated.
Thanks (again)
goji

**NBVC** · 09-21-2009, 03:06 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**zbor** · 09-21-2009, 03:07 PM

Can you upload example how names (first and last) can appear?

**goji** · 09-21-2009, 03:17 PM

the results (in yellow on the sheet) should be first and last name seperated by a comma. Previously using IF's double commas would appear if a person did not show up on the list in e13:e19.

**NBVC** · 09-21-2009, 03:24 PM

tRY:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(E13," ","#")&" "&SUBSTITUTE(E14," ","#")&" "&SUBSTITUTE(E15," ","#")&" "&SUBSTITUTE(E16," ","#")&" "&SUBSTITUTE(E17," ","#")&" "&SUBSTITUTE(E18," ","#")&" "&SUBSTITUTE(E19," ","#"))," ",", "),"#"," ")