Extracting part of text (seperated with a carriage return ALT Enter) with a formula

Angus Bishop

Hi,

I have a huge sheet with a comments column (column A). In many cells in this column there are multiple lines of comments i.e.

2/3/22 blah blah bla

1/3/22 blah blah blah

I need to create a new column (column B) to extract via a formula all of the text in column A but ONLY the 1st line of text before the carriage return at the end of that line. In the case above, column B would only show:

2/3/22 blah blah bla

I have been plaing around with various options using CHAR(10) but cant seem to figure out how to ONLY extract the 1st line to the first carriage return - this forumla gives me everything in the cell:

=LEFT(Comments@row, LEN(Comments@row) - FIND(CHAR(10), Comments@row))

....and this gives me everythign AFTER the 1st carriage return:

=RIGHT(Comments@row, LEN(Comments@row) - FIND(CHAR(10), Comments@row))

I suspect that this can be done but if anyone has already knows how it will save me hours of frustraion! Thanks.

Angus

Kevin Smith

Hi

I was able to get this to work but my formula was just:

=LEFT(Comments@row, FIND(CHAR(10), Comments@row))

I haven't tried to figure out what your formula is doing but I found this smaller formula to work well.

I hope that works for you.

Kevin Smith

Hi

I was able to get this to work but my formula was just:

=LEFT(Comments@row, FIND(CHAR(10), Comments@row))

I haven't tried to figure out what your formula is doing but I found this smaller formula to work well.

I hope that works for you.

Angus Bishop

Thanks Kevin - that worked for me!

Kevin Smith

My pleasure