# Count characters in a Cell

#### manuprashar

##### New Member
I am looking for a function that will help me count characters[text or numbers] in a cell

All help is appreciated,

regards,

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Assuming your value is in cell A1:

=LEN(A1)

Enjoy.

or, if there might be blanks / spaces that you don't want to count:

=LEN(A1)-(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

EDIT: I guess Aladin bouldn't find the right "tongue in cheek" smilie!:

=LEN(SUBSTITUTE(A1," ",""))
This message was edited by PaddyD on 2002-09-23 16:54

or, if there might be blanks / spaces that you don't want to count:

=LEN(A1)-(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

=LEN(SUBSTITUTE(A1," ","")
This message was edited by aladin akyurek on 2002-09-23 22:20

=LEN(SUBSTITUTE(A1," ","")

[SIZE=-1][ This Message was edited by: aladin akyurek on 2002-09-23 22:20 ][/SIZE]

i m lookinf for a funcation which counts only word in a cell
but i want to count only word as its has to be 4- word 4
which formula i need to use for this kinldy suggest
Ex:-
 "Word". 7 =len()

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

This formula would omit characters like , " ' . [space] and counts rest.

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"'",""),"""",""),".",""),",","")," ",""))

thanks a lot ... its working

This formula would omit characters like , " ' . [space] and counts rest.

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"'",""),"""",""),".",""),",","")," ",""))
thanks a lot ... its working
That formula counts characters like digits, colons, semi-colons, slashes, dashes, and so on. Is that what you actually wanted? Or did you just want to count letters only (and ignore all the rest of the characters)? If so, then give this formula a try...

=SUMPRODUCT((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)>="A")*(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)<="Z"))

hi i m looking for a formula which helps me to count how many cells in a row not a blank cells only wording cells .

like Ex:-

the to or one 4

kinldy help on the same

Can you advise clearly, you said in the initial question you wanted characters, but are also referring to words

If its just characters excluding spaces, You have been given a couple of answers already

Can you advise what number you would want returned for the below example

My Test Text.

hi i m looking for a formula which helps me to count how many cells in a row not a blank cells only wording cells .

like Ex:-

the to or one 4

kinldy help on the same

Replies
9
Views
180
Replies
2
Views
97
Replies
15
Views
439
Replies
1
Views
345
Replies
7
Views
84

1,218,812
Messages
6,144,617
Members
450,560
Latest member
afcmRamos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back