Removing 'stop words' from a sentence in excel

**shashankbansal** · 07-06-2012, 10:16 AM

Hi,

I have an excel sheet with one column having one sentence in each cell of the column (rest all the cells of the sheet are vacant).

Now, i want to 'find' and 'remove' a list of pre-specified stop words such as "a", "the", "it", "of", etc. from every sentence and then get the resulting sentence pasted in the adjacent columns's cell.

(also for example the word "a" should be removed only when it is a separate word and not part of a word. Likewise for other stop words).

Have been trying to do this for quite sometime now, read posts related to this on different forums, but didn't get much help as they don't deal with this specific problem.

Can anyone please help me do this? Enclosing a test file showing what i want to do.

Thanks,
Shashank

**stanleydgromjr** · 07-06-2012, 11:27 AM

shashankbansal,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveSeparators macro.

**shashankbansal** · 07-09-2012, 02:37 AM

Thanks Stanley! That was indeed quite helpful.

Warm Regards,
Shashank

**stanleydgromjr** · 07-09-2012, 07:57 AM

shashankbansal,

You are very welcome. Glad I could help.

Thanks for the feedback.

Come back anytime.

**PiaHarrison** · 05-23-2014, 01:11 AM

Hello Stanley!

Thanks very much for the above advice. I'm very new to excel macros and was trying to use your code with a longer list of stop words, as per below. However I keep getting an error: Compile error: Expected: line number or label or statement or end of statement
The stop words are highlighted in red and the words "Sub RemovesSeparators ()" is highlighted in yellow with an arrow pointing to it. Do you know what I can do to fix this?
Many thanks,
Pia

