Need to add newline(s) and space(s) inside a cell in Excel

Question

I have data in an Excel cell which is delimited by ,. For example: abc,bde,fgh. I used the formula =SUBSTITUTE(A2, ",", CHAR(10) & CHAR(13)) to change the commas to line breaks. Therefore, the data became like below:

abc    
bde  
fgh 

However, my requirement is to add spaces inside the cell, so that output would look like:

abc  
  bde     
   fgh 

So, the second line has 2 spaces in the front, the 3rd line has 3 spaces in the front, and the 4th line has 4 spaces in the front, sequentially. How can I achieve this?

Answer 1

If you have only a few lines to reach, then you can achieve it with nested SUBSTITUTE:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",", CHAR(10)&REPT(" ",2),1),",",CHAR(10)&REPT(" ",3),1),",",CHAR(10)&REPT(" ",4),1)