Solved Using MS Excel Countif and Mid

Microsoft Excel 2010 - complete product...
June 29, 2013 at 06:06:30
Specs: Windows XP Pro SP3, 3.0 Mz/1.0Gb
 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

See More: Using MS Excel Countif and Mid

June 29, 2013 at 18:24:07
 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

#1
June 29, 2013 at 08:51:21
 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 Function```After 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 always be 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.

Report •

#2
June 29, 2013 at 12:38:43
 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

Report •

#3
June 29, 2013 at 14:51:11
 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.

Report •

Related Solutions

#4
June 29, 2013 at 18:24:07
 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

Report •

#5
June 30, 2013 at 15:32:33
 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

Report •

#6
June 30, 2013 at 15:35:00
 Dear Mike,Thank you for your assistance. The strings that I am using contain text and your solution worked perfectly.Brian W

Report •