2

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?

1
  • do you want 2 spaces in 2nd line and 3 spaces in 3rd one, or 1 space in 2nd line and 2 spaces in the 3rd? Jun 3, 2015 at 14:08

1 Answer 1

2

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)

6
  • I have millions of lines :(
    – user454889
    Jun 3, 2015 at 13:54
  • 1
    millions of cells to format, or millions of items in a cell? Jun 3, 2015 at 13:58
  • millions of cells to format
    – user454889
    Jun 4, 2015 at 1:36
  • What is the shape of your data you need to format? One column? Multiple / all columns in a range? One column in several tables? Jun 4, 2015 at 1:55
  • It's one column(A) with close to 1 million rows.
    – user454889
    Jun 4, 2015 at 2:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .