Excel Formula to find a text and enter a carriage return in the same cell-CodePudding

I have a string in a cell, let's say A1 that looks like this:

CloudChecker Gold;#System Administration;#NOC Monitoring;#ACDaaS

I'm trying to create a formula that finds each ;# and then does a carriage return (Alt Enter).

I tried doing this, but it blanks out the whole cell. And the FIND formula just returns the index number of ; (which is 16 in the string above).

IF(FIND(";#", A1), CHAR(10), A1)

Any other ideas? The end goal should look like this in one cell:

CloudChecker Gold
System Administration
NOC Monitoring
ACDaaS

CodePudding user response：

You can use Substitute function along with CHAR(10) like this:

=SUBSTITUTE(A1,";#",CHAR(10))

Note: You need to have "Wrap Text" enabled on the cell to actually see it being split in multiple lines. If wrap text is not enabled, you will see it in one line