# Adding Letters in Excel

Hi Experts

Can show me how to  simply count the number of letters in a row?

For example, the letters A,B,C,D could be in cells A1:A4. How would like be able to count the letters with answer 4 in cell A5.

Cheers

Carlton
Microsoft Excel

Last Comment
cpatte7372
Saqib Husain

If it is always 1 letter each then you can use

=counta(A1:A4)
Use this formula to count the number of text values in those cells (versus empty or numeric values):

=SUMPRODUCT(ISTEXT(A1:A4)*1)

Kevin
Saqib Husain

if it could be aa,b,cc,d then you can use

=SUMPRODUCT(LEN(A1:A4))

to get 6
Having fun guessing what you really want ;-)

This will count the number of As, Bs, Cs, and Ds, in those four cells regardless of what is in them:

=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,{"A","B","C","D"},"")))

Kevin
cyberkiwi

Here's a simple fast formula

In E1

=len(A1&B1&C1&D1)

A "row" is across...
cpatte7372

Hi Guys,

Thanks all for responding.

I wish I had read your responses sooner as I obviously didn't make myself clear..

I know I said count the number of letters, however I meant if a cell has either a single letter or a number of letters in it then that would be counted as one. e.g if cell A1 contained a single letter 'B' or three letter 'ABC', then that would be counted as one.

Again, if you could help me that would be great, and sorry for not responding sooner..

Cheers
Use this formula to count the number of text values in those cells (versus empty or numeric values):

=SUMPRODUCT(ISTEXT(A1:A4)*1)

Kevin
cpatte7372

zorvek,

thanks for getting back to me.

I copied and paste your formula into excel but it just returned the same formula in the cell

=SUMPRODUCT(ISTEXT(A1:A4)*1
cpatte7372

Oops,

zorvek, wrote the code in manually and it worked.

You're a star...

Cheers mate.
cpatte7372

I really do wish I would fully test these formulas before announcing their working fine.

I put Zorvek's formula to test on the attached spreadsheet, but its giving me the numbers 31 in cells BN3, BO3, BP3 though to BS3.

The sad thing is Zorvek and most of you guys are probably busy working on other stuff so won't get a reply until later this evening when I really would like response soon... oh well. If someone is available to take a look I would appreciate it.

Cheers
E-MINIS-DOWv2.xlsm
Saqib Husain

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
cpatte7372

ssaqibh

You really do come through... Thanks dude - now that's working.... :-)
The problem is there are formulas in the cells and a blank produced by a formula is a text value.

Here is another formula that would work. It counts the total number of cells and then subtracts the cells that contact blanks (from formulas) or are empty:

=ROWS(BG3:BG33)-COUNTIF(BG3:BG33,"")

Kevin
Saqib Husain

If mine works then why did you not accept it?
cpatte7372

Hi ssaqibh,

I accepted the wrong answer. Is there a way to re-submit?

Sorry

Cheers
Saqib Husain

Yes, you have to click on the link "Request attention" at the bottom right of your question box and file a request to reopen the question.

Saqib
cpatte7372

This solution worked exactly as I wanted it to. Thanks
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY