Trim carriage returns at end of string

[Betherski]

I have an extract that contains several comments fields. Some lines only have 1 comment populated, and some have several populated. I want to combine into a single comment field, with a line between each comment. I have this formula to combine them:
=CONCATENATE(EN2,CHAR(10),EO2,CHAR(10),EP2,CHAR(10),EQ2,CHAR(10),ER2,CHAR(10),ES2,CHAR(10),ET2,CHAR(10),EU2,CHAR(10),EV2,CHAR(10),EW2,CHAR(10),EX2,CHAR(10),EY2,CHAR(10),EZ2,CHAR(10),FA2,CHAR(10),FB2,CHAR(10),FC2,CHAR(10),FD2,CHAR(10),FE2,CHAR(10),FF2,CHAR(10),FG2,CHAR(10),FH2,CHAR(10),FI2,CHAR(10),SJ2,CHAR(10),FK2,CHAR(10),FL2,CHAR(10),FM2)

I then want to delete the delete the carriage returns at the end, but keep the carriage returns between the comments.

=TRIM(SUBSTITUTE(A1,CHAR(10),""))
This is deleting all of the carriage returns. How can I just get it to delete everything after the end of the text?

 

[dave3009]

Which version of Excel are you using?
And what do you mean "after the end of the text"? Does each cell have whitespace in it?

 

[Betherski]

I am using Excel 2016. By after the end of the text, I mean after the last populated comment. The concatenated cell is very large, because it contains all of the carriage returns, whether there was one comment populated or 20 comments populated.

 

[dave3009]

OK, see the JoinString function by Rick Rothstein in this thread.

Concat UDF - VBA Question

I wanted to ask how to change the code below so it will ignore blank cells: The UDF works great but I receive a "value" when all the cells in a range are all blank Function Concat(myRange As Range, Optional myDelimiter As String) Dim r As Range Application.Volatile For Each r In myRange If...

www.mrexcel.com

 

[Peter_SSs]

I am using Excel 2016.

Please add that information to your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

If you want to stick to a standard worksheet function approach you could use a structure like this. I have only done a few of the sections to demonstrate the concept - you would have to add the others.

=SUBSTITUTE(MID(CONCATENATE(IF(EN2="","","|"&EN2),IF(EO2="","","|"&EO2),IF(EP2="","","|"&EP2),IF(EQ2="","","|"&EQ2)),2,1000),"|",CHAR(10))

A bit surer would be

=SUBSTITUTE(REPLACE(CONCATENATE(IF(EN2="","","|"&EN2),IF(EO2="","","|"&EO2),IF(EP2="","","|"&EP2),IF(EQ2="","","|"&EQ2)),1,1,""),"|",CHAR(10))

 

[Betherski]

If you want to stick to a standard worksheet function approach you could use a structure like this. I have only done a few of the sections to demonstrate the concept - you would have to add the others.

=SUBSTITUTE(MID(CONCATENATE(IF(EN2="","","|"&EN2),IF(EO2="","","|"&EO2),IF(EP2="","","|"&EP2),IF(EQ2="","","|"&EQ2)),2,1000),"|",CHAR(10))

A bit surer would be

=SUBSTITUTE(REPLACE(CONCATENATE(IF(EN2="","","|"&EN2),IF(EO2="","","|"&EO2),IF(EP2="","","|"&EP2),IF(EQ2="","","|"&EQ2)),1,1,""),"|",CHAR(10))

Worked perfectly!! Thank you so much!!!

 

[Betherski]

OK, see the JoinString function by Rick Rothstein in this thread.

Concat UDF - VBA Question

I wanted to ask how to change the code below so it will ignore blank cells: The UDF works great but I receive a "value" when all the cells in a range are all blank Function Concat(myRange As Range, Optional myDelimiter As String) Dim r As Range Application.Volatile For Each r In myRange If...

www.mrexcel.com

Thanks very much for the response! I was trying this out and having issues (mostly due to my Excel skills). I ended up using Peter's solution, just because it was simpler (for me). I really appreciate the help!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.