Make new variables based on the information in brackets in current column

[Paul Lavery]

Hi

I am looking to make new variables with more specific information based on what is currently contained in a column named 'AdvisorsToTarget" (see data example below). The column as it is contains up to six pieces of information, such as 'Legal Advisor', 'Financial Advisor', 'Accountant', 'Information Agent', 'Due Diligence Provider', 'Transfer Agent/Registrar' and 'Placement Agent'.

What I want is six new columns each with this specific information, as opposed to all the information being contained together in one variable/column. Each separate piece of information is separated by a semi-colon, and the detail of the information is in brackets. Note that some rows contain more than one Financial Advisor, for example (see row 11 for example).

I have attached an example piece of data below - I hope this makes sense and I appreciate any suggestions on how this may be done, if it is indeed feasible.

Thanks
Paul

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Date byte Target str1 Value byte(Buyer Sellers) str184 AdvisorsToTarget
21539  1 "-"  1  1 "GCA Altium Limited (Financial Advisor); CMS Germany (Legal Advisor)"                                                                                                                    
21539  2 "-"  2  2 "Macfarlanes LLP (Legal Advisor); Spencer House Partners LLP (Financial Advisor)"                                                                                                        
21538  3 "-"  3  3 "Skadden, Arps, Slate, Meagher & Flom LLP (Legal Advisor); Travers Smith LLP (Legal Advisor); KPMG Corporate Finance LLP (Financial Advisor)"                                            
21538  4 "-"  4  4 "Rothschild & Co SCA (ENXTPA:ROTH) (Placement Agent)"                                                                                                                                    
21537  5 "-"  5  5 "Addleshaw Goddard LLP (Legal Advisor); DC Advisory (Financial Advisor); Jamieson Corporate Finance LLP (Financial Advisor)"                                                              
21536  6 "-"  6  6 "Orrick, Herrington & Sutcliffe LLP (Legal Advisor); Goldman Sachs & Co. LLC (Placement Agent)"                                                                                          
21536  7 "-"  7  7 "Gateley (Holdings) Plc (AIM:GTLY) (Legal Advisor); Clearwater International (Financial Advisor)"                                                                                        
21536  8 "-"  8  8 "Houlihan Lokey, Inc. (NYSE:HLI) (Financial Advisor)"                                                                                                                                    
21530  9 "-"  9  9 "Clearwater International (Placement Agent)"                                                                                                                                              
21530 10 "-" 10 10 "Simmons & Company International Limited (Financial Advisor)"                                                                                                                            
21528 11 "-" 11 11 "Kirkland & Ellis LLP (Legal Advisor); Innisfree M&A Inc. (Information Agent); Morgan Stanley & Co. LLC (Financial Advisor); UBS Securities LLC (Financial Advisor)"                      
21528 12 "-" 12 12 "TLT LLP (Legal Advisor); WH Ireland Limited (Financial Advisor); WH Ireland Limited (Placement Agent)"                                                                                  
21525 13 "-" 13 13 "HMT LLP (Financial Advisor)"                                                                                                                                                            
21525 14 "-" 14 14 "Park Place Corporate Finance Limited (Financial Advisor)"                                                                                                                                
21522 15 "-" 15 15 "Burges Salmon (Legal Advisor); Deloitte PCS Limited (Accountant); Blake Morgan LLP (Legal Advisor); Ernst & Young Services (uk) Ltd (Financial Advisor)"                                
21522 16 "-" 16 16 "Burness Paull & Williamsons (Legal Advisor); BDO International Limited (Financial Advisor)"                                                                                              
21510 17 "-" 17 17 "Addleshaw Goddard LLP (Legal Advisor); Liberty Corporate Finance Associates LLP (Financial Advisor)"                                                                                    
21508 18 "-" 18 18 "Moelis & Company (NYSE:MC) (Financial Advisor)"                                                                                                                                          
21508 19 "-" 19 19 "RG Financial Limited (Financial Advisor)"                                                                                                                                                
21507 20 "-" 20 20 "Turner Parkinson LLP (Legal Advisor); Clearwater CFA (Financial Advisor)"                                                                                                                
21500 21 "-" 21 21 "Ernst & Young UK, Investment Banking Arm (Financial Advisor)"                                                                                                                            
21496 22 "-" 22 22 "Freeths LLP (Legal Advisor)"                                                                                                                                                            
21496 23 "-" 23 23 "Charles Russell Speechlys LLP (Legal Advisor)"                                                                                                                                          
21495 24 "-" 24 24 "Liontree Advisors UK LLP (Financial Advisor)"                                                                                                                                            
21494 25 "-" 25 25 "BCMS Corporate Limited (Financial Advisor)"                                                                                                                                              
21490 26 "-" 26 26 "BDO LLP (Accountant)"                                                                                                                                                                    
21485 27 "-" 27 27 "PALAZZO Investment Bankers (Placement Agent)"                                                                                                                                            
21483 28 "-" 28 28 "Ward Hadaway LLP (Legal Advisor); HURST Corporate Finance (Financial Advisor)"                                                                                                          
21479 29 "-" 29 29 "BDO LLP, Financial Advisory Arm (Placement Agent)"                                                                                                                                      
21474 30 "-" 30 30 "Shoosmiths LLP (Legal Advisor); Houlihan Lokey (Europe) Limited (Financial Advisor)"                                                                                                    
21473 31 "-" 31 31 "Houlihan Lokey (Europe) Limited (Financial Advisor)"                                                                                                                                    
21472 32 "-" 32 32 "Lazard & Co., Limited (Financial Advisor); Wyvern Partners Limited (Financial Advisor); Morrison & Foerster (UK) LLP (Legal Advisor)"                                                    
21472 33 "-" 33 33 "GCA Altium Limited (Financial Advisor); Addleshaw Goddard LLP (Legal Advisor); PricewaterhouseCoopers LLP (Due Diligence Provider); CIL Management Consultants (Due Diligence Provider)"
21469 34 "-" 34 34 "DLA Piper UK LLP (Legal Advisor)"                                                                                                                                                        
21469 35 "-" 35 35 "Jamieson Corporate Finance LLP (Financial Advisor); Osborne Clarke LLP (Legal Advisor)"                                                                                                  
21466 36 "-" 36 36 "Allen & Overy LLP (Legal Advisor); DLA Piper UK LLP (Legal Advisor)"                                                                                                                    
21466 37 "-" 37 37 "JMW Solicitors Ltd. (Legal Advisor); Spinnaker Corporate Finance (Financial Advisor)"                                                                                                    
21466 38 "-" 38 38 "Walker Morris LLP (Legal Advisor); DSW Bridge Houghton Forensic (Financial Advisor)"                                                                                                    
21465 39 "-" 39 39 "Birketts LLP (Legal Advisor)"                                                                                                                                                            
21458 40 "-" 40 40 "Silverpeak LLP (Financial Advisor)"                                                                                                                                                      
21458 41 "-" 41 41 "Spayne Lindsay & Co. LLP (Financial Advisor)"                                                                                                                                            
21447 42 "-" 42 42 "Gateley (Holdings) Plc (AIM:GTLY) (Legal Advisor); Alantra USA (Placement Agent)"                                                                                                        
21447 43 "-" 43 43 "KPMG Corporate Finance LLP (Financial Advisor); DLA Piper UK LLP (Legal Advisor)"                                                                                                        
21446 44 "-" 44 44 "Osborne Clarke LLP (Legal Advisor)"                                                                                                                                                      
21445 45 "-" 45 45 "EMW Law LLP (Legal Advisor); Nexus Corporate Finance LLP (Financial Advisor)"                                                                                                            
21445 46 "-" 46 46 "Pinsent Masons (Legal Advisor); Grant Thornton Corporate Finance (Financial Advisor)"                                                                                                    
21438 47 "-" 47 47 "Addleshaw Goddard LLP (Legal Advisor); Shore Capital and Corporate Limited (Financial Advisor)"                                                                                          
21433 48 "-" 48 48 "BDO LLP (Accountant); Houlihan Lokey Capital, Inc. (Financial Advisor)"                                                                                                                  
21431 49 "-" 49 49 "N M Rothschild & Sons Limited (Financial Advisor); DLA Piper UK LLP (Legal Advisor)"                                                                                                    
21430 50 "-" 50 50 "Macfarlanes LLP (Legal Advisor); Jamieson Corporate Finance LLP (Financial Advisor)"                                                                                                    
21428 51 "-" 51 51 "KPMG LLP (United Kingdom), Investment Banking Arm (Financial Advisor)"                                                                                                                  
21427 52 "-" 52 52 "BDO Corporate Finance LLP (Financial Advisor)"                                                                                                                                          
21424 53 "-" 53 53 "Higgs & Sons (Legal Advisor); PricewaterhouseCoopers LLP (Accountant); Spencer Gardner Dickins (Accountant)"                                                                            
21420 54 "-" 54 54 "BDO LLP (Accountant); The Jordan, Edmiston Group, Inc. (Financial Advisor); CMS Cameron McKenna Nabarro Olswang LLP (Legal Advisor)"                                                    
21419 55 "-" 55 55 "Gowling WLG (UK) LLP (Legal Advisor); Winchester Technology Advisors Limited (Placement Agent)"                                                                                          
21418 56 "-" 56 56 "PwC Corporate Finance LLP (Financial Advisor)"                                                                                                                                          
21412 57 "-" 57 57 "Pinsent Masons (Legal Advisor); Numis Securities Limited (Financial Advisor); Nplus1 Singer Advisory LLP (Financial Advisor)"                                                            
21410 58 "-" 58 58 "Kuit Steinart Levy LLP (Legal Advisor)"                                                                                                                                                  
21409 59 "-" 59 59 "Slaughter and May (Legal Advisor); Deutsche Bank AG, London Branch (Financial Advisor); Equiniti Limited (Transfer Agent/Registrar)"                                                    
21408 60 "-" 60 60 "CMS Cameron McKenna (Legal Advisor)"                                                                                                                                                    
21406 61 "-" 61 61 "Simpson Thacher & Bartlett LLP (Legal Advisor)"                                                                                                                                          
21403 62 "-" 62 62 "BDO LLP (Accountant); N M Rothschild & Sons Limited (Financial Advisor); Travers Smith LLP (Legal Advisor); CMS Germany (Legal Advisor); Panmure Gordon (UK) Limited (Financial Advisor)"
21398 63 "-" 63 63 "Jenson Solutions Limited (Accountant)"                                                                                                                                                  
21398 64 "-" 64 64 "Cavendish Corporate Finance LLP (Financial Advisor); Armstrong Watson Ltd (Accountant); PricewaterhouseCoopers LLP (Accountant)"                                                        
21398 65 "-" 65 65 "Allen & Overy LLP (Legal Advisor); Reynolds Porter Chamberlain (Legal Advisor); KPMG LLP (United Kingdom) (Accountant); Houlihan Lokey (Europe) Limited (Financial Advisor)"            
21397 66 "-" 66 66 "Browne Jacobson LLP (Legal Advisor); BDO Corporate Finance LLP (Financial Advisor)"                                                                                                      
21396 67 "-" 67 67 "PricewaterhouseCoopers International Limited (Accountant); Park Place Corporate Finance Limited (Financial Advisor); Squire Patton Boggs (UK) LLP (Legal Advisor)"                      
21396 68 "-" 68 68 "Osborne Clarke LLP (Legal Advisor)"                                                                                                                                                      
21395 69 "-" 69 69 "TLT LLP (Legal Advisor)"                                                                                                                                                                
21391 70 "-" 70 70 "N M Rothschild & Sons Limited (Financial Advisor)"                                                                                                                                      
21389 71 "-" 71 71 "Ernst & Young Global Limited, Corporate Finance (Financial Advisor)"                                                                                                                    
21389 72 "-" 72 72 "CMS Cameron McKenna Nabarro Olswang LLP (Legal Advisor)"                                                                                                                                
21388 73 "-" 73 73 "Arrowpoint Advisory LLP (Financial Advisor); James Cowper LLP (Accountant); Pitmans LLP (Legal Advisor)"                                                                                
end
format %td Date

[Ali Atia]

One solution, which generates seven dummy variables which are 1 if the string appears and 0 if it doesn't:

gen finadvisor = strpos(AdvisorsToTarget, "Financial Advisor")!=0
gen legaladvisor = strpos(AdvisorsToTarget, "Legal Advisor")!=0
gen accountant = strpos(AdvisorsToTarget, "Accountant")!=0
gen infoagent = strpos(AdvisorsToTarget, "Information Agent")!=0
gen diligence = strpos(AdvisorsToTarget, "Due Diligence Provider")!=0
gen transferagent = strpos(AdvisorsToTarget, "Transfer Agent/Registrar")!=0
gen placementagent = strpos(AdvisorsToTarget, "Placement Agent")!=0

Or - more efficiently:

foreach x in "Legal Advisor" "Financial Advisor" "Accountant" "Information Agent" "Due Diligence Provider" "Transfer Agent/Registrar" "Placement Agent"{
    local y = strtoname("`x'")
    gen `y' = strpos(AdvisorsToTarget, "`x'")!=0
}

[Paul Lavery]

Hi Ali

Thanks - this is certainly a start as it shows me which rows have the corresponding data and which don't.

However what I am really looking for is to have the name of each type of advisor in new columns. For example a new column called 'Legal Advisor' with the the name(s) of the legal advisor for each row in it (I hope that makes sense)

