Using LEFT to extract text, then applying wildcard to the result to do a COUNTIF

[danielsan210684]

Hi all, I have the results of a survey which allows people to free-type their answers. I have cleaned the answers up however there are a litany of spelling errors for many of the same words.

I need to count the instances of these answers, including the misspelled words. I have a list of the correct spellings and using the COUNTIF and LEFT function I am extracting the first 5 letters from each correct spelling and counting the instances in the original list of answers.

However, I need to apply the wildcard "*" function to the five letters the LEFT formula extracts so the COUNTIF will pick up correctly, incorrectly and expanded versions of answers.

Currently I have the below formula:

=COUNTIF($A$3:$A$6001,LEFT(B3,5))

	A	B	C
1	Protectng	Protecting	=COUNTIF($A$2:$A$6001,LEFT(B2,5))
2	Conservtion	Conservation	=COUNTIF($A$3:$A$6001,LEFT(B3 ,5))​

<tbody>
</tbody>

How do I get it so the COUNTIF applies the wildcard and count all of the relevant words in column A?

Any help would be massively appreciated!

 

[Rick Rothstein]

Currently I have the below formula:

=COUNTIF($A$3:$A$6001,LEFT(B3,5)&"*")

Does adding what I show in red above do what you want?

 

[danielsan210684]

Does adding what I show in red above do what you want?

Hi, thanks for this. I figured it out shortly after I posted with the exact same solution you provided! Really appreciate your response (would have replied sooner but I've been off work since Friday, sorry for the delay). It always seems to be the simple things that trip me up.