How to count characters in Excel
Microsoft has upped the educational content in its Excel blog. Here are highlights from recent posts, including the answer to a very popular Any Answers query.
More than 50,000 people have visited AccountingWEB.co.uk to find the answer to Michael Ung’s 2007 Any Answers query, Is there a formula that will count the number of characters in a cell?
For those who want a more detailed answer, help is at hand from Microsoft’s Steven Thomas. In a recent post on the company’s Excel blog, he explained how to use the LEN function to get a cell's character count.
Unlike Word, Excel does not have a built-in function that lets you select a block of text and see how many characters the selection contains. But Excel’s LEN function is the next best thing and lets you count characters by using a formula.
To apply LEN, you will need to enter a text string in the same way as any other formula - though often you may want to use a cell reference rather than actual text data:
Here's an example:
The Formula bar will display the logic entered into the selected cell (D9), and whenever the text in C9 changes, the LEN function will recalculate. A separate Excel Help article on the Microsoft Office website explains ways to count values in a worksheet, including word counts.
The character count tutorial is just one of many new postings on the Excel blog. Other recent highlights include:
- Introduction to PowerPivot for Excel
- Excel Skills Builder - a training series made up of five short videos that you can work through to develop your knowledge of different tasks. The subjects covered include: Tables, sorting and filtering; visual data analysis with conditional formatting and sparklines; collaboration and sharing techniques; and macros/reporting.
@ JohnBanfield
One common reason for wanting to count the number of characters in a cell is if the cell formula returns the worksheet name including full path and file name, and you just want to extract the worksheet name element. The preceding path and filename will be of indeterminate length. With a combination of MID(), LEN() and FIND() you can achieve this. Maybe there are easier ways of doing it in the latest versions of Excel but I am still stuck in 2003 and to be frank any spreadsheets that I use I would want to be backwardly compatible to 2003 anyway.
I expect I could think of several other applications if I thought hard enough.
With kind regards
Clint Westwood.
Counting for Accountants
If you are counting the characters in the cell, its usually because you want to restrict the number of them that you are transferring elsewhere. Say you had a cell (a1) with 100 characters in it, and you only wanted to pick out the first 15 of them ie to compare a vlookup with another cell, then use =mid(a1,1,15), and this will start from character 1 and stop at 15. Likewise if you wanted to pick from the 8th to the 12th use =mid(a1,8,12).
I would be interested in knowing why people would want to actually count the characters - Once I had to import a description from excel into a Journal line and it would fail if it went over a certain amount of characters.