Forum Discussion

resham1985's avatar
resham1985
Former Employee
Jul 02, 2020
Solved

Find a word between words in excel

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 anot...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Jul 02, 2020

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.

resham1985's avatar
resham1985
Former Employee
Jul 02, 2020

mtarler - Thank you for the help, it worked.

 

I have attached one more file.  need your help.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 03, 2020

    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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 03, 2020

    resham1985 

    As variant

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

Resources