-1

I have an excel sheet in which a column has cells with 2 consecutive line breaks and I would like to replace them with a single line break.

I've actually been able to achieve that using a VBA function:

Function ClearLineBreaks(cell As Range) As String
    ClearLineBreaks = Replace(cell.Value, vbLf & vbLf, vbLf)
End Function

But I was wondering if it would be possible to achieve the same result using only Excel functions.

Joan Lara
  • 1,362
  • 8
  • 15
  • Use _Find and Replace_ – Storax Jan 07 '23 at 13:59
  • @Storax That would work, but I'm looking for a permanent solution as more cells will be added to that column that will have the same problem. – Joan Lara Jan 07 '23 at 14:09
  • What about formulas (`SUBSTITUTE`)then, but this will not work without an extra column? – Storax Jan 07 '23 at 14:16
  • @Storax The extra column is not a problem (I know it has to be like that). What would it look like using the ```SUBSTITUTE``` function? – Joan Lara Jan 07 '23 at 14:20
  • 1
    `=SUBSTITUTE(A1,CHAR(10) & CHAR(10),CHAR(10))` – Storax Jan 07 '23 at 14:27
  • It works, I was sure I tried it (maybe I used ```CHAR(13)``` instead of ```CHAR(10)```). If you want to put the code as an answer I will accept it. – Joan Lara Jan 07 '23 at 14:42
  • 1
    Glad to help. See also [here](https://stackoverflow.com/questions/12747722/what-is-the-difference-between-a-line-feed-and-a-carriage-return) regarding the difference between new line `CHAR(10)` and carriage return `CHAR(13)` – Storax Jan 07 '23 at 14:45

1 Answers1

0

For future visits and following @Storax advice, this is what worked for me:

=SUBSTITUTE(A1,CHAR(10) & CHAR(10),CHAR(10))
Joan Lara
  • 1,362
  • 8
  • 15