gravanoc
Active Member
- Joined
- Oct 20, 2015
- Messages
- 351
- Office Version
- 365
- Platform
- Windows
- Mobile
What is the best formula to use to find an accurate count of a word when that word could possibly appear as part of another word?
Ex: Word to search for is "Excel" in range A3:A255. However, it should not count as an occurrence if "Excel" is found inside another phrase, like "Excellent" or "Excel Is Great". Each value is separated by a comma without a trailing or preceding space. Last, although this isn't a current scenario, if the word is repeated in a cell it would be interesting if that is taken into account, but is not necessary. My example table below should help clarify. If the formula met all but the last condition, the count would be 21. With the last condition it would be 25.
Ex: Word to search for is "Excel" in range A3:A255. However, it should not count as an occurrence if "Excel" is found inside another phrase, like "Excellent" or "Excel Is Great". Each value is separated by a comma without a trailing or preceding space. Last, although this isn't a current scenario, if the word is repeated in a cell it would be interesting if that is taken into account, but is not necessary. My example table below should help clarify. If the formula met all but the last condition, the count would be 21. With the last condition it would be 25.
MASTER Showpad tools_Tag Reporting vFinalOnline.xlsm | |||
---|---|---|---|
AW | |||
3 | Excel | ||
4 | Excel Is Great | ||
5 | Excel,Excel Is Great | ||
6 | |||
7 | Excel | ||
8 | Excel | ||
9 | Excel,Excel | ||
10 | Excelsior | ||
11 | |||
12 | Excel In Life | ||
13 | |||
14 | Excel | ||
15 | |||
16 | |||
17 | Excel | ||
18 | Excellent | ||
19 | Excelsior | ||
20 | Excelsior | ||
21 | Excel,Excel | ||
22 | Excelsior | ||
23 | |||
24 | Excelsior | ||
25 | Excellent | ||
26 | Excelsior | ||
27 | Excelsior | ||
28 | Excelsior | ||
29 | Excel,Excel | ||
30 | Excelsior | ||
31 | |||
32 | Excelsior | ||
33 | Excelsior | ||
34 | |||
35 | |||
36 | |||
37 | Excel | ||
38 | Excelsior | ||
39 | Excelsior | ||
40 | |||
41 | |||
42 | Excel Is Great | ||
43 | Excelsior | ||
44 | |||
45 | Excellent | ||
46 | Excel | ||
47 | |||
48 | Excel | ||
49 | Excelsior | ||
50 | Excelsior | ||
51 | Excelsior | ||
52 | Excelsior | ||
53 | Excelsior | ||
54 | Excellent | ||
55 | Excelsior,Excel | ||
56 | Excel In Life | ||
57 | Excelsior | ||
58 | |||
59 | |||
60 | |||
61 | Excelsior | ||
62 | Excelsior | ||
63 | Excel | ||
64 | Excelsior | ||
65 | Excelsior,Excel | ||
66 | Events | ||
67 | Excelsior | ||
68 | |||
69 | |||
70 | |||
71 | Excellent | ||
72 | Excellent | ||
73 | Excel Is Great | ||
74 | |||
75 | |||
76 | Excellent | ||
77 | |||
78 | Excellent | ||
79 | |||
80 | |||
81 | |||
82 | Excelsior | ||
83 | |||
84 | Excelsior,Excel | ||
85 | Excelsior | ||
86 | Excelsior,Excel | ||
87 | |||
88 | |||
89 | Excelsior | ||
90 | Excelsior | ||
91 | |||
92 | Excellent | ||
93 | Excellent | ||
94 | Excelsior | ||
95 | Excellent | ||
96 | Excelsior | ||
97 | Excelsior | ||
98 | Excelsior | ||
99 | |||
100 | Excelsior | ||
101 | Excellent | ||
102 | |||
103 | Excelsior | ||
104 | Excelsior | ||
105 | Excelsior | ||
106 | Excel In Life | ||
107 | |||
108 | Excel | ||
109 | |||
110 | |||
111 | |||
112 | Excellent | ||
113 | Excellent | ||
114 | |||
115 | Excel | ||
116 | |||
117 | |||
118 | |||
119 | Excelsior | ||
120 | Excellent | ||
121 | Excelsior | ||
122 | |||
123 | Excellent | ||
124 | Excellent | ||
125 | Excelsior | ||
126 | |||
127 | Excellent | ||
128 | |||
129 | |||
130 | |||
131 | |||
132 | Excel Is Great | ||
133 | Excellent | ||
134 | Excelsior | ||
135 | |||
136 | Excelsior | ||
137 | Excellent | ||
138 | Excel,Excel | ||
139 | Excellent | ||
140 | |||
141 | Excellent | ||
142 | Excellent | ||
143 | Excelsior | ||
144 | Excellent | ||
145 | |||
146 | Events | ||
147 | Excellent | ||
148 | |||
149 | |||
150 | Excellent | ||
151 | |||
152 | |||
153 | |||
154 | Excellent | ||
155 | Excellent | ||
156 | Excelsior | ||
157 | |||
158 | Excellent | ||
159 | Excellent | ||
160 | Excel Is Great | ||
161 | |||
162 | Excellent | ||
163 | Excellent | ||
164 | Excellent | ||
165 | Excellent | ||
166 | Excelsior | ||
167 | |||
168 | Excellent | ||
169 | Excellent | ||
170 | |||
171 | Excellent | ||
172 | Excellent | ||
173 | Excellent | ||
174 | Excelsior | ||
175 | Excellent | ||
176 | Excelsior | ||
177 | |||
178 | Excellent | ||
179 | |||
180 | Excellent | ||
181 | Excelsior | ||
182 | Excellent | ||
183 | |||
184 | Excellent | ||
185 | Excellent | ||
186 | Excelsior | ||
187 | Excelsior | ||
188 | |||
189 | Excellent | ||
190 | Excellent | ||
191 | Excellent | ||
192 | Excellent | ||
193 | Excellent | ||
194 | |||
195 | Excellent | ||
196 | Excellent | ||
197 | Excel | ||
198 | Excellent | ||
199 | Excellent | ||
200 | Excellent | ||
201 | Excellent | ||
202 | Excellent | ||
203 | Excellent | ||
204 | Excellent | ||
205 | Excellent | ||
206 | Excellent | ||
207 | Excel Is Great | ||
208 | Excellent | ||
209 | |||
210 | |||
211 | |||
212 | Excellent | ||
213 | Excellent | ||
214 | |||
215 | Excellent | ||
216 | |||
217 | Excellent | ||
218 | Excellent | ||
219 | Excelsior | ||
220 | |||
221 | Excellent | ||
222 | Excellent | ||
223 | Excellent | ||
224 | |||
225 | Excellent | ||
226 | Excellent | ||
227 | |||
228 | Excellent | ||
229 | |||
230 | |||
231 | |||
232 | Excelsior | ||
233 | |||
234 | Excellent | ||
235 | Excelsior | ||
236 | Excellent | ||
237 | Excellent | ||
238 | |||
239 | Excel Is Great | ||
240 | Excellent | ||
241 | Excellent | ||
242 | Excellent | ||
243 | Excellent | ||
244 | Excellent | ||
245 | |||
246 | |||
247 | |||
248 | |||
249 | Excelsior | ||
250 | |||
251 | |||
252 | |||
253 | |||
254 | |||
255 | |||
256 | |||
257 | |||
Data |