is there a way to build a list in a single cell from a sting in another cell and without impacting the x400 line.
I've tried the following with no joy
=CONCATENATE(A19&CHAR(10), ";")
=TEXTJOIN( "; "&CHAR(10),TRUE,A19)
=TEXTJOIN({"; "}, TRUE, A11&CHAR(10))
Input String | Desired Output List In Single Cell |
---|---|
smtp:[email protected];smtp:[email protected];smtp:[email protected];SMTP:[email protected];smtp:[email protected];X400:C=GB;A= ;P=Test;O=Exchange;S=User;G=1; | smtp:[email protected]; smtp:[email protected]; smtp:[email protected]; SMTP:[email protected]; smtp:[email protected]; X400:C=GB;A=;P=Test;O=Exchange;S=User;G=1; |
CodePudding user response:
Parse the string and use SUBSTITUTE:
=SUBSTITUTE(LEFT(A1,SEARCH("x400",A1)-1),";",";"&CHAR(10))&MID(A1,SEARCH("x400",A1),LEN(A1))