# Excel: Count Lines in a Cell

Recently I was using an Excel spreadsheet to prepare some data and I had a constraint to work to in the number of lines of data the target system could display. Unfortunately rather than rows of data in Excel this was to match against rows of data in a single Excel cell. So the question is, how can you count the number of rows in an Excel cell?

# The Algorithm

There was no inbuilt function I could find for getting this information, and whilst I could write a quick VBA function to return a value I like to try and avoid macro enabling spreadsheets wherever possible. There are always discussions about security and enabling the code whenever you share it with someone; something I’d rather not spend my time on. Therefore to address this I came up with a somewhat quick and dirty approach using inbuilt functions.

The first step was to count how many characters there are in a cell. This can be achieved using the LEN function.

``````=LEN(A2)
``````

Next I removed all of the line feed characters (ASCII value 10) in the cell, by replacing them with nothing; and counted the number of characters again. Excel registers line feeds as additional characters and as markers for a newline. Curiously Windows uses carriage return + line feed for new lines, but replacing the carriage return (ASCII value 13) made no difference to the character count.

``````=LEN(SUBSTITUTE(A2,CHAR(10),""))
``````

Then by taking the difference of the two I can tell how many new lines are in the cell.

``````=B2-C2
``````

Finally if there is content in the cell then there must be at least one line not ending in a newline and if there isn’t then there would be no lines. We can therefore check the length and add one if we have at least some content present.

``````=D2+IF(LEN(A2)>0,1,0)
``````

# Combining it

Taking all of the elements together and using an example of cell A2, we can then produce the following combination of in-built functions to count the number of lines of text in a cell.

``````=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+IF(LEN(A2)>0,1,0)
``````
Author: Stephen Millard
Tags: | excel |

• ## Excel - Failed Tabbed Data Paste

02 May 2019

In my day job I work with a lot of raw data, and I make judicious use of Microsoft Excel to help me in the builds and transforms I need to accomplish. As many people are no doubt aware, Excel can be quite useful with the use of tab separated text as it treats each tab as a way to separate data out into cells and each new line as a new row in a spreadsheet. At least that’s what I thought. Recently however, I fell foul of finding tab separated data on my Windows clipboard not pasting into Excel in this way. But I did track down why this occurred.

• ## Batch Generating Worksheet Hyperlinks in Excel

29 Apr 2017

In my work I often find myself building spreadsheets to carry out calculations, manage sophisticated processes and capture information. These spreadsheets can often extend to covering many worksheets and as a result it can be awkwardly slow to navigate to particular worksheets just by paging back and forth with the navigation buttons.

In this post I’m going to illustrate a couple of useful techniques to help you navigate around.

• ## Extracting Excel File Path Information Using Excel Formulas

29 Jun 2014

In several of the template Excel spreadsheets I use in my work there are references to clients and version numbers that are included. This is for reference purposes, particularly if the spreadsheet might be printed or exported as a soft copy (e.g. PDF). Whilst it is possible to maintain this information manually I decided that this should be something that could be improved by virtue of the file name containing such information (as these are the very basis of file management on projects). I’d referenced the file path and elements of it in the past and I thought that this would be something that should be relatively straight forward to achieve; and here’s how I got on ….

• ## Auto Incrementing in Excel

29 Jun 2013

In this post I’m going to share a tip about auto incrementing numbers in Excel. I’ve frequently found myself creating incremental number lists (e.g. 1, 2, 3, 4, 5, …, etc.) and whilst Excel is quite helpful at providing ways to create such lists I find its methods are often a bit limited for my needs - so I came up with another way that I think you might find more resilient.