Remove trailing Carriage Returns from text

    Hi,


    I am really struggling with this one. I have around 10 columns which looks up multiple data from another sheet and brings it back into a cell and enters a carriage return (Char(10)) between each element of data.


    In another cell, i join all the data from these 10 cells. The problem is that not all 10 cells will have data in them all of the time so i need to remove any leading and trailing carriage returns from the text otherwise i get huge gaps in my data. Can I do this through a formula or vba (will need help understanding any vba)? Any help on this would be appreciated. :)

    Re: Remove trailing Carriage Returns from text


    Or if you want to replace the carriage return with a normal space,try:


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


    where B5 contains the original text.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

    Re: Remove trailing Carriage Returns from text


    Thank you all for your replies.


    Jindon - it is the just the carriage returns at the end that i want to remove and that file you attached does that.:thumbup: Can i use that code in my file and have the code run without the user having the click on a button?

    Re: Remove trailing Carriage Returns from text


    FYI.


    This formula will remove just the last carriage return:


    =SUBSTITUTE(B5,CHAR(10)," ",LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),"")))


    again where B5 contains the text to convert.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

    Re: Remove trailing Carriage Returns from text


    I know this post is very old but I could not find anything that would do this. So I finally put this together from all the forums.


    Select Cell or Range and this will remove All Line Breaks from the Left and Right, and removes any blank lines. Then Trim it all to look good. Alter as you see fit. I also made one that removes All Line Breaks, if interested. TY


    Even SEVEN years after he posted his solution ... jindon does keep receive Thanks ... !!!


    A true LEGEND ...!!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)