Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to count characters in cell/range/worksheet in Excel?

Sometimes, you may need to count the length of the letters in a cell, a range or a whole worksheet in Excel, so now I talk about the methods for you to get it done quickly.

Count characters in cell/range with formulas

Count characters in the whole worksheet with Word

Count the number of a specific character in a string with Kutools for Excel good idea3

Count characters in cell/range with formulas

If only count the length of all characters in a cell, you can select a blank cell, and type this formula =LEN(A1) (A1 stands the cell you want to count letters, you can change it as you need), then press Enter button on the keyboard, the length of the letters in the cell has been counted. See screenshot:

Note: This method will count string length of all characters in the specified cell, including numbers, space, special marks, etc.

But if you want to count the characters in a range, you can type this formula =SUM(LEN(A1:B5)) (A1:B5 stands the range you want to count characters, you can change it as you need), then press Ctrl + Shift + Enter buttons on the keyboard, the length of the characters in the specified range has been counted. See screenshot:



Count characters in the whole worksheet with Word

If you want to count all the characters in a whole worksheet, there is not a direct way to count them in Excel, it must be with the help of Word.

1. Open the worksheet you want to count the letters, and press Ctrl + A to select the whole worksheet, then copy them to a Word document.

2. Then in Word, click Review > Word Count, see screenshot:

3. In the pop-up Word Count dialog, you can see the number of letters. See screenshot:

Count the number of a specific character in a string with Kutools for Excel

If you want to count only the occurrnce of a specific character in a string, you can use Kutools for Excel's COUNTCHAR function to quickly count the number of a character in a cell.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 
Free Download
free full-featured in 30 days

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Select a cell you want to output the counting result, and click Kutools > Kutools Functions > Statistical & Math > COUNTCHAR .See screenshot:
doc count characters 1

2. Then in the Function Arguments dialog, select the string that you want to count from to the textbox besides Within_text, and enter the character surrounded with double quotation marks which you want to count in the Find_text box. See screenshot:
doc count characters 2

Note: This function count character with case sensitive.

3. Click Ok to close the dialog, and drag the autofill handle down to apply the formulas to cells you need.
doc count characters 3

Relative Articles:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (5)
No ratings yet. Be the first to rate!
Xls
Xls
This comment was minimized by the moderator on the site
This formula counts characters, not letters as the title implies it should.
Reply
0
0
Deschreider M.M.
Deschreider M.M.
This comment was minimized by the moderator on the site
Copying the contents of the whole worksheet into word in order to be able to count the number of characters in the worksheet is an excellent advice! Thank you very much.
Reply
0
0
FETTY
FETTY
This comment was minimized by the moderator on the site
NAOMBA NISAIDIE KUJUA IDADI YA ROW NA COLUM KWENYE EXCEL
Reply
0
0
Sunny
Sunny     FETTY
This comment was minimized by the moderator on the site
Sorry? What's your problem？
Reply
Report
0
0
ABAT
ABAT
This comment was minimized by the moderator on the site
To count all characters in a worksheet: - Rather than bother copying to Word, just use formula
' =SUMPRODUCT(LEN(A1:B5)) '.
It works, try it!!
In fact SUM(LEN(A1:B5)) will probably crash with #VALUE! error if used over large ranges, or a lot of characters are in each cell,
particularly in older versions of Excel like Excel2000.

So if you are getting #VALUE! errors when using ' SUM(LEN(A1:B5)) ', switch to SUMPRODUCT.
I prefer this to tediously copying to Word, because with SUMPRODUCT :-

1) It works over large ranges easily, on cell formating of any kind

2) You can deduct parts of a range that may not be relevant (e.g. a cell, row or column that contains personal notes not relevant to the data you want to count)
e.g. =SUMPRODUCT(LEN(B1:L5799)) - LEN(C4) removes the count of characters in Cell C4 from the whole range B1:L5799
=SUMPRODUCT(LEN(B1:L5799)) - SUMPRODUCT(LEN(B462:L462)) removes the count of characters in Row 462 from the whole range B1:L5799
=SUMPRODUCT(LEN(B1:L5799)) - SUMPRODUCT(LEN(D1:D5799)) removes the count of characters in Col. D from the whole range B1:L5799

3) You can add ranges on more than one sheet, so without pasting every sheet to Word, you can get a total no. of characters in the whole Workbook
e.g =SUMPRODUCT(LEN(B3:L799))+SUMPRODUCT(LEN(Sheet2!B4:C7))+SUMPRODUCT(LEN(Sheet3!A1:C7000)) is totaling the count for data on 3 sheets

4) These formulae will update as your worksheet progresses - otherwise you will have to keep pasting to Word every time you alter your data and need a revised count of the characters

5) It keeps your information in one place.

Warning A) - Both ' SUMPRODUCT(LEN(A1:B5)) ' and ' SUM(LEN(A1:B5)) ' will fail if any of the individual cells in the range contain a formula that cannot compute and produce an error value such as #VALUE!, #NULL!, #REF!, #NUM!,#DIV/0! etc. Always best to combine error protection to your formulae in every cell.
You can use Error protection on SUMPRODUCT to flag an error, such as ' =IF(ISERROR(SUMPRODUCT(LEN(B4:C7))),"err",SUMPRODUCT(LEN(B4:C7)))'
which outputs the message "err" if any cell contains an error, but you cannot obtain the character count of the range until the cell(s) causing the errors are corrected, or the range is modified to omit the cell(s) containing the error.
Reply
0
0
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL