# Excel Magic Trick #223 Count Single Character In Text String

How many “X”s are ther in that word? See how to count individual letters in a word. See how to count the occurrence of a given character in a text string. See the SUMPRODUCT, MID, ROW, INDIRECT functions in an array formula that Counts individual characters.

Nguồn: https://samurai-weapons.net/

Xem thêm bài viết khác: https://samurai-weapons.net/category/cong-nghe/

How can we use same to count numbers and special characters In a text

How can I count total text ..?

It is for single word but if I wanna count all the words in B9 excluding number…..

I have a question, I'm setting up a excel program for work, We have a bus company, So drivers get an award for safe miles. Right now they keep track of all there miles on paper with a pencil.. lol.. I have created a document on excel similar to the page but would like to have it kind of all automated. I am trying to find out that if a Check mark appears then they can add the two mileages together if it don't add one column to the total column its safety miles and regular miles driven. They loose their safety miles if they have one accident but keep the total miles for life. so safety miles are only added on if a check mark appears in the OK column. see what i'm saying?

Shrodinger wave eqation was probably much easier than this

We can also try Len(B9)-len(substitute(B9,"x",""))

Why does this trick not seem to work for numbers?

i want to know how many carractor in the cell withouth space for example""'"'''' I am A active associate in the tec-mahindra. please revert me at my email id chaudhary.rakesh23@gmail.com

we need your best support

wow,,it solved one of my excel problem,,,,,amazing

Super Fun

;MID(J19635;1;1));J19635;1)+1) its not sophisticated but it works… it checks every character if it is zero , and if so it moves to the next one until it finds something else…

;IF(MID(J19635;17;1)="0";IF(MID(J19635;18;1)="0";IF(MID(J19635;19;1)="0";IF(MID(J19635;20;1)="0";IF(MID(J19635;21;1)="0";MID(J19635;7;1);MID(J19635;21;1));MID(J19635;20;1));MID(J19635;19;1));MID(J19635;18;1));MID(J19635;17;1));MID(J19635;16;1));MID(J19635;15;1));MID(J19635;14;1));MID(J19635;13;1));MID(J19635;12;1));MID(J19635;11;1));MID(J19635;10;1));MID(J19635;9;1));MID(J19635;8;1));MID(J19635;7;1));MID(J19635;6;1));MID(J19635;5;1));MID(J19635;4;1));MID(J19635;3;1));MID(J19635;2;1))

Thanx for the immediate answer… this is how i finally solved it this afternoon… =RIGHT(J19635;LEN(J19635)-FIND(IF(MID(J19635;1;1)="0";IF(MID(J19635;2;1)="0";IF(MID(J19635;3;1)="0";IF(MID(J19635;4;1)="0";IF(MID(J19635;5;1)="0";IF(MID(J19635;6;1)="0";IF(MID(J19635;7;1)="0";IF(MID(J19635;8;1)="0";IF(MID(J19635;9;1)="0";IF(MID(J19635;10;1)="0";IF(MID(J19635;11;1)="0";IF(MID(J19635;12;1)="0";IF(MID(J19635;13;1)="0";IF(MID(J19635;14;1)="0";IF(MID(J19635;15;1)="0";IF(MID(J19635;16;1)="0"

Maybe also:

=LEN(A9)-LEN(SUBSTITUTE(A9,"0",""))+ISNUMBER(SEARCH("0",A9))

maybe:

=IF(ISNUMBER(SEARCH("0",B9)),LEN(B9)-LEN(SUBSTITUTE(B9,"0",""))+1,"No Zeros")

0000000000000ΥΑΧ5901

here's a little tricky one… i want to define the position of the first NON zero character in this cell… in this example the character "Y"… how do i do that?

Yes, that is a great method!! It is MUCH better: shorter, not volatile!

Thanks for the post!

Hi Sir

I have watched many of your excel videos and all of them has been very informative and helpful for me to supplement my knowledge.I thank you wholeheartedly.

But I feel that this can be solved in a simpler way

=LEN(Text)-LEN(SUBSTITUTE(Text,"X",""))

mokkk

@custermom , you are welcome!

@beda402 , you are welcome!

YouTube wont let me post the formula, so see the e-mail I sent…

Also, spreadsheets have been around for so long, that the upper end of what is possible can be very confusing if you are missing the basics. The excelisfun Basics series and Highline Class Series cover a lot of basics…

Yes, it is very confusing if you do not understand each part of the formula. If you download the workbook, it has notes and reference videos that may help. You can search for and then watch this video title about how to download the workbooks with notes:

Search For Excel Videos Download Excel Files excelisfun

That … is VERY confusing, I see your comment to eabbbm, I'm assuming that means you've left individual notes to help with the formula. I understand the MID, that's very helpful actually.

Dear eabbbm,

I do not understand your question.

–excelisfun

Dear eabbbm,

You MUST download the workbooks that i supply. They have all the formulas and lots of good notes that can help you study. Search for this video and then watch it, it will show you how to download:

Search For Excel Videos Download Excel Workbooks excelisfun

–excelisfun