excel中基于数组的字符串拆分

Question

我有像下面这样的excel单元格

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

我想抓住任何一列的道路，街道，车道，公路等，然后在另一栏相交的道路。因此，对于上面的单元格，我想要的输出是：

第1栏:威龙道

第2栏:布莱恩特博士

下面的代码将这两条道路合并成一列，但我想将它们分开，但在如何实现方面有一些困难。

=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)))

Answer 1

我试过检查你的公式，但不幸的是，它给我带来了一个错误。因此，我花了一些时间想出了以下解决方案：

例如，输入字符串位于A2单元格中，输出位于B2和C2单元格中。因此，公式如下：

对于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)))

对于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)))

注意，第二个公式依赖于第一个公式输出。