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

Answer 1 / 1
Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
 
Hello,

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


--
Best regards
Patrice
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
Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
  

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),CHAR(10)&CHAR(13)),CHAR(13)&CHAR(10),"")
0
Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783 > 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