Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Count line breaks in cell

Sometimes, we use line breaks to break the lengthy content while using excel worksheet. By doing so, the content can be displayed completely in the cell. Have you ever thought of counting line breaks in the cell? In this article, we will show how to count line breaks in cell

Formula

You can use the formula given below to count line breaks in a cell. The formula is the combination of CHAR, SUBSTITUTE, and LEN functions.  

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

Explanation

To start with, LEN function measures all of the characters present in B5. Subsequently, SUBSTITUTE expels all of the “line returns” from the content in B5 by searching “CHAR(10)” or, in other words, codes for returning character in the Windows. LEN restores the outcome within a second LEN and it calculates characters with no carriage returns. Subtraction occurs between second and the first count and 1 adds up in the final outcome.

Example

In this example, we count line breaks in a cell as shown in the screenshot. We have selected a cell next to a string that we desire to count line break. We typed the above-mentioned formula and press “Enter” to get the result. See screenshot

Figure 1 Example of Count Line Breaks in Cell

Notes

Of you are working on Mac system then use the following formula:

=LEN(SUBSTITUTE(A2,CHAR(13),""))

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I am trying to figure out how to use a sumif on SUBSTITUTE(A2,"*","~*") where A2 is a formula with 3371-4
Solved by M. Y. in 19 mins
Hello, I need help with IF and SUBSTITUTE formula in excel
Solved by A. A. in 43 mins
I have 4 column. when i fill the first 3 column. in 4th column i need a unique id number and i generate unique id number using RANDBETWEEN formula [ =CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(1000,9900) ] but i want ..... when i'm filling the 3 column after that 4th column run this formula and generate unique id automatically
Solved by X. Q. in 20 mins
I have text and number in multiple cells (Ex: Cell 1: 4S, Cell 2: 3.5A, Cell 3: 8O) and want to sum the number values only (Ex: 4 + 3.5 + 8). How can I do this?
Solved by O. A. in 60 mins
a date formula that results in showing the date as 29/12/00 (date format as dd/mm/yy) - use column BA to calculate 364 days (same date format) in column BS, if BA blank, then calculate 364 days using column AX. Results for column BA are correct. However, if column BA is empty, then it should calculate using column AX, which has data, but the result is always 29/12/00, regardless of the date in column AX. I have used this formula with success in another workbook, but this file wont work! Formula: =IF(AND(LEN($BA74)=0,LEN($AX74)=0),"",IF(LEN($BA74)=0,DATE(YEAR($AX74),MONTH($AX74),DAY($AX74)+364),DATE(YEAR($BA74),MONTH($BA74),DAY($BA74)+364)))
Solved by A. L. in 60 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc