Find a word between words in excel

resham1985 

Attached formula (added in J2:K3) may suffice, provided raw data field will always include "Area-", "DC-" & "RCA-" in that order...

It's basically a MID formula where the start position and character counts are based on the above mentioned constants, in the string.

Hope this helps.

resham1985 

In assumption that texts have the same structure, formula solution could be

=INDEX(FILTERXML("<r><n>" & SUBSTITUTE(A2," : ","</n><n>") & "</n></r>",  "//n"),2)

and

=INDEX(
   RIGHT(
       TRANSPOSE(FILTERXML("<r><n>"&SUBSTITUTE(F2,",","</n><n>")&"</n></r>","//n")),
       LEN(TRANSPOSE(FILTERXML("<r><n>"&SUBSTITUTE(F2,",","</n><n>")&"</n></r>","//n")))-
       SEARCH("-",TRANSPOSE(FILTERXML("<r><n>"&SUBSTITUTE(F2,",","</n><n>")&"</n></r>","//n")))
   ),
{1,2})

resham1985 

resham1985 

If you are open to a VBA solution, you may create your own User Defined Function to get the desired sub string from the raw string.

This solution will only work if the desired sub string is preceded with either RES, Area or DC and these keywords are used in the User Defined Function given below.

Once the UDF is placed on a Standard Module, you may use it like a Regular Excel Function on the Worksheet.

You may use this UDF like...

=getSubString(A2,"res")

=getSubString(F2,"area")

=getSubString(F2,"dc")

To place this UDF into your workbook, follow these steps...

1. Open your Workbook and press Alt+F11 to open VB Editor.
2. On VB Editor's Ribbon --> Insert --> choose Module and this will insert a New Module.
3. Copy the code given below and paste it into the opened code window of Module1.
4. Close the VB Editor and Save your Workbook AS Excel Macro-Enabled Workbook.

Please refer to the attached for more details.

The UDF is placed in yellow cells on the Sheet1.

UDF:

Function getSubString(ByVal str As String, crit As String) As String
Dim Matches As Object

With CreateObject("VBScript.RegExp")
    .Global = True
    If LCase(crit) = "res" Then
        .Pattern = "(RES\d+)"
    ElseIf LCase(crit) = "area" Then
        .Pattern = "Area-([A-z\s]+)"
    ElseIf LCase(crit) = "dc" Then
        .Pattern = "DC-([A-z\s]+)"
    Else
        Exit Function
    End If
    
    If .Test(str) Then
        Set Matches = .Execute(str)
        getSubString = Matches(0).SubMatches(0)
    End If
End With
End Function