Split String Based on Array in excel-CodePudding

I have excel cells like the one below

WYONG RD BRYANT DR TUGGERAH 2259 CENTRAL COAST (LGA) NSW

I want to grab any sort of Road, Street, Drive, Highway etc. in one column and then the intersecting road in another column. SO for the above cell my desired output would be:

Column 1: WYONG RD

Column 2: Bryant DR

The code below brings the two roads into one column but I want to separate them but am having some difficulty on how to.

=INDEX(IFERROR(LEFT(H2,SEARCH({" DR"," HWY"," ST"," CRK"," BND"," LN"," AV"," AVE"," MTWY"," RD"," CT"},H2) 3),""),MATCH(MAX(LEN(IFERROR(LEFT(H2,SEARCH({" DR"," HWY"," ST"," CRK"," BND"," LN"," AV"," AVE"," MTWY"," RD"," CT"},H2) 2),""))),LEN(IFERROR(LEFT(H2,SEARCH({" DR"," HWY"," ST"," CRK"," BND"," LN"," AV"," AVE"," MTWY"," RD"," CT"},H2) 2),"")),0)))

CodePudding user response：

With MS365, you could use:

Formula in A3:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"),TRIM(FILTERXML("<t><s>"&TEXTJOIN(" ",,IF(MMULT(--(X={"DR";"HWY";"ST";"CRK";"BND";"LN";"AV";"AVE";"MTWY";"RD";"CT"}),SEQUENCE(11,,,0)),X&"</s><s>",X))&"</s></t>","//s")))

CodePudding user response：

I tried to check your formula but, unfortunately, it throws me an error. Thus, I spent some time and came up with the following solution:

Say, the input string is in the A2 cell and the outputs are in the B2 and C2 cells. Thus, the formulas can be as follows:

For B2:

=LEFT(A2,
MIN(IFERROR(SEARCH(" DR",  A2) 2,1000000),
    IFERROR(SEARCH(" HWY", A2) 3,1000000),
    IFERROR(SEARCH(" ST",  A2) 2,1000000),
    IFERROR(SEARCH(" CRK", A2) 3,1000000),
    IFERROR(SEARCH(" BND", A2) 3,1000000),
    IFERROR(SEARCH(" LN",  A2) 2,1000000),
    IFERROR(SEARCH(" AV",  A2) 2,1000000),
    IFERROR(SEARCH(" AVE", A2) 3,1000000),
    IFERROR(SEARCH(" MTWY",A2) 4,1000000),
    IFERROR(SEARCH(" RD",  A2) 2,1000000),
    IFERROR(SEARCH(" CT",  A2) 2,1000000)))

For C2:

=LEFT(SUBSTITUTE(A2,B2&" ",""),
MIN(IFERROR(SEARCH(" DR",  SUBSTITUTE(A2,B2&" ","")) 2,1000000),
    IFERROR(SEARCH(" HWY", SUBSTITUTE(A2,B2&" ","")) 3,1000000),
    IFERROR(SEARCH(" ST",  SUBSTITUTE(A2,B2&" ","")) 2,1000000),
    IFERROR(SEARCH(" CRK", SUBSTITUTE(A2,B2&" ","")) 3,1000000),
    IFERROR(SEARCH(" BND", SUBSTITUTE(A2,B2&" ","")) 3,1000000),
    IFERROR(SEARCH(" LN",  SUBSTITUTE(A2,B2&" ","")) 2,1000000),
    IFERROR(SEARCH(" AV",  SUBSTITUTE(A2,B2&" ","")) 2,1000000),
    IFERROR(SEARCH(" AVE", SUBSTITUTE(A2,B2&" ","")) 3,1000000),
    IFERROR(SEARCH(" MTWY",SUBSTITUTE(A2,B2&" ","")) 4,1000000),
    IFERROR(SEARCH(" RD",  SUBSTITUTE(A2,B2&" ","")) 2,1000000),
    IFERROR(SEARCH(" CT",  SUBSTITUTE(A2,B2&" ","")) 2,1000000)))

Note that the second formula relies on the first formula output.