[Andrew Musau]

split AdvisorsToTarget, p(;) g(a)
drop AdvisorsToTarget
reshape long a, i( Date Target Value Buyer Sellers) j(which)
drop if missing(a)
replace which=.
local i 1
foreach occupation in  "Legal Advisor" "Financial Advisor" Accountant ///
"Information Agent" "Due Diligence Provider" "Transfer Agent/Registrar" ///
"Placement Agent" {
    replace which=`i' if regexm(a, "`occupation'")
    local ++i
}
bys Target which: replace which= which*_n*10 if _n>1
reshape wide a, i(Date Target Value Buyer Sellers) j(which)
rename (a1 a20) (legal1 legal2)
rename (a2 a40) (financial1 financial2)
rename (a3 a60) (accountant1 accountant2)
rename (a5 a100) (due_dil1 due_dil2)
rename (a4 a6 a7) (information1 transfer1 placement1)
reshape long legal financial accountant information due_dil transfer ///
placement , i(Date Target Value Buyer Sellers) j(which)
foreach var of varlist legal -placement{
    replace `var'= ustrregexra(`var', "\(.+?\)", "", .)
}

How I deal with multiple industries within targets is simple. If legal is coded which=1, the second will be which =2*10=20, 3rd which=3*10=30, and so on. This works for the example, but you will need something more intricate as which originally coded 3 has the second observation coded which = 3*2*10=60, which will coincide with the 6th observation of which=1 (6*1*10=60). The result is too large to display, so I post the dataex output.

