count the # of line breaks in a cell in excel?

Assume your cell that need to find the # of break line is A1, put the following formula in a cell say B1

=SUM(IF(MID( A1,ROW( $A$1:OFFSET( $A$1,LEN(A1)-1 ,0)),1)= CHAR(10),1,0))

if you want instead find how many 8 in A1, use the following

=SUM(IF(MID(A1,ROW( $A$1:OFFSET( $A$1,LEN(A1)-1,0)),1)="8" ,1,0))

then activate it as an array formula, hit F2 then Ctrl+Shift+Enter, in the formula bar, you'll find the formula enclosed with {} and the cell will show the # of break line in it.

Note: if you want to change the object of test (A1 in the above) change only the A1 in the above formula to another cell address, never change $A$1 in the formula cause that's the array checking parts.

Hope this helps.

Not really, you are on the right track, but there is no function built into Excel to count the line breaks.. It would nice, and relatively easy given MS has the same function in Word to count the words in a document, essentially line breaks would amount to the same thing.

You just have to put it in a loop

Do -

While -

Or

If -

Then -

You have the right idea, but this is not an easy task.