danielsan210684
New Member
- Joined
- Aug 9, 2018
- Messages
- 2
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))
<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!
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!