Count cell word

[JAMIR]

Hello everyone,

I have attached sample file. I want to count the vehicles in a single cell.

Regards,

Zameer Shaikh

 

[Asheesh]

Hi, Try the below in cell B2

LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

 

[JAMIR]

Hello Asheesh Sir,

Its work perfectly. But if suppose instead of , i give space( ) then how to count.

Regards,

Zameer

 

[Asheesh]

I have used space as an identifier and not the comma(,)

 

[JAMIR]

Hello Asheesh Sir,

But its counting the blank cell also. i.e. if A2 having no data and its showing 1

Regards,

Zameer

 

[Asheesh]

Use the below..

IF(A2="","",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)

 

[Khalid NGO]

Or replace the space " " with "-"
and remove +1 at end:

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

 

[shrivallabha]

If all vehicles are having Maharashtra passing and all numbers begin with MH then you could also use:
=(LEN(A2)-LEN(SUBSTITUTE(A2,"MH","")))/2

 

[JAMIR]

Asheesh

Thanks for your reply. Its Work perfectly.

Thanks once again. Take Care.

shrivallabha

Thanks for your reply. This is what i want.

Take Care....

Regards,


Zameer

 

[JAMIR]

Or replace the space " " with "-"
and remove +1 at end:

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

Thanks for your reply...

Take care...

Zameer