calculate the sum of characters in a cell

D

David Billigmeier

Do you mean if you have "12345" in a cell you want 1+2+3+4+5?? In this case
this formula array entered (Ctrl+Shift+Enter) will work:

=SUM(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

If this isn't what you wanted try explaining a little better or posting some
examples.
 
N

Nat

Thank you for your reply

I'm looking to calculate how much alphabetical characters I have in each cell
 
D

David Billigmeier

Should numerical values and symbols be counted as well, e.g. should the 1,8,3
and "-" be counted in "A1FD-83"? If so all you need is =LEN(A1)

If not you need something a little more complicated. I see you said
"alphabetical characters" so here is an example counting only the values
appearing in the English alphabet (A through Z):

=SUMPRODUCT(--(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>=65),--(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))<=90))

If this is still not what you wanted please post some examples.
 
N

Nat

Thank you "LEN" this is what I'm looking for

David Billigmeier said:
Should numerical values and symbols be counted as well, e.g. should the 1,8,3
and "-" be counted in "A1FD-83"? If so all you need is =LEN(A1)

If not you need something a little more complicated. I see you said
"alphabetical characters" so here is an example counting only the values
appearing in the English alphabet (A through Z):

=SUMPRODUCT(--(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>=65),--(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))<=90))

If this is still not what you wanted please post some examples.
Click to expand...
 
N

Nat

Is there a way to count the numerical values and symbols and even spaces
except the spaces after the sentence?
 
D

David Billigmeier

What sentence are you talking about? Is that the contents of your cell? If
so, is the end of your sentence a period? Do you want just numerical,
symbols and spaces counted? Or do you want alphabetic characters counted as
well (e.g. A through Z)? So many questions, I am confused again what you
mean, sorry. Please post a couple of examples...
 
N

Nat

I had exported into excel some data, every cell has 15 characters if not live
character then the system will fill it up with spaces now I want to get rip
of the spaces but only from the spaces that the system put in to fill up the
15 characters not the spaces that are between the words
 
D

David Billigmeier

=TRIM(A1)

--
Regards,
Dave


Nat said:
I had exported into excel some data, every cell has 15 characters if not live
character then the system will fill it up with spaces now I want to get rip
of the spaces but only from the spaces that the system put in to fill up the
15 characters not the spaces that are between the words
Click to expand...
 
Top