remove common English words

What is good programming code for removing common English words (say a dozen, such as

Please Login or Register  to view this content.

, etc.) from cells in Excel? The questions I'm running into are whether to try to do it at the formula level, probably using Substitute, or at the VB level, such as with the code below.

When using the code below, the words in the strings to search are separated with commas (for example, keyword lists) and the words in my list of words to find have that same character on each side of the word (such as

Please Login or Register  to view this content.

). So, this method works okay, but will not find the common words at the beginning or the end of the strings, because if I add something like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

in order to find the common words at beginning or end of cell, then I wind up stripping characters out of other words such as ,theme.

Please Login or Register  to view this content.

So, I'm wondering what others have done to accomplish something like this.

Hello finlander,

It would help to see some actual data. Do have a workbook you post?

Hello finlander,

Why are there commas in column "M"?

Hi Leith,

Those are to replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

That works okay for stuff in the middle, because the common English word is stripped out and replaced with a comma, so that's fine, but if I try to expand the search (cells L9 and L10) to include beginning and end of the cell, then a new problem begins.

For example, if the search is expanded to search for

a,

at the beginning of a cell, then the text

Please Login or Register  to view this content.

anywhere would lose its last letter.

That's the part I'm struggling with.

Hello finlander,

Okay, that clears up that piece. So with this line...

with,General,the,Cable,a,Corporation,his

after the macro is run should look like this?

with,General,the,Cable,Corporation,his

Hello finlander,

I'm back. Had to cut up a ham and put it away. I understand now what you want. I'll put together a parsing routine for you. Are you familiar at all with Regular Expressions?

Hello, thanks for your help, yes, I've worked quite a bit with regular expressions, but I have not worked that much with VB, so it would have been a challenge to combine the two. I had thought that the solution might have involved regex to simply identify beginning or end of line, so that whatever list of common English words is used, it would would work without slaughtering other keeper words. Beyond that, I had no idea how to implement it, or if it was the right idea at all. Many thanks for your willingness to look more closely as this challenge.

update: to be precise, this text

the,then,General,with,Cable,a,lathe,Corporation,for

would ideally end up looking like this

General,Cable,lathe,Corporation

It was expanding my 'middle stuff' solution to handle 'beginning and end' without fouling up other words, that was baffling me.

Hello finlander,

Using Regular Expressions to parse and replace the words is the most flexible and expedient solution. Glad to hear you have some familiarity with them. At least the code won't look totally alien to you.

Hello finlander,

The macro below has been added to the attached workbook. A button on the worksheet runs it. The Find keyword range starts at "L5" and continues down to the last entry in column "L". Likewise, The Replace range starts at cell "M2". If you have any other punctuation in the words, like hyphens, apostrophes, etc. I will need to make a minor change to the macro. Otherwise it should work with no problems.

Find and Replace Macro

Please Login or Register  to view this content.

Hello finlander,

I'll make the needed changes to address the comma position problem. Afterwards, we can deal with any other issues that arise.

hi again, Leith,

sure, sound great.

On a side note, I've been studying the pattern that you came up with. It's very interesting, because the AND (&) operator is used to look for the item at any position (begin,middle,end), but does not require that the item be at begin,middle,end. I'm still studying it to see how it works. Do they call that a "look ahead" type of operator? For example, if the item (the word, the, for example) is at the beginning but not in the middle and not at the very end, then the final two-thirds of the pattern is just ignored, i.e, it's not required that Keyword actually exist in order to use the '&' operator between each segment of the pattern?

My question probably doesn't make sense .. sorry .. was just trying to figure out how an AND operator can simply be overlooked when not needed. I'll study it some more.

update: never mind .. it's the OR (|) operator that is busting it up into optional pieces. I see it now.

Hello finlander,

Here is the updated macro. This will ensure no commas are at the start or end of the string.

Please Login or Register  to view this content.

Hello finlander,

To answer your previous question about the ampersand character, it is used to concatenate the variable string "keyword" into the Regular Expression pattern.

Look aheads in VB Regular Expressions appear in this format: (?:exp), (?=exp), or (?!exp), where exp is the expression before looked for. Each language version of Regular Expressions has it differences, so these may not be familiar to you.

The pattern looks for the keyword at the beginning of the string (marked by the caret "^"), the middle of the string, and the end of the string (marked by the dollar sign "$"). The pipe character ("|") is used to express an OR condition.

Normally a pattern is fixed string. In this case, we are searching for a variable number of possible matches. By making keyword a separate string, it can be assigned whatever value we want to search for. Setting the Regular Expression property Global to True will search the string for all possible matches. The default setting is False. When false, only a single match is attempted.