I have a formula that builds a connection string. I would like to add text from a column to a comma separated list in another column to the connection string column.
| PORT | DOMAINS | CONNECTION STRING |
|---|---|---|
| 80 | a.domain.com,b.domain.com,c.domain | a.domain.com:80,b.domain.com:80,c.domain:80 |
| 88 | a.domain.com,b.domain.com,c.domain | a.domain.com:88,b.domain.com:88,c.domain:88 |
I’ve searched google for excel formula to add text into a comma separated list but unable to find a formula that satisfies my criteria.
>Solution :
Try using the following formulas:
=TEXTJOIN(",",,TEXTSPLIT(B2,,",")&":"&A2)
Or, Using SUBSTITUTE() & TEXTBEFORE() function:
=TEXTBEFORE(SUBSTITUTE(B2:B3&",",",",":"&A2:A3&","),",",-1)