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?
=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?