Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial

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 120 handy functions, makes your jobs easier. 
Go to Download
Free Trial 60 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 > 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

Tip.If you want to quickly count the times of a word appears in a cell or range, please try to use the Kutools for Excel’s Count times a word appears as shown in the following screenshot. It’s full function without limitation in 60 days, please download and have a free trial now.

Count times a word appears
doc formula 1

Relative Articles:

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!
Read More Download Now Purchase

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Read More Download Now Purchase
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    ABAT · 3 months ago
    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.
  • To post as a guest, your comment is unpublished.
    FETTY · 9 months ago
    NAOMBA NISAIDIE KUJUA IDADI YA ROW NA COLUM KWENYE EXCEL
  • To post as a guest, your comment is unpublished.
    Deschreider M.M. · 2 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Xls · 4 years ago
    This formula counts characters, not letters as the title implies it should.
English English
en Englishaf Afrikaanssq Shqipar العربيةhy Հայերենaz Azərbaycan dilieu Euskarabe Беларуская моваbg Българскиca Catalàzh-CN 简体中文zh-TW 繁體中文hr Hrvatskics Čeština‎da Dansknl Nederlandset Eestitl Filipinofi Suomifr Françaisgl Galegoka ქართულიde Deutschel Ελληνικάht Kreyol ayisyeniw עִבְרִיתhi हिन्दीhu Magyaris Íslenskaid Bahasa Indonesiaga Gaeligeit Italianoja 日本語ko 한국어lv Latviešu valodalt Lietuvių kalbamk Македонски јазикms Bahasa Melayumt Malteseno Norsk bokmålfa فارسیpl Polskipt Portuguêsro Românăru Русскийsr Српски језикsk Slovenčinasl Slovenščinaes Españolsw Kiswahilisv Svenskath ไทยtr Türkçeuk Українськаur اردوvi Tiếng Việtcy Cymraegyi יידיש