Count characters in a Cell

manuprashar

New Member
Joined
Sep 22, 2002
Messages
1
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
or, if there might be blanks / spaces that you don't want to count:

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

Paddy

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-23 14:11, PaddyD wrote:
or, if there might be blanks / spaces that you don't want to count:

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

Paddy

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

priya biradar

New Member
Joined
Dec 26, 2013
Messages
5

ADVERTISEMENT

=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>
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,200
This formula would omit characters like , " ' . [space] and counts rest.

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,481
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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"))
 

priya biradar

New Member
Joined
Dec 26, 2013
Messages
5
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
 

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
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
 

Forum statistics

Threads
1,144,312
Messages
5,723,654
Members
422,508
Latest member
Lordkit1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top