Microsoft Excel 2010 - complete product...

I need to count the number of times a particular letter is in the third place of a string of letters and numbers. These strings are in Column A to M, rows 1 to 650. I have tried combining = Countif and = Mid in different ways but have been unsuccessful.

Thank you.

Brian W

✔ Best Answer

Try this, it's a bit of a "cheat",

as it uses both =COUNTIF() and Wild Card characters.It seems to work, but I have not done extensive testing:

=COUNTIF(A1:M650,"??X*")

The X is the single character, in the Third position that your looking for,

the first two ? characters are the wild card for Any Single Character,

the trailing * means Any Character, Any Number of Times.As best as I can tell this only works for TEXT,

if the cells your checking are ALL NUMBERS then it above will not work,

or at least I could not get it to work with ONLY numbers.

MIKE

I tried a couple of array formulas, but nothing seemed to work consistently. If you don't mind a User Defined Function, this should work:

Function countD(ByVal rng As Range) Application.Volatile For Each cell In rng If Mid(cell, 3, 1) = "d" Then tmpCount = tmpCount + 1 End If Next countD = tmpCount End FunctionAfter pasting this code into a standard VBA module, enter =countD(A1:M650) in any cell and it will count each "3rd position d" in the range entered.

If it will

alwaysbe A1:M650, then use this and just enter =countD()Function countD() Application.Volatile For Each cell In Range("A1:M650") If Mid(cell, 3, 1) = "d" Then tmpCount = tmpCount + 1 End If Next countD = tmpCount End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear DerbyDad03, Thank you for your response. I have never used a User Defined Function so I do not know how to enter the code that you provided. Can you give me any references so that I can learn how to use it?

Thanks again,

Brian W

Look here: http://office.microsoft.com/en-us/e...

It's more than you probably need, but it gives a pretty complete picture of UDF's.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Try this, it's a bit of a "cheat",

as it uses both =COUNTIF() and Wild Card characters.It seems to work, but I have not done extensive testing:

=COUNTIF(A1:M650,"??X*")

The X is the single character, in the Third position that your looking for,

the first two ? characters are the wild card for Any Single Character,

the trailing * means Any Character, Any Number of Times.As best as I can tell this only works for TEXT,

if the cells your checking are ALL NUMBERS then it above will not work,

or at least I could not get it to work with ONLY numbers.

MIKE

DerbyDad03, Thank you for your assistance; I will read the info you cited about User Defined Functions and will try to use them the next time I run into a similar problem.

Brian W

Dear Mike, Thank you for your assistance. The strings that I am using contain text and your solution worked perfectly.

Brian W

Ask Your Question

Weekly Poll

Do you think its a good idea for Amazon to divide its new second headquarters between two locations?

Discuss in The Lounge

Poll History