解析一个单元格并将关联值返回到另一个单元格

Question

在 Excel 2016 中，我尝试编写数组公式或 vba 函数来完成以下操作：

1.) 用户在单元格（附件示例中的单元格 D1）中输入一个字符串（项目名称）

2.) B 列中的单元格将根据 A 列的内容填充与项目名称字符串相关联的数字，每个数字用逗号分隔

A 列包含一些以逗号分隔的条目。每个条目包含一个项目名称和一个以%字符分隔的关联编号

例如，示例图像中的第一行包含三个条目：

Project1 1234
Project2 2345
Project3 5678

在单元格中输入为“Project1%1234,Project2%2345,Project3%5678”

我附上了一个示例图像，其中包含我的问题的空白版本，下面是用户输入时所需的结果Project1。我希望这能很好地说明问题，但如果不能，我可以澄清一下。

这是否可以通过数组公式来实现，或者是否需要 vba 中的用户定义函数？

Answer 1

是的，只需使用数组公式即可实现。不过这并不简单。

数组中输入（Ctrl++ Shift）Enter以下公式B2并复制粘贴/填充列：

{=TEXTJOIN(",",TRUE,LEFT(TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),IFERROR(FIND(",",TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))))-1,0)))}

---

美化后的公式如下：

{=
TEXTJOIN(
  ",",
  TRUE,
  LEFT(
    TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),
    IFERROR(
      FIND(
        ",",
        TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2)))
      )-1,
      0
    )
  )
)}

解释：

该公式的工作原理是将目标字符串中的项目名称加分隔符替换为一长串空格，然后将字符串“拆分”为一个块数组，每个块都以匹配项目的项目编号开头。这就是函数的输出TRIM(…)。

然后提取每个块的最左边的部分，直到（但不包括）第一个逗号，即项目编号。

最后将项目编号与TEXTJOIN()函数连接起来。

IFERROR的部分需要IFERROR(FIND(",",TRIM(…))-1,0)删除FIND()函数失败时返回的错误，而得到的空字符串将被忽略TEXTJOIN()。

笔记：

• 美化的公式确实可以起作用。

Answer 2

由于我没有及时看到 robinCTS 的答案，我最终编写了一个 vba 函数来解决这个问题。我想我会分享它，即使它有点笨重，以防它能帮助有同样问题的人。

Function ParseK(celltxt As String, userin As String) As String
Dim project_name As String
Dim number As String
Dim final_result As String
Dim string_array() As String
ReDim string_array(5)
Dim i As Variant
string_array = Split(celltxt, ",")
For Each i In string_array
    number = Right(i, Len(i) - InStr(i, "%"))
    project_name = Left(i, InStr(i, "%") - 1)
    If InStr(project_name, userin) > 0 Or project_name = userin Then final_result = final_result & number & ","

Next i
ParseK = final_result
End Function