I realze the caret mans you want to keep the following letters, as in "keeping" them in a replace situation. I was looking at them to identify whole word matches, so I presume if he code detects a letter or number before or after the search text it finds, it should regard that hit as NOT a whole word match. However, if the hit has punctuation or a special character on either adjacent side, such as a comma, $, !, :, etc., I think it should regard the term as a whole word and extract it. I tried plugging that long pattern above into the code and got syntax errors, and it's too many characters for me to figure out what's wrong with it. But I do know that if I run the code with .Pattern = "[^A-Za-z0-9\ ]", it ignores all hits that have a dash (or hyphen) before or after the search term (without spaces). That's what I'm after - how to get the code to accept hits with adjacent punctuation or special characters as whole word matches. I still don't know how to get there.

That was the string of characters I put into cell D2 to check that .Pattern = "[^A-Za-z0-9\ ]" was all that was required to remove all those particular characters.
It was.

I can't figure out what you're talking about, nor why.
All the string manipulation, array population and matching to the SearchText happens in memory.
Without using message boxes or debug.print to show the individual array elements, you can't see what's going on.
Where is it you are finding these "hits that have a dash (or hyphen) before or after the search term (without spaces)." that you think are not being treated as whole words?
And what do you mean by extract it? I was of the impression the intent was to copy entire rows.

After reading and re-reading....

Maybe try adding this UDF.
Then in the main procedure, on the Split line, change RemovePunctuation to removeSpecial

NoSparks, hyou've hit the nail on the head. That new function is what I was looking for. I just ran some tests and it does just what I want it to do.

I know all that stuff happens in memory and it's hard to trace (one reason I asked for help). And I was testing the code changes against my database, which does contain punctuation: one of my searches was for a term, some of which occurrences had a preceding dash and some of which didn't. And by "extract", well, I always called the original macro "Extract_Rows_by_Text" because to me, I am searching for text, then copying it, then extracting it to a new workbook. Not JUST copying it. When one queries a database to produce a report, is that not called an extract? I never heard anyone wanting a report about data ask for a "copy", but for an "extract", yes!

Semantics aside, I greatly appreciate your help and patience with this task. It means a lot!

I just couldn't get my head around what you were describing.

Don't know if there's many applicable instances in the sample data but had you said do a search for 'can' and seq 26 should show up,
it would have been the perfect illustration to straighten me out on what was happening.

Anyway glad it's sorted out.

Sorry about that. I threw that database together just for testing purposes (to protect the real data's privacy) and I didn't prepare it properly to illustrate what I was looking for. I also assumed you understood that a whole word match meant that such a search would include words that had adjacent punctuation or special characters and exclude those with adjacent letters or numbers. Anyway, thanks again!