# Using COUNTIF in Excel when you need to manipulate text

The problem is this: I have a column full of nouns. These nouns could be something like: "Doctor", "doctor" or "DOCTOR " (with a space at the end). Clearly I need to normalize those records before using `COUNTIF`. Rigth now I'm just using a new column "in the middle" in which I normalize the data and then I use COUNTIF on those normalized data.

I'm doing this because I can't understand how I can normalize the range taken for COUNTIF as first argumenti. Is it possibile to apply functions to the first argument? Or is there a more suitable function I can use in this case?

I have at home the 2007 and at the office 2010 so you can aim for both version.

Requested example:

Column A has:

``````"Doctor "
"doctor"
"  doctoR"
"fireman"
``````

I need to do something like COUNTIF( NORMALIZE(A1:A4) ; "doctor" ) because " Doctor" will not match "doctor" because of the space. Right now I'm doing NORMALIZE(A1:A4) in B1:B4 and then COUNTIF( B1:B4; "doctor")

NORMALIZE is something I just inventend, not a real function of course.

-
can you explain with some example with input and expected output? – Siva Charan Apr 19 '12 at 17:43
edited with an example. – dierre Apr 19 '12 at 17:51
So you mean if you do countif(A1:A4, "doctor"), your expected output should be 3 or 1? – Siva Charan Apr 19 '12 at 17:56
I don't think you understood my problem. My expected output is 3, and 3 I have. I'm just asking if there's a way to normalize the range inside the COUNTIF and not using two steps in the process. – dierre Apr 19 '12 at 18:07
Refer to my answer. – Siva Charan Apr 19 '12 at 18:32

``````=COUNTIF(A:A,"*doctor*")
``````

OR

``````=COUNTIF(A2:A4,"*doctor*")
``````

## OUTPUT

``````3
``````
-
If I have something like "University professor" should I use the wildcard in the middle? Does it accept + like for regexp,like "University +professor"? (1 or more). I do not have backtick on my kb. It should be 4 spaces between University and professor. – dierre Apr 19 '12 at 18:39
@dierre: `+` won't do. If you set `University*professor`, you get total count. And if you want specifically to check 4 spaces between University and professor then your formula can be `University professor` – Siva Charan Apr 19 '12 at 18:55
ok but that will check also "Universityprofessor" right? – dierre Apr 19 '12 at 19:07
@dierre: yes, it will count this one too. – Siva Charan Apr 19 '12 at 19:09

If you have a limited number of nouns and variations, than you can use some addition in your formula and capture all the possible variations (capitalization shouldn't matter, but spaces does):

=COUNTIF(A2:A4,"doctor")+COUNTIF(A2:A4,"doctor ")+COUNTIF(A2:A4," doctor")

Using a helper column or column "in the middle" is also a good way (and sometimes a necessary way) to manage shaping the data for analysis. Often they can help keep your formulas from getting too complex.

-