View original (French)

Remove empty lines within a cell

Ubi75 Posted messages 2 Registration date   Status Member Last intervention   -  
Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   -
Bonjour,
Do you know a formula to remove empty lines inside a cell in Excel?


(!) the number of non-empty lines is different in each cell

Thank you!

1 answer

  1.
    Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
     
    Hello,

    With: 
    =SUBSTITUTE(A1,CHAR(10)&CHAR(10),CHAR(10))


    --
    Best regards
    Patrice
    1
    1. Ubi75 Posted messages 2 Registration date   Status Member Last intervention  
       
      Great!!! Thanks a lot!!!
      One small issue, when there is more than one empty line, the formula doesn't remove them all
      Can we change this in the formula?
      0
    2. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785
        

      =SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),CHAR(10)&CHAR(13)),CHAR(13)&CHAR(10),"")
      0
      1. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 785 > Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention  
         
        Or rather: 
        =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),CHAR(10)&CHAR(13)),CHAR(13)&CHAR(10),"");CHAR(13),"")

        to clean the remaining carriage returns.
        0