Res.:

. dataex Target which legal - placement

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(Target which) str69(legal financial accountant due_dil information transfer placement)
73 1 " Pitmans LLP "                             "Arrowpoint Advisory LLP "                         " James Cowper LLP "                            ""                             "" ""                   ""                                        
73 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
71 1 ""                                          "Ernst & Young Global Limited, Corporate Finance " ""                                              ""                             "" ""                   ""                                        
71 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
72 1 "CMS Cameron McKenna Nabarro Olswang LLP "  ""                                                 ""                                              ""                             "" ""                   ""                                        
72 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
70 1 ""                                          "N M Rothschild & Sons Limited "                   ""                                              ""                             "" ""                   ""                                        
70 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
69 1 "TLT LLP "                                  ""                                                 ""                                              ""                             "" ""                   ""                                        
69 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
67 1 " Squire Patton Boggs  LLP "                " Park Place Corporate Finance Limited "           "PricewaterhouseCoopers International Limited " ""                             "" ""                   ""                                        
67 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
68 1 "Osborne Clarke LLP "                       ""                                                 ""                                              ""                             "" ""                   ""                                        
68 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
66 1 "Browne Jacobson LLP "                      " BDO Corporate Finance LLP "                      ""                                              ""                             "" ""                   ""                                        
66 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
63 1 ""                                          ""                                                 "Jenson Solutions Limited "                     ""                             "" ""                   ""                                        
63 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
64 1 ""                                          "Cavendish Corporate Finance LLP "                 " PricewaterhouseCoopers LLP "                  ""                             "" ""                   ""                                        
64 2 ""                                          ""                                                 " Armstrong Watson Ltd "                        ""                             "" ""                   ""                                        
65 1 " Reynolds Porter Chamberlain "             " Houlihan Lokey  Limited "                        " KPMG LLP  "                                   ""                             "" ""                   ""                                        
65 2 "Allen & Overy LLP "                        ""                                                 ""                                              ""                             "" ""                   ""                                        
62 1 " CMS Germany "                             " N M Rothschild & Sons Limited "                  "BDO LLP "                                      ""                             "" ""                   ""                                        
62 2 " Travers Smith LLP "                       " Panmure Gordon  Limited "                        ""                                              ""                             "" ""                   ""                                        
61 1 "Simpson Thacher & Bartlett LLP "           ""                                                 ""                                              ""                             "" ""                   ""                                        
61 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
60 1 "CMS Cameron McKenna "                      ""                                                 ""                                              ""                             "" ""                   ""                                        
60 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
59 1 "Slaughter and May "                        " Deutsche Bank AG, London Branch "                ""                                              ""                             "" " Equiniti Limited " ""                                        
59 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
58 1 "Kuit Steinart Levy LLP "                   ""                                                 ""                                              ""                             "" ""                   ""                                        
58 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
57 1 "Pinsent Masons "                           " Nplus1 Singer Advisory LLP "                     ""                                              ""                             "" ""                   ""                                        
57 2 ""                                          " Numis Securities Limited "                       ""                                              ""                             "" ""                   ""                                        
56 1 ""                                          "PwC Corporate Finance LLP "                       ""                                              ""                             "" ""                   ""                                        
56 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
55 1 "Gowling WLG  LLP "                         ""                                                 ""                                              ""                             "" ""                   " Winchester Technology Advisors Limited "
55 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
54 1 " CMS Cameron McKenna Nabarro Olswang LLP " " The Jordan, Edmiston Group, Inc. "               "BDO LLP "                                      ""                             "" ""                   ""                                        
54 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
53 1 "Higgs & Sons "                             ""                                                 " Spencer Gardner Dickins "                     ""                             "" ""                   ""                                        
53 2 ""                                          ""                                                 " PricewaterhouseCoopers LLP "                  ""                             "" ""                   ""                                        
52 1 ""                                          "BDO Corporate Finance LLP "                       ""                                              ""                             "" ""                   ""                                        
52 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
51 1 ""                                          "KPMG LLP , Investment Banking Arm "               ""                                              ""                             "" ""                   ""                                        
51 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
50 1 "Macfarlanes LLP "                          " Jamieson Corporate Finance LLP "                 ""                                              ""                             "" ""                   ""                                        
50 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
49 1 " DLA Piper UK LLP "                        "N M Rothschild & Sons Limited "                   ""                                              ""                             "" ""                   ""                                        
49 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
48 1 ""                                          " Houlihan Lokey Capital, Inc. "                   "BDO LLP "                                      ""                             "" ""                   ""                                        
48 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
47 1 "Addleshaw Goddard LLP "                    " Shore Capital and Corporate Limited "            ""                                              ""                             "" ""                   ""                                        
47 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
45 1 "EMW Law LLP "                              " Nexus Corporate Finance LLP "                    ""                                              ""                             "" ""                   ""                                        
45 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
46 1 "Pinsent Masons "                           " Grant Thornton Corporate Finance "               ""                                              ""                             "" ""                   ""                                        
46 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
44 1 "Osborne Clarke LLP "                       ""                                                 ""                                              ""                             "" ""                   ""                                        
44 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
42 1 "Gateley  Plc  "                            ""                                                 ""                                              ""                             "" ""                   " Alantra USA "                          
42 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
43 1 " DLA Piper UK LLP "                        "KPMG Corporate Finance LLP "                      ""                                              ""                             "" ""                   ""                                        
43 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
40 1 ""                                          "Silverpeak LLP "                                  ""                                              ""                             "" ""                   ""                                        
40 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
41 1 ""                                          "Spayne Lindsay & Co. LLP "                        ""                                              ""                             "" ""                   ""                                        
41 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
39 1 "Birketts LLP "                             ""                                                 ""                                              ""                             "" ""                   ""                                        
39 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
36 1 "Allen & Overy LLP "                        ""                                                 ""                                              ""                             "" ""                   ""                                        
36 2 " DLA Piper UK LLP "                        ""                                                 ""                                              ""                             "" ""                   ""                                        
37 1 "JMW Solicitors Ltd. "                      " Spinnaker Corporate Finance "                    ""                                              ""                             "" ""                   ""                                        
37 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
38 1 "Walker Morris LLP "                        " DSW Bridge Houghton Forensic "                   ""                                              ""                             "" ""                   ""                                        
38 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
34 1 "DLA Piper UK LLP "                         ""                                                 ""                                              ""                             "" ""                   ""                                        
34 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
35 1 " Osborne Clarke LLP "                      "Jamieson Corporate Finance LLP "                  ""                                              ""                             "" ""                   ""                                        
35 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
32 1 " Morrison & Foerster  LLP "                " Wyvern Partners Limited "                        ""                                              ""                             "" ""                   ""                                        
32 2 ""                                          "Lazard & Co., Limited "                           ""                                              ""                             "" ""                   ""                                        
33 1 " Addleshaw Goddard LLP "                   "GCA Altium Limited "                              ""                                              " PricewaterhouseCoopers LLP " "" ""                   ""                                        
33 2 ""                                          ""                                                 ""                                              " CIL Management Consultants " "" ""                   ""                                        
31 1 ""                                          "Houlihan Lokey  Limited "                         ""                                              ""                             "" ""                   ""                                        
31 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
30 1 "Shoosmiths LLP "                           " Houlihan Lokey  Limited "                        ""                                              ""                             "" ""                   ""                                        
30 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
29 1 ""                                          ""                                                 ""                                              ""                             "" ""                   "BDO LLP, Financial Advisory Arm "        
29 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
28 1 "Ward Hadaway LLP "                         " HURST Corporate Finance "                        ""                                              ""                             "" ""                   ""                                        
28 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
27 1 ""                                          ""                                                 ""                                              ""                             "" ""                   "PALAZZO Investment Bankers "            
27 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
26 1 ""                                          ""                                                 "BDO LLP "                                      ""                             "" ""                   ""                                        
26 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
25 1 ""                                          "BCMS Corporate Limited "                          ""                                              ""                             "" ""                   ""                                        
25 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
24 1 ""                                          "Liontree Advisors UK LLP "                        ""                                              ""                             "" ""                   ""                                        
24 2 ""                                          ""                                                 ""                                              ""                             "" ""                   ""                                        
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 146 observations
Use the count() option to list more

