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

Extract word or words within a cell full of text



Appreciate if you can help me with the formula to extract a word or words within a cell full of text. The word(s) I want to extract are anything with "EXPx" (x = number) and it can be more than 1 EXPx in that 1 cell. Also, in example cell 4, I want to extract "EXP<space>x" as well.

Example cell 1:

EXP1, EXP5, EXP8 - Text...text...text...text...

Example cell 2:

EXP2 - Expansion category 2


Example cell 3:

[EXP10] [EXP4] Text...text...text...text...text...

Example cell 4:

EXP 2, EXP 3 Text...text...text...text...text...




This is a UDF (User Defined Function) which has to be installed in a standard code module of your workbook (Alt+F11 to open VB Editor. Right-click on workbook in Project Explorer window on the left. Select Insert and then Module. Paste the code in the empty panel that opens on the right).

Function ExtractExp(Cell As Range) As String
    ' 01 May 2018
    Const Criterium As String = "exp"           ' not case sensitive
                                                ' but return all-caps
    Dim Fun As String                           ' function return value
    Dim Txt As String, Num As Integer
    Dim n As Integer
    Txt = Cell.Value
        n = InStr(1, Txt, Criterium, vbTextCompare)
        If n = 0 Then Exit Do
        Txt = Trim(Mid(Txt, n + Len(Criterium)))
        If IsNumeric(Left(Txt, 1)) Then _
           Fun = Fun & UCase(Criterium) & CStr(Val(Txt)) & ", "
    n = Len(Fun)
    If n Then Fun = Left(Fun, n - 2)
    ExtractExp = Fun
End Function

In your worksheet, call the function like any of Excel's built-in functions, for example


where A2 holds the text to be extracted from. Copy down as required.


Answer the Question

You must create an account to use the forum. Create an Account or Login