Extract word or words within a cell full of text

0

Hi, 

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

Text...text...text...text...text...

Example cell 3:

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

Example cell 4:

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

Answer
Discuss

Answers

0

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
    Do
        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)) & ", "
    Loop
    
    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

=ExtractExp($A2)

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

Discuss

Answer the Question

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