[Ali Atia]

Try this (requires -ereplace-, written by Chris Larkin and Nick Cox; available on SSC):

gen newAd = ";" + AdvisorsToTarget
quietly{
foreach x in "Legal Advisor" "Financial Advisor" "Accountant" "Information Agent" "Due Diligence Provider" "Transfer Agent/Registrar" "Placement Agent"{
    local y = strtoname("`x'")
    gen dummy_`y' = strpos(AdvisorsToTarget, "`x'")!=0
    local noad`y' = round(length(AdvisorsToTarget) - length(subinstr(AdvisorsToTarget, "`x'", "", .)) / length("`x'"))
    egen name_`y' = ends(newAd), punct("(`x'") head trim
    ereplace name_`y' = ends(name_`y'), punct(";") last
    egen newAd`y'1 = ends(newAd), punct("(`x'") tail trim
    forvalues i = 2/`=`noad`y'''{
        egen name_`y'`i' = ends(newAd`y'`=`i'-1'), punct("(`x'") head trim
        ereplace name_`y'`i' = ends(name_`y'`i'), punct(";") last
        replace name_`y' = "" if !dummy_`y'
        replace name_`y'`i' = "" if strpos(name_`y'`i', "(")!=0 | strpos(name_`y'`i', ")")!=0
        replace name_`y'`i' = ";"+name_`y'`i' if name_`y'`i'!=""
        egen newAd`y'`i' = ends(newAd`y'`=`i'-1'), punct("(`x'") tail trim
    }
    egen wanted`y' = concat(name_`y'*)
    drop name_`y'* dummy_`y'
    order wanted`y'
}
}
drop newAd*

edit: crossed with #4

[Paul Lavery]

That's really helpful - thanks a lot folks!