how to not count a cell congaing certain characters

[MoparGirl]

I created a formula that counts text AB, BC, CD, DE, etc. In the formula I use "AB*" and the same for each individual set of letters for a wildcard. My problem is that in that same set of cells there could possible be text that has a * in it like this. AB* which means that this person is working extra. I used the formula to count how many times AB is in the cells I have put in the formula. How do I get it to NOT count if there is and AB*? I tried to do a -Countif, but when I did that is subtracted how many times AB* appeared. I hope this makes sense and that you can help me. Thanks.

[MarvinP]

Hi MoparGirl and welcome to the forum.

If you are working on a single cell and you want to count how many "AB" are in that string, here is the plan.

Replace all "AB" in the string with "" (nothing). Now see if the string is shorter using Len(). It will be 2 characters shorter for each "AB" replaced by nothing.

That is the plan on counting stuff in a string in a single cell.

You need to show a sample of your work for us to see if that is the real problem.

[alansidman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[MoparGirl]

Excel Forum Sample.xlsx

I figured out that my cell range regarding the previous question was too broad. Now, I was wondering if you could help me to make the formula less cumbersome, I know there has to be a shorter stream to get the same result. Also, this is my first time attaching a document in the forum I hope I did it right.

[MarvinP]

Hi MoparGirl (is that a golf alias?)

Try this formula for counting all the "AB" or "AB*" in your columns.

=COUNTIF(C:E,"AB")+COUNTIF(C:E,"AB?")

"AB?" should mean everything that starts with AB and has a single character after it.
"AB*" should mean everything that starts with AB and has anything after it.

[Crooza]

Hi MoparGirl (is that a golf alias?)

Are golf carts Chrysler powered now?

[MarvinP]

I thought she wanted More Par Scores while playing golf...

[MoparGirl]

No, it is definitely a classic car reference. My favorite, a 1969 Plymouth Barracuda.