Find a word between words in excel

[resham1985]

Escalation : RES26181649 : Urgent Help Needed- I need RES26181649 in one seperate column 

Area-US, DC-Bangalore, RCA-Process-CSPO-TicketPrioritization- I need Bangalore in one column and US in another column 

[#2]

@resham1985 

Please populate the few possible entries of raw data in column A and mock up the desired output in column B and then upload the Excel file here so that it would be easier for us to see the pattern and expected output.

[#3 · Accepted solution]

@Subodh_Tiwari_sktneer - Please see the attached file for reference.

[#4]

@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

[#5]

@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})

[#6]

@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.

[#7]

@resham1985  Maybe I'm missing something but here are my 'low tech' solutions if it helps.  The 3 output formulas are:

col B

=MID(A2,SEARCH("RES",A2),12)

col G

=MID(F2,6,SEARCH(", DC",F2)-6)

col H

=MID(F2,11+LEN(G2),SEARCH(", RCA",F2)-11-LEN(G2))

you can also look at the attached.

[#8]

@mtarler - Thank you for the help, it worked.

I have attached one more file.  need your help.

[#9]

@resham1985 

As variant

=LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH("SC - ",A2)+4,"")," -",";"),
SEARCH(";",SUBSTITUTE(REPLACE(A2,1,SEARCH("SC - ",A2)+4,"")," -",";"))-1
)

[#10]

@resham1985  Here is the formula I used, which assumes the outcome you want follows "SC - " and is 3 characters or 2 characters followed by a ";".  In you sample I assume and hope that your expected outcome on line 5 is a typo.

=SUBSTITUTE(MID($A2,SEARCH("SC -",$A2)+5,3),";","")

Hope is works and make sure to like the helpful posts and mark best solution to 'close' the thread.