How to count number of lines (line breaks) in a cell in Excel?
When working with data in Excel, it's common to encounter cells containing multiple lines of text separated by line breaks. For instance, imported addresses, product lists, or survey responses might appear as several lines within a single cell due to the use of Alt+Enter or data source formatting. Knowing how many lines a cell contains can be crucial for data analysis, cleanup, or reporting. This article explains practical ways to count the number of lines (line breaks) within a cell in Excel to help you better manage such data and streamline your workflow.
Easily split multiline cell contents into rows or columns in Excel
- Count number of lines in a cell with formulas
- VBA macro to count line breaks in selected cells and display line counts
Count number of lines in a cell with formulas
Excel does not provide a direct command to count line breaks in a cell, but you can easily achieve this with built-in functions. The following formulas help you count the number of lines in a cell, which correspond to the number of line breaks plus one. This is particularly helpful when you need to analyze survey responses, multi-item comment fields, or addresses kept in single cells.
1. Select a blank cell where you want to display the count. Enter the formula below and press the Enter key. Make sure to adjust the cell reference to the cell you want to analyze (for example, use A2).
=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1
Explanation: This formula calculates the number of line breaks (represented by CHAR(10)) in the cell, and adds1 to get the total number of lines.
Note: In this formula, A2 is the cell you need to count the number of lines for. If your data is in a different cell, simply adjust the reference.
2. After entering the formula, keep selecting the resulting cell. To apply the formula to other cells in the same column, drag the Fill Handle down to automatically count lines for additional cells, as shown in the screenshot below.
Practical tips and notes:
Formulas offer a quick, non-destructive solution to count line breaks, and work well for most regular data analysis scenarios. However, for more advanced needs—such as simultaneously processing large ranges or getting results for multiple selections—the formula approach may be less efficient compared to other methods.
Quickly create a speedometer chart in Excel:
Kutools for Excel's Split Cells utility helps you easily split cell contents by space, comma, new line or other separators into separated rows or columns in Excel as the below screenshot shown. Using this tool, you can further transform and analyze multidimensional text data after identifying line breaks, greatly streamlining your workflow.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
VBA macro to count line breaks in selected cells and display line counts
When dealing with large datasets or needing to count the number of lines (line breaks) across multiple cells at once, using a VBA macro can provide more efficiency and flexibility than manual formulas. The following solution is ideal for batch-processing tasks, such as summarizing responses, preparing data for export, or conducting quality checks on imported text.
Here is how you can quickly count the number of lines in each selected cell using a VBA macro. This will display the results in a message box, allowing you to review counts at a glance without modifying your worksheet data.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. In the VBA editor, click Insert > Module, and then paste the following code into the newly created module:
Sub CountLinesInSelection()
Dim rng As Range
Dim cell As Range
Dim lineCount As Long
Dim result As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to count line breaks in:", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
result = ""
For Each cell In rng
If Not IsEmpty(cell.Value) Then
lineCount = Len(cell.Value) - Len(Replace(cell.Value, Chr(10), "")) + 1
Else
lineCount = 0
End If
result = result & "Cell " & cell.Address(False, False) & ": " & lineCount & " line(s)" & vbCrLf
Next cell
MsgBox result, vbInformation, "Line Break Counts"
End Sub2. After entering the code, click the 
Run button to execute the macro. A dialog box will pop up asking you to select the range of cells you want to analyze—this can be any contiguous or non-contiguous selection.
3. The macro will count the number of line breaks (line feeds) in each selected cell and display a summary box listing each cell with its respective line count. If a cell is blank, the returned line count will be 0.
Tips and troubleshooting:
- This macro does not modify existing worksheet data, making it suitable when you need a quick summary or diagnostic check without altering or adding extra columns.
- If you receive a permission error when running the macro, ensure your workbook is saved as a macro-enabled file (*.xlsm) and that macros are enabled under Excel's security settings.
- When applying this method to very large selections, the message box may be lengthy; consider limiting the cell range for easier reading.
Using VBA is especially advantageous for batch analysis, reporting, or when you need a one-off overview across various worksheet regions. It’s flexible for power users but doesn’t automatically update like formulas when cell values change, so re-run as needed after edits.
Related articles:
- How to split multiline cell contents into separated rows/columns in Excel?
- How to put multiple lines of text in one cell in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in