Is there a formula that will count the number of times a character or word appears in a cell? I have downloaded fields from a database where each item is separated by the word "and". For example the field would say Atlanta_and_Chicago_and_Detroit. I am trying to count the number of cities that show up in this field so in this example I would want it to return 3. Is there a simple formula to count the number of "and"s in a cell?
Thanks in advance
Hmmm...simple?...You be the judge.Originally Posted by aalbers
With A1 containing text that may contain 1 or more names separated by "and"
This formula counts the number of cities in A1. It returns 0 if the cell contains no text.
Please Login or Register to view this content.
Is that something you can work with?
Thanks, that worked great. I never would have thought of approaching it that way.
Thanks again.
I have a similar issue with counting repeated words in a single cell.
I have a list of equipment in a single cell that clients have hired. It looks like this:
A1: projector, microphone, laptop etc
Sometimes they hire more than one of the same thing like this (a different client for each row)
A1: projector, projector, microphone, laptop
A2: microphone, projector
A3: projector, projector
I then need to count the total number of each piece of equipment in the data range $A:$A. So I am using the COUNTIF function to count the number of times a word appears (in this case 'projector') in the data range $A:$A but it will only count each cell once instead of counting each word once.
HELP!!
count how many "projector" = 5
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"projector","")))/LEN("projector")
sorry
array formula
ctrl + shift + enter
not enter only.
Last edited by vlady; 02-19-2012 at 09:54 PM. Reason: sorry array formula
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
AWESOME!!
I was scratching my head for a while until you added the array part
Is there a way that you can make 'projector' a reference to another cell so that i can copy the formula without having to write it over and over?
you mean like this, assume the words will be in cell A5 that will be counted.
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,$A$5,"")))/LEN($A$5)
great!
it all works well, is there a way to make it case INsensitive?
substitute is a case sensitive function..
you can use the upper() lower() proper()
SUBSTITUTE(lower(A1:A3),$A$5,"")
[NM: post not related.]
Last edited by JimD; 06-27-2012 at 08:59 AM.
JimD,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi, I am new to this forum and new to using excel formula also . I found this thread while searching in google and registered to this group .
Below I have described the scenario.
A1 :projector , projector
A2 :Mad , Projector_d
projeCtor
projectoR
A5 :projector
I applied =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(LOWER(A1:A3),$A$5,"")))/LEN($A$5) this , I got correct results .
For this one below :
K1 : Audio
K2 : Audio , audio , mad
Audio, mad
Audio
audio
K3 :Audio
K5 :Audio
I apply same formula here : =SUM(LEN(K1:K3)-LEN(SUBSTITUTE(LOWER(K1:K3),$K$5,"")))/LEN($K$5) , it does not work , can someone help.
Thanks.
=SUMPRODUCT((MID(A1,ROW(1:100),LEN("and"))="and")*1)+1
@jinkachandu
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks