If you actually mean how many times a keyword appears in the URL, this explanation should help. If you mean how many times a keyword appears within the PAGE, please reply, as that's a bit more complicated.
Depending on your version of Excel, cells can contain up to 32K characters, so that will be your potential limit for your list of target keywords. As Victor mentioned, if you need greater scale, you'll need to turn to writing VBA code.
If this limit is acceptable, SeoTools can handle this challenge with ease. Here's how...
(Feel free to substitute your own locations)
- List your target keywords starting in cell A5
- List your URLs starting in cell C5
- Place this formula in cell A2:
="\b(" & StringJoin(A5:A1005, "|", TRUE) & ")\b"
(Expand beyond A1005 if more than 1,000 keywords)
- Place this formula in cell D6:
=StringJoin( RegexpFind( C5, $A$2, 1 ), ", " )
- Place this formula in cell E6:
=IFERROR( ROWS( RegexpFind( C5, $A$2, 1 ) ), "" )
- Copy both formulas in D6:E6 down for all URLs
I believe this should result in what you're looking for:
Hope this helps!