Add comma at the end of cell in excel-CodePudding

I am having a excel sheet with data like below(image attached) where I am adding comma at the end of cell using concatenate formula. Formula works fine for cell with single values like Train--> Train, but when multiple entries are present in cell, concatenate formula is not working like car road bus truck --> carroadbustruck, How can I get cell in this format car,road,bus,truck,

CodePudding user response：

While SUBSTITUTE() will work as @Tim suggested. You can also use TEXTSPLIT() with CONCAT().

=IFERROR(CONCAT(TEXTSPLIT(B1,CHAR(10))&","),"")

CodePudding user response：

We can use the SUBSTITUTE() function here along with concatenation:

=IF(A1<>"",SUBSTITUTE(A1,CHAR(10),",")&",","")

This assumes that your intended logic is to replace newlines with comma, and also tag on an extra trailing comma at the end.