Identifying specific text from two conditions from a line of text

[Rav_Singh]

Would appreciate if someone may be able to assist me with my Excel problem.

I have been given an exhausting row of raw data (250,000+) from which I need to try and unravel key indicators. As such I require to extract specific text from two conditions in a string.

An example is below;

query=litigation&rm_within_search=&sort_index_dir_sortby=rel&sort_order_dir_sortby=ascending&rm_taxonomy_dir_loc_office=Hong+kong&resultsize=100

From this example I require to extract into another cell any text between _taxonomy_ and =. In this case I would like to identify and extract "dir_loc_office" into another cell.

The code I thought would work was below, however I believe as '=' is used multiple times in the text this may be why my formula didnt work.

=MID(A3,SEARCH("_taxonomy_",A3)+LEN("_taxonomy_"),SEARCH("=",A3)-SEARCH("_taxonomy_",A3)-LEN("_taxonomy_"))

As '=' is used different times depending on the text, I require a code that identifies the specific '=' after the first condition _taxonomy_ is used.

Alas this formula is beyond me, any help would be greatly appreciated.

 

[mumps]

Try:
=MID(A1,FIND("_taxonomy_",A1,1)+10,LEN(A1)-FIND("=",A1,FIND("_taxonomy_",A1,1))-10-1)

Change the A1 to suit your needs.

 

[Rav_Singh]

Firstly many thanks for replying to my first post on here. Greatly appreciated.

Your code works perfectly for that scenario however unfortunately for other lines of text, the result provides texts outside of that range.

For the example below if I apply that formula;

query=rita&rm_within_search=&sort_index_dir_sortby=rel&sort_order_dir_sortby=ascending&rm_taxonomy_dir_org_practice_area=Hong+kong+%28litigation%29&resultsize=100

The result I get is: dir_org_practice_area=Hong+kon

I wonder if there is a way I can get the text I require (in this case dir_org_practice_area) from the range specified without getting any additional text (in this case Hong+kon) regardless of the length of the string beyond the range specified.

Again many thanks for investing time in this.

 

[mumps]

Try: =MID(A1,FIND("_taxonomy_",A1,1)+10,FIND("=",A1,FIND("_taxonomy_",A1,1))-FIND("_taxonomy_",A1,1)-10)