CODEWITHSUNDEEP
excelexcel-formula
ramon
ramon

Reputation: 73

how to remove line break on the last text

How to remove line break for example in a1 column i have 3 lines which is separated by line breaks and on the last line there is extra line break which i want to remove please note that sometimes there is more line breaks on the last line that i need to remove and i need to maintain the line break separates on each line except for the last line i have tried below formula but i cannot find the solution to maintain line break on each line

=SUBSTITUTE(A1,CHAR(10)," ")

dfddgfggfgfg(char)10
jhjkhkhjhhjk(char)10
hkjhkhjhhhjh(char)10

Upvotes: 3

Views: 6740

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

This will replace all the Char(10)s at the end regardless of how many:

=IF(RIGHT(SUBSTITUTE(TRIM(A1),CHAR(10) & CHAR(10),""),1)=CHAR(10),LEFT(SUBSTITUTE(TRIM(A1),CHAR(10) & CHAR(10),""),LEN(SUBSTITUTE(TRIM(A1),CHAR(10) & CHAR(10),""))-1),SUBSTITUTE(TRIM(A1),CHAR(10) & CHAR(10),""))

Upvotes: 1

Andrew
Andrew

Reputation: 7880

Not perfect, but this will remove all the line breaks in the last 3 characters of the cell:

=LEFT(A1,LEN(A1)-3)&SUBSTITUTE(RIGHT(A1,3),CHAR(10),"")

If you think there may be more than 3 line breaks at the end, or that a line won't be as short as 2 characters, you may increase that value (in both places).

Also, as stated in the other answer, make sure that in the cell's properties, the Wrap Text option is checked.

Upvotes: 2

Kate66
Kate66

Reputation: 92

This will remove a trailing CHAR(10) if it exists:

=IF(RIGHT(A1,1)=CHAR(10),LEFT(A1, LEN(A1)-1),A1)

It first checks if the last character is CHAR(10), and if it is it trims it off.

Edit Make sure you get the format on the cell correct. You'll need to click "Wrap Text" for the cell you put the formula in. To test it, you can just put in "=A1". If all the breaks are gone, then it's a display issue!

Upvotes: 3

Related Questions