I would like to build a list within a single cell from a sting stored another cell. The string has a semi colon delimiter ';' to separate the string SMTP, smtp, X400, FAX. Unfortunately the x400 portion of the string also has semi colons which need to be kept. Its delimiter is a second semi colon ';;'.
I need some help expanding this answer from yesterday, which provides an example of the output i need - https://stackoverflow.com/a/69948563/14638694; as does the picture. enter image description here
| Input Cell | Output Cell |
|---|---|
| SMTP:[email protected];X400:C=GB;A= ;P=TEST;O=Exchange;S=S1;G=user;I=P;;smtp:[email protected];FAX:User P. S1[userS1]@test.local | List From Input Cell |
| X400:C=GB;A= ;P=TEST;O=Exchange;S=41;G=user; | X400:C=GB;A= ;P=TEST;O=Exchange;S=41;G=user\ |
| SMTP:[email protected];X400:C=GB;A= ;P=TEST;O=Exchange;S=2;G=user;;smtp:[email protected];FAX:user 2[user2]@test.local | List From Input Cell |
| X400:C=GB;A= ;P=TEST;O=Exchange;S=17;G=user;;FAX:user 17[user17]@test.local | List From Input Cell |
| SMTP:[email protected];X400:C=GB;A= ;P=TEST;O=Exchange;S=3;G=user;;smtp:[email protected];FAX:user 3[user3]@test.local | List From Input Cell |
| X400:C=GB;A= ;P=TEST;O=Exchange;S=4;G=user;;smtp:[email protected] | List From Input Cell |
| FAX:user 6[user6]@test.local;X400:C=GB;A= ;P=TEST;O=Exchange;S=6;G=user; | List From Input Cell |
| smtp:[email protected] | List From Input Cell |
CodePudding user response：
One solution would be to use nested SUBSTITUTE functions per:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,";SMTP",CHAR(10)&"SMTP"),";smtp",CHAR(10)&"smtp"),";X400",CHAR(10)&"X400"),";FAX",CHAR(10)&"FAX")
Notes:
- The above would be as for an input cell at A1
- It replaces any semicolon delimiter, that appears before any the defined protocol ID's with a linefeed [i.e. Char(10)]
- SUBSTITUTE is case sensitive (hence the need for both SMTP and smtp)
- If there's other cases of that (e.g. X400 and x400), you need to nest in another SUBSTITUTE to cater for those
- If (as the examples suggest) SMTP will appear first or not at all, you could remove substitution of that protocol ID