|
Extract a Word from a Sentence / Cell in Excel with this UDF - Allows for a User-Defined Delimiter
Extract whole words from a cell or sentence in Excel with this UDF. This allows you to specify which word from a cell you want to pull out of that cell and will then display that word. This is a very easy to use function that replaces having to use many complicated text manipulation functions in Excel. To use this user defined function (UDF) you only need to input the location of the cell with the text, the number of the word in the cell (counted from left to right) and the delimiter, if that is something other than a space.
Where to install the macro: Module
UDF to Extract a Word from a Sentence / Cell in Excel - Allows for a User-Defined Delimiter
Function GETTEXT(Text As Variant, N As Integer, Optional Delimiter As Variant) As String
If IsMissing(Delimiter) Then
Delimiter = " "
End If
GETTEXT = Split(Text, Delimiter)(N - 1)
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Have very question about extract Words, I write it to give for you. With function ElText() you can extract any word from Sentece in cells.
I am trying to extract a specific word that contains the letters I would like to identify from a cell that contains a long paragraph of text.
Example:
Cell A1: "I AM GOING HOME"
I would like to extract the word from this cell that contains the letters "OIN" and the result should be the word "GOING".
Thank u in advance for your help
I am importing information into a datasheet. The data comes into the cell as one long long sentence...let's say 200 words. The sentence is never the same and I need to extract a single word from this sentence. Comma delimited does not work as they did a HORRIBLE job designing the database...long story I have to do it this way.
The sentence is based upon Real Estate ....here is a small portion of what is in the sentence:
ppointment SHOWI: Call First SHOWI: Call LA SHOWI: Lockbox SHOWI: Owner Occupied STYLE: Colonial TYPE: Other-See
I need to be able to have the Style of the home sent to another cell on another sheet.
In this case...I need to search for the word STYLE: then have the next word copied to another cell. Therefore, the word "Colonial" will be copied to another sheet.
Please help as this is probably the last piece of the large puzzle I need on this task.
I have some data that contains a column which is Notes entered by CSR's. I would like to flag certain rows of data if the CSR puts in the notes that a customer has promised to fax something for instance. the Notes colum first contains the date stamp then the CSR name then then 1-2 sentences about the phone call. I would like to flag the cell if the word "Fax" appears anywhere in that cell. Can Excel do this with a basic formula?
Hi All,
I am after the formula to count the occurrence of, for instance the word 'the' in a sentence/paragraph that is contained in Cell A1. Cell B1 should return the quantity of times the word 'the' has been found in Cell A1.
Thanks in Advance,
Cheers,
Mark
Hello,
I am new to excel. The task is difficult for me now.
I need to find a particular word from a sentence with the use of wild cards & paste the same word (if found) in another cell.
Eg:
'Server d23bes60 is Down'
I have the above sentence in A1. I need to check if there is any word starting with 'd23' if found i need the out put as 'd23bes60'
there are lot of words which starts with D23 eg. d23bes60, d23bes23, d23bes54, d23bes91, d2311111.
So i need a formula to pick the word from a sentence which starts from D23....
Another Clause:
If there is no word starting with "D23" then the output should come as "0" or "NA"
Thanks for the reply. Its an urgent case.
Regards,
Harsha
Hi everyone, im try to build a macro that will find a word within a sentence that is in a cell. i.e.
A1="store number 566" I need the macro to select that cell (and stop). After that I will insert the rest of the code to copy and paste a larger area from that point, into another worksheet.
Thanks everyone in advance for any help.
sd
As title really.. I have got Excel to automatically add copies of tables that I need into a blank template of a report, but I would like it to also update a couple of bits of text in the middle of the doc which relate to the number of tables imported. I have no idea how to do any coding in Word so is this even possible? How would I go about it??
Hey folks,
I have a column of sentences and trying to move the last word of the sentence from the one cell and separating it into the adjacent cell.
The boy went to the park >> The boy went to the | park
Something like that. I am sure this is pretty easy. Thanks!
- Greg
Hi all board members,
I am a newbie and from Germany. I have some experience using vb-codes with outlook or excel but am not capable to program by myself.
I searched the whole net for a code I can use with Outlook to display the value of an Excel cell in my userdefined contacts form. I use Office XP. The value I want to show has the format of a number in Excel, or maybe the currency format.
The number I want to display in Outlook maybe changes caused by a Excel sum-formula. But the cell position itself will not change! (Its "AA41")
Is there someone who can help me?
Alex
|
|
