Simple Excel Function to Extract a Word or Text from a Cell

Add to Favorites
Author: | Edits: don

Excel function that makes it easy to extract a word or text from a cell in Excel.

This is a single function!

It does NOT require a complex formula or nested functions or anything like that.

To use this function, we first need to create it using a UDF, User Defined Function. You can learn about this kind of function here: User Defined Functions in Excel

Sections:

Syntax

Create the Function and Install It

Function Examples

Notes

Syntax

(UDF code for this function listed in the next section)


=Get_Word(input_data, delimiter, word)
Argument Description
Input_data The cell or text from which you want to get a word or text.
Delimiter

The character that separates your text. In a sentence, this would be a space. It could be a dash, for things like part numbers, or, really, anything that separates your text into individual sections or elements. If the value in here is not a number, it must be surrounded with double quotation marks "".

Word

This is a number. The number is which word or individual section you want to get from the text. 1 means get the first word/section in the cell; 2 means get the second word/section in the cell, etc.

Create the Function and Install It

(If you do not know about UDFs, please visit the link at the top of this tutorial. Here, I assume you know what they are.)

Here is the UDF code:


Function Get_Word(input_data, delimiter, word)

    'make input number more friendly for users
    word = word - 1

    input_data = Split(input_data, delimiter)

    Get_Word = input_data(word)

End Function

I only included one comment above because the rest of the code is self-explanatory, if you know VBA, and, if you don't know VBA, it doesn't really matter, because it works already.

Copy/past the above code into a module in the VBA Editor (Alt + F11 and then Insert > Module) and then go back to Excel and we can begin to use it.

Function Examples

Get a Word

Here is our sample:

ac9b05f30caf29276c97c1e2cb16d9e3.png

Let's get is from this sentence using our new function.

05f4959c5d82addc4c24c42032e710b7.png

Notice that the second argument is a blank space surrounded by double quotation marks. This is because a space is what separates the individual parts of text in cell A1.

The third argument is 2, which says that we want to get the second word from that cell, which will be is.

Result:

ebc800a3ef7210108142eb02fe55da4e.png

Get a Part Number

This follows the same pattern as above.

Sample data:

e7678b56c75e0a207b53d7cab265fd62.png

I want to get the number 234 from this cell.

13d8afa916392c41e7ea5ecd91e8041d.png

Here, the delimiter is a dash and, of course, it needs to be surrounded with double quotation marks.

The 2 in the third argument means to get the second word or element from the cell. The function breaks the original cell into parts based on the delimiter that you enter and you just need to tell Excel which part you want to get back.

Result:

553addcf10be218dc6e71ada6ec7484a.png

Notes

This is a great UDF. I love how simple it is to use and how easily it does something you expect Excel to do.

If you didn't understand everything in this tutorial, please read the UDF tutorial that is linked to at the top of this tutorial. That will get you up-to-speed on how these types of custom functions work in Excel.

Make sure to download the sample file attached to this tutorial so you can test it out and get the code.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Simple Excel Function to Combine Text with a Separator
Tutorial: Excel function that makes combining text very easy. This function is simpler and better th...
Simple Excel Function to Combine Values in All Versions of Excel - UDF
: Excel function that combines values from multiple cells or inputs using a delimiter - work...
Formulas to Remove First or Last Character from a Cell in Excel
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Logical Comparison Operators in Excel - How to Compare Things
Tutorial: (Video tutorial's page: Compare Values in Excel - Beginner to Advanced) Logical compariso...
Get the First Word from a Cell in Excel
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...
SUBSTITUTE() Function - Change Specific Text within a Cell in Excel
Tutorial: Change or replace text in a cell with other text - you can replace a single character, num...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course