Removing Trailing Live Breaks

[mickzilla]

Hi There!

I've searched a bit for a solution to this, and can't find anythign.

I have a column that has a bunch of random text blurbs, and at the very end of these blurbs some have many extra line breaks. I want to remove just the trailing line breaks.

I can't use =CLEAN() because it removes *all* line breaks, and there are legitament line breaks in the middle of the text, its only the last bunch of line breaks that i want to remove in each blurb.

Thanks so much for your expertise!

-Mick

[lecxe]

Hi Mick

Try:

=LEFT(A1,LOOKUP(1,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>CHAR(10)),ROW(INDIRECT("1:"&LEN(A1)))))

HTH
lecxe

[Bjornar]

Try:

=LEFT(A1,LOOKUP(1,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>CHAR(10)), ROW(INDIRECT("1:"&LEN(A1)))))

I think this one will remove all linefeed, not just the last ones.


It took me some time to make this one, but I think this array formulas will do the trick:

Please Login or Register  to view this content.

It is an array formula so you have to confirm by Ctrl+Shift+Enter. Not just press enter.

[lecxe]

Hi Bjornar

=LEFT(A1,LOOKUP(1,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>CHAR(10)), ROW(INDIRECT("1:"&LEN(A1)))))

I think this one will remove all linefeed, not just the last ones.

Can you post at least 1 example where other than the last linefeeds are deleted? I'd like to improve the formula, if necessary.

Kind regards
lecxe

[mickzilla]

hi there - thanks so much for your excellent replies.

we're slow close...

lecxe - your formular appears to work on most of the blurbs, but seems to cut off the text wherever there is a ":"

Bjornar - your formula works, but only stips one of the line breaks at the end. Most of these blurbs have 2-3 line breaks at the very end.

thanks again for your continued help!

-mick

[lecxe]

Hi again

Thank you for your precise feedback.

You are right. I tried with some strings with a ":" and it failed. Instead of "...LOOKUP(1,..." it should be "...LOOKUP(2,...".

You can also try instead:

=LEFT(A1,LOOKUP(LEN(A1),ROW(INDIRECT("1:"&LEN(A1)))/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>CHAR(10))))

HTH
lecxe

[Bjornar]

Quote:
=LEFT(A1,LOOKUP(1,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>CHAR(10)), ROW(INDIRECT("1:"&LEN(A1)))))

I think this one will remove all linefeed, not just the last ones.

Can you post at least 1 example where other than the last linefeeds are deleted? I'd like to improve the formula, if necessary.

My fault. I forget to turn on text wrapping on the cells with your formula, but it was turned on in the cells with my array formula.

Instead of "...LOOKUP(1,..." it should be "...LOOKUP(2,...".

The first version cuts of to early, but this change does the trick. In my tests the first version cuts to early.

Bjornar - your formula works, but only stips one of the line breaks at the end. Most of these blurbs have 2-3 line breaks at the very end.

It works fine in my tests, and strips of all line breaks at the end. Even when there are multiple line breaks. Could the problem be that there are other linebreaks characters than ASCII #10 ? I use Alt+Enter in my tests. In other applications Enter (without Alt) gives a line break. I think this is ASCII #13 . Correct me if I'm wrong, just throwing out an idea...

[lecxe]

Mick

I also tried Bjornar's formula and it worked OK for me.

You can check the code of the trailing line separators with code().

Ex: to check for the code of the character in the 4th position

=CODE(MID(A1,4,1))

The formulas we posted assumed the code 10 (linefeed) at the end.

HTH
lecxe