CODEWITHSUNDEEP
excelexcel-formula
Carl Soto
Carl Soto

Reputation: 85

Using Excel Formula, add text into a comma separated list

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.

Upvotes: 1

Views: 66

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27428

Try using the following formulas:

enter image description here

=TEXTJOIN(",",,TEXTSPLIT(B2,,",")&":"&A2)

Or, Using SUBSTITUTE() & TEXTBEFORE() function:

enter image description here

=TEXTBEFORE(SUBSTITUTE(B2:B3&",",",",":"&A2:A3&","),",",-1)

Upvotes: 0

Related Questions