• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Count Characters in Excel

Home » Excel » Excel Tips » Count Characters in Excel

By Jyoti Singh Leave a Comment

Count Characters in Excel

Excel Character Count (Table of Contents)

  • Count Characters in Excel
  • Explanation of Count Characters in Excel
  • How to Count Characters?

Count Characters in Excel Cell

Many times we need to count the characters in the cells, this might be because we have some limits in excel or maybe this has been restricted by the user that certain characters should be entered only in the cells. Hence this becomes important that we know how we can count a number of characters in a cell in excel.

Counting the number of character in excel is different from count the filled cells in excel. Counting the number of characters in excel means that we need to know how many alphabets are entered into the cells and on the other side counting the filled cells would mean that counting those cells only that have text or some value in them. So this is important that we know the difference between counting cells and counting the number of characters of excel cells as both of these topics are different.

When we count a number of characters in a cell this means that we are counting the alphabets that are in that cell. This becomes sometimes important to know because excel has its own limits.

In excel a numeric value can be inserted or shown up to some limits and same is the case with the formulas, we can only execute that formula that has the character limit of a maximum of 8192 charters. So if in any case if we see that the formula is giving error we should count the character first.

In excel counting the characters are very easy, for this, we can use the internal formula of excel that is called “LEN”. This function will count the Letters, number, characters and all spaces that are present in the cell. Since this function counts everything that is in the cells, this becomes important that we should know how we can exclude some of the alphabets or value that are in the cells.

Explanation of Count Characters in Excel Cell

Using the LEN function is the easiest way to calculate the characters that are present in the cell as this function does not need only one argument that is we just need to specify the cell for which we need to calculate the characters.

While using the function of LEN we have various options about how we want to calculate the characters, we can choose to count all the characters or choose to exclude some of the characters.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

Not only we can count characters from a cell, but we can also count the characters from the full range and this adds more usability to this function of LEN. While calculating the characters from the cell this should be noted that every character that is in the cell will be calculated and this means that even space inserted in the cell would be counted. Even if the space is not visible to us but this is still be counted as a character by the function of LEN.

The generic function that is designed to calculate the characters from the cells is the LEN, however, we can make some modifications to these functions to get the result as per the situation. If we have data where we have the text that includes some special characters and we want the special characters should not be counted then we can still make this happens. This just needs using the substitute function with the LEN function.

How to Count Characters in Excel Cell?

Using this LEN function we can easily get the count of characters that are in excel cell.

You can download this Count Characters Excel Template here – Count Characters Excel Template

Example #1 – Count Total Characters in a Cell

To count the number of characters in a cell we can simply use the function of LEN

=LEN(Cell)

Where “Cell” will mean the location of the cell for which we need to calculate the character.

Count Characters in Excel example 1

Example #2 – Count all Characters Excluding a Specific Character.

For this, we need to use substitute excel function inside of LEN.

Count Characters in Excel formula 2

Count Characters in Excel example 2

Example #3 – Counting Only a Specific Character.

To exclude some the characters from the count we just need to subtract the count of whole characters from the count of characters excluding specific characters

Count Characters in Excel example 3

The first LEN will give the complete count and the second part of the function will give us the count of cells excluding “@”.

At last, we will have the count of specific characters.

Example #4 – Counting all Characters of a Range Instead from a Single Cell.

The Len function is not capable of handling the Arrays, hence we cannot use this function to calculate the count of the whole of the range.

So we need some function that is capable of handling the arrays, arrays mean a source of data. So we will use the function of SumProduct that is very much capable of handling Arrays.

Excel formula 4

Sumproduct will sum the count of all LEN functions and hence we will get the count of the complete range.

example 4-1

This function will execute by calculating the characters of all the cells that are in the range, once this is done and the function has calculated the characters for all of the cells it will move to the function of SUM and will sum up all the character count and hence that is why we will get the character count of complete range.

Things to Remember

  • “Space” in the cell is also counted as a character by the LEN function.
  • While using the substitute function this should be remembered that this function is case sensitive. This means that “A” is not treated or searched as “a”.

Recommended Articles

This has been a guide to Count Characters in Excel Cell. Here we discuss how to count total characters in an excel cell using LEN, SUMPRODUCT and SUBSTITUTE Functions along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Excel Insert Image
  • Excel Formula for Grade
  • SUMPRODUCT and Multiple Criteria
  • Char In Excel
  • REPLACE Function in Excel
  • TEXT Formula in Excel
  • How to Split Cells in Excel?
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Excel Tips

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download Count Characters Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More