Calculating the number of matching words between two text cells

Hi,
I would need your help to determine the macro to calculate the number of matching words between two cells:
- A: Text with several keywords separated by "," or ";" or ":" or "."
- B: Text with several keywords separated by "," or ";" or ":" or "."
- C: I would like to receive the number of keywords that match in A and B

Eg:
A1 = heading keywords 1
A2 = Environment; Engineering; Environmental Engineering; Climate change; VOC; Waste treatment; Waste disposal

B1 = heading keywords 2
B2 = Pollutant; Environment; Geology; Engineering; Waste; Climate change

C2 = heading matching
C1 = 5 (Environment; Engineering; Climate Change; Waste) - In this case Waste is contained in two keywords and should count twice -

Hi MarvinP,

Thanks!
I need to know which of the keywords separated by ; or , or . or any other symbol are included in both cells
The fact that Waste is alone in B2 but it part of two keywords in A2 should count as partial match twice

I did not see anything in the forum? Any advice on how to do it?

Hi
its a bit clunky and may be unstable, but this macro seems to work
it searches for all the words and phrases in cell A2 in the string of text in A3
if it finds and exact match it counts as full; if the word forms part of a word or phrase in A3 it returns a partial match

Please Login or Register  to view this content.

Hi NickyC,
Many thanks for the code! I will run on my table