You can take several different approaches for extracting a particular word from a string of words in Microsoft Excel. Some approaches make use of Excel's built-in text formulas. Others involve using Excel's programming language, Visual Basic, to execute a function that performs the extraction. Whichever approach you take, knowing how to extract words from strings enables you to validate and process user input on your Excel spreadsheets.
Text to Columns
Video of the Day
Step 1
Open Excel, then type a sentence containing at least three words into any of the cells in the first worksheet. You'll break this sentence into its individual words.
Video of the Day
Step 2
Click the "Data" menu heading, then click the "Text to Columns" button. This function splits up text whose words are separated by a character you specify.
Step 3
Click the "Delimited" option button in the dialog box that appears, then click "Next."
Step 4
Click the "Space" check box, then click the "Finish" button. Excel will split the sentence you wrote into separate columns. Each column will contain one word from the sentence.
Step 5
Type the function, minus the quotation marks, "=index([ARRAY],1,[NUMBER OF WORD TO EXTRACT])" into a cell below the string of separated words. Replace the term "ARRAY" with the range containing the string of words. For example, if the words occupy cells "A1" to "C1," enter the text "A1:C1" in place of the "ARRAY" text. Replace the text "NUMBER OF WORD TO EXTRACT" with the number of the word you want to extract from the sentence. For example, if you want to extract the third word from the range "A1:C1," type "3" for the last argument.
Step 6
Press "Enter" to complete entering the "Index" function. Excel will display the word you wanted to extract from the sentence.
Use VB
Step 1
Open a new Excel spreadsheet, then type any sequence of three or more words into cell F4. You'll use a short Visual Basic program to extract a word from this sentence.
Step 2
Type the number of the word you want to extract from the sentence in cell F5.
Step 3
Click the "Developer" menu heading, then click the "Visual Basic" button. The programming environment for Excel will open.
Step 4
Click the "Insert" menu heading, then click "Module."
Step 5
Paste the following program into the window that appears. The heart of this subroutine is the "split" function, which separates a sentence into its individual words, as the "Text to Columns" command does.
Sub Macro1() Dim ar, str1, n str1 = Range("F4") n = Range("F5") - 1 ar = Split(str1, " ") MsgBox "Word number " & n + 1 & " is " & ar(n) End Sub
Step 6
Click the "Excel" icon on the Windows taskbar to return to the Excel spreadsheet.
Step 7
Click the "Macros" button of the "Developer" tab, then double-click the "Macro1" function. A message box will appear indicating the word your program extracted from the sentence you typed in step 1.