cpatte7372

asked on

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

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

Last Comment

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

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

Kevin

=SUMPRODUCT(ISTEXT(A1:A4)*

Kevin

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

=SUMPRODUCT(LEN(A1:A4))

to get 6

=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

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

Kevin

Here's a simple fast formula

In E1

=len(A1&B1&C1&D1)

A "row" is across...

In E1

=len(A1&B1&C1&D1)

A "row" is across...

ASKER

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

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

=SUMPRODUCT(ISTEXT(A1:A4)*

Kevin

ASKER

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

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)*

ASKER

Oops,

zorvek, wrote the code in manually and it worked.

You're a star...

Cheers mate.

zorvek, wrote the code in manually and it worked.

You're a star...

Cheers mate.

ASKER

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

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

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.

ASKER

ssaqibh

You really do come through... Thanks dude - now that's working.... :-)

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

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(BG

Kevin

If mine works then why did you not accept it?

ASKER

Hi ssaqibh,

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

Sorry

Cheers

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

Sorry

Cheers

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

Saqib

ASKER

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

=counta(A1:A4)