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

user_11810306 0
Viewed 120

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

excel
1 Answers

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

user_69082820
Related