Option Explicit
Sub RemoveSeparators()
' stanleydgromjr, 07/06/2012
' http://www.excelforum.com/excel-gene...-in-excel.html
Dim s, b
Dim i As Long, ii As Long, lr As Long, h As String
s = Array(
"s ", "ll ", "ve ", "a ", "i ", "m ", "am ", "able ", "about ", "above ", "abroad ", "according ", "accordingly ", "across ", "actually ", "adj ", "after ", "afterwards ", "again ", "against ", "ago ", "ahead ", "aint ", "aint ", "all ", "allow ", "allows ", "almost ", "alone ", "along ", "alongside ", "already ", "also ", "although ", "always ", "am ", "amid ", "amidst ", "among ", "amongst ", "an ", "and ", "another ", "any ", "anybody ", "anyhow ", "anyone ", "anything ", "anyway ", "anyways ", "anywhere ", "apart ", "appear ", "appreciate ", "appropriate ", "are ", "arent ", "arent ", "around ", "as ", "as ", "a ", "aside ", "ask ", "asking ", "associated ", "at ", "available ", "away ", "awfully ", "back ", "backward ", "backwards ", "be ", "became ", "because ", "become ", "becomes ", "becoming ", "been ", "before ", "beforehand ", "begin ", "behind ", "being ", "believe ", "below ", "beside ", "besides ", "best ", "better ", "between ", "beyond ", "both ", "brief ", "but ", "by ", "came ",
"can ", "cannot ", "cant ", "cant ", "cant ", "caption ", "cause ", "causes ", "certain ", "certainly ", "changes ", "clearly ", "cmon ", "cmon ", "co ", "com ", "come ", "comes ", "concerning ", "consequently ", "consider ", "considering ", "contain ", "containing ", "contains ", "corresponding ", "could ", "couldnt ", "course ", "cs ", "currently ", "dare ", "darent ", "darent ", "definitely ", "described ", "despite ", "did ", "didnt ", "didnt ", "different ", "directly ", "do ", "does ", "doesnt ", "doing ", "done ", "dont ", "down ", "downwards ", "during ", "each ", "edu ", "eg ", "eight ", "eighty ", "either ", "else ", "elsewhere ", "end ", "ending ", "enough ", "entirely ", "especially ", "et ", "etc ", "even ", "ever ", "evermore ", "every ", "everybody ", "everyone ", "everything ", "everywhere ", "ex ", "exactly ", "example ", "except ", "fairly ", "far ", "farther ", "few ", "fewer ", "fifth ", "first ", "five ", "followed ", "following ", "follows ", "for ", "forever ", "former ", "formerly ",
"forth ", "forward ", "found ", "four ", "from ", "further ", "furthermore ", "get ", "gets ", "getting ", "given ", "gives ", "go ", "goes ", "going ", "gone ", "got ", "gotten ", "greetings ", "had ", "hadnt ", "hadnt ", "half ", "happens ", "hardly ", "has ", "hasnt ", "hasn t ", "have ", "havent ", "haven t ", "having ", "he ", "hed ", "he d ", "hell ", "he ll ", "hello ", "help ", "hence ", "her ", "here ", "hereafter ", "hereby ", "herein ", "heres ", "heres ", "hereupon ", "hers ", "herself ", "hes ", "hes ", "hi ", "him ", "himself ", "his ", "hither ", "hopefully ", "how ", "howbeit ", "however ", "hundred ", "id ", "id ", "ie ", "if ", "ignored ", "ill ", "ill ", "im ", "im ", "immediate ", "in ", "inasmuch ", "inc ", "indeed ", "indicate ", "indicated ", "indicates ", "inner ", "inside ", "insofar ", "instead ", "into ", "inward ", "is ", "isnt ", "isnt ", "it ", "itd ", "itd ", "itll ", "itll ", "its ", "its ", "it s ", "itself ", "ive ", "ive ", "just ", "keep ", "keeps ", "kept ", "know ",
"known ", "knows ", "last ", "lately ", "later ", "latter ", "latterly ", "least ", "less ", "lest ", "let ", "lets ", "lets ", "like ", "liked ", "likely ", "likewise ", "little ", "look ", "looking ", "looks ", "low ", "lower ", "ltd ", "made ", "mainly ", "make ", "makes ", "many ", "may ", "maybe ", "maynt ", "maynt ", "me ", "mean ", "meantime ", "meanwhile ", "merely ", "might ", "mightnt ", "mightnt ", "mine ", "minus ", "miss ", "more ", "moreover ", "most ", "mostly ", "mr ", "mrs ", "much ", "must ", "mustnt ", "mustnt ", "my ", "myself ", "name ", "namely ", "nd ", "near ", "nearly ", "necessary ", "need ", "neednt ", "neednt ", "needs ", "neither ", "never ", "neverf ", "neverless ", "nevertheless ", "new ", "next ", "nine ", "ninety ", "no ", "nobody ", "non ", "none ", "nonetheless ", "noone ", "nor ", "normally ", "not ", "nothing ", "notwithstanding ", "novel ", "now ", "nowhere ", "obviously ", "of ", "off ", "often ", "oh ", "ok ", "okay ", "old ", "on ", "once ", "one ", "ones ", "ones ", "only ", "onto ", "opposite ", "or ", "other ", "others ", "otherwise ", "ought ", "oughtnt ", "oughtnt ", "our ", "ours ", "ourselves ", "out ", "outside ", "over ", "overall ", "own ", "particular ", "particularly ", "past ", "per ", "perhaps ", "placed ", "please ", "plus ", "possible ", "presumably ", "probably ", "provided ", "provides ", "que ", "quite ", "qv ", "rather ", "rd ", "re ", "really ", "reasonably ", "recent ", "recently ", "regarding ", "regardless ", "regards ", "relatively ", "respectively ", "right ", "round ", "said ", "same ", "saw ", "say ", "saying ", "says ", "second ", "secondly ", "see ", "seeing ", "seem ", "seemed ", "seeming ", "seems ", "seen ", "self ", "selves ", "sensible ", "sent ", "serious ", "seriously ", "seven ", "several ", "shall ", "shant ", "shant ", "she ", "shed ", "shed ", "shell ", "shes ", "shell ", "shes ", "should ", "shouldnt ", "shouldnt ", "since ", "six ", "so ", "some ", "somebody ", "someday ", "somehow ", "someone ", "something ", "sometime ",
"sometimes ", "somewhat ", "somewhere ", "soon ", "sorry ", "specified ", "specify ", "specifying ", "still ", "sub ", "such ", "sup ", "sure ", "take ", "taken ", "taking ", "tell ", "tends ", "th ", "than ", "thank ", "thanks ", "thanx ", "that ", "thatll ", "thatll ", "thats ", "thats ", "thats ", "thatve ", "thatve ", "the ", "their ", "theirs ", "them ", "themselves ", "then ", "thence ", "there ", "thereafter ", "thereby ", "thered ", "thered ", "therefore ", "therein ", "therell ", "therell ", "therere ", "therere ", "theres ", "theres ", "theres ", "thereve ", "thereupon ", "thereve ", "these ", "they ", "theyd ", "theyll ", "theyre ", "theyve ", "theyd ", "theyll ", "theyre ", "theyve ", "thing ", "things ", "think ", "third ", "thirty ", "this ", "thorough ", "thoroughly ", "those ", "though ", "three ", "through ", "throughout ", "thru ", "thus ", "till ", "to ", "together ", "too ", "took ", "toward ", "towards ", "tried ", "tries ", "truly ", "try ", "trying ", "ts ", "twice ", "two ", "un ",
"under ", "underneath ", "undoing ", "unfortunately ", "unless ", "unlike ", "unlikely ", "until ", "unto ", "up ", "upon ", "upwards ", "us ", "use ", "used ", "useful ", "uses ", "using ", "usually ", "v ", "value ", "various ", "versus ", "very ", "via ", "viz ", "vs ", "want ", "wants ", "was ", "wasnt ", "wasnt ", "wed ", "way ", "we ", "wed ", "welcome ", "well ", "well ", "well ", "were ", "werent ", "were ", "weve ", "went ", "were ", "were ", "werent ", "weve ", "what ", "whatever ", "whatll ", "whats ", "whatve ", "whatll ", "whatve ", "whats ", "when ", "whence ", "whenever ", "where ", "whereafter ", "whereas ", "whereby ", "wherein ", "wheres ", "whereupon ", "wherever ", "whether ", "which ", "whichever ", "while ", "whilst ", "whither ", "who ", "whod ", "whoever ", "whole ", "wholl ", "wholl ", "whom ", "whomever ", "whos ", "whos ", "whose ", "why ", "will ", "willing ", "wish ", "with ", "within ", "without ", "wonder ", "wont ", "would ", "wouldnt ", "yes ", "yet ", "you ", "youd ",
"youll ", "your ", "youre ", "yours ", "yourself ", "yourselves ", "youve ", "zero ",)
lr = Cells(Rows.Count, 2).End(xlUp).Row
b = Range("B2:C" & lr).Value
For i = LBound(b, 1) To UBound(b, 1)
h = Trim(b(i, 1))
If Right(h, 1) = "." Then h = Left(h, Len(h) - 1)
For ii = LBound(s) To UBound(s)
h = Replace(h, s(ii), "")
Next ii
b(i, 2) = h
Next i
Range("B2:C" & lr).Value = b
End Sub

**stanleydgromjr** · 05-23-2014, 06:47 PM

PiaHarrison,

Welcome to the Excel Forum.

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

In your NEW thread, include:

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

3. To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

After you do the above, then send me a Private Message with a link to your NEW thread, and, I will have a look.

**stanleydgromjr** · 05-24-2014, 05:56 AM

PiaHarrison,

I have created a new macro with a StopWords worksheet with 635 stop words, but, I need you to follow the instructions in my last reply #6.

**janeriany** · 06-20-2016, 02:54 AM

hi stanley, if i have my stoplist in other sheet, how i apply in your coding?

thanks beforee....

**FDibbins** · 06-20-2016, 11:32 PM

janeriany welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.