使用VBA编写宏很简单:
Option Explicit
Sub ExtractOrganization()
Dim i As Long,countries As Variant,organizations As Variant,country As String
' take lists from cells and split them by ;
countries = Split(Cells(1,1),";")
organizations = Split(Cells(2,";")
country = "BE"
For i = LBound(countries) To UBound(countries)
If countries(i) = country Then Exit For
Next
MsgBox organizations(i)
End Sub
这是我的工作表设置:
,
如果您具有Excel 2013或更高版本,则可以使用FILTERXML方法,如@JvdV所示。对于所有版本,您都可以使用:
=LOOKUP(2,1/(TRIM(MID(SUBSTITUTE(";"&B2,";",REPT(" ",99)),ROW($A$1:$A$10)*99,99))=B4),TRIM(MID(SUBSTITUTE(";"&B3,99)))
哪里
单元格B2保留RO; BE; AU; CN; DE; UK; ES; HU; NL; SE; AT; EL; CZ
单元格B3拥有Org1; Org2; Org3 ..
单元格B4按住您要查找的密钥,例如RO。
假定源列表中的所有条目都是唯一的,并且要拆分10个条目。如果数量更多,则需要将ROW($A$1:$A$10)
更改为最大条目,以适合您的情况,例如ROW($A$1:$A$99)
等。
,
因为您已经用VBA标记了问题,所以可以使用UDF来获取值。
此UDF有三个参数:
- 国家单元格
- 组织单元格
-
您要寻找的国家
Function GET_ORG(ByVal vCountries As Range,ByVal vOrgs As Range,ByVal vThisCountry As String) As String
Dim Countries As Variant
Countries = Split(vCountries.Value,";")
Dim Orgs As Variant
Orgs = Split(vOrgs.Value,";")
Dim i As Byte
For i = LBound(Countries) To UBound(Countries) Step 1
If Countries(i) = vThisCountry Then Exit For
Next i
GET_ORG = Orgs(i)
Erase Countries
Erase Orgs
End Function
我进行的测试:
在单元格D2
中,我的公式是=GET_ORG(A1;A2;D1)
,
只是为了好玩。
可以使用基本的excel公式完成此操作,只要您真的不想再查看该公式并且不关心性能即可。
这是所有使用的公式
=FIND(";" & B4 &";";";" & B2 & ";")
=LEFT(B2;C6)
=LEN(C7)-LEN(SUBSTITUTE(C7;";";""))
=SUBSTITUTE(B3;";";"|";C8)
=FIND("|";C9)
=FIND(";";B3&";";C10+1)
=MID(C9;C10+1;C11-C10-1)
=MID(B3;FIND("|";SUBSTITUTE(B3;";";"|";LEN(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";")))-LEN(SUBSTITUTE(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";"));";";""))))+1;FIND(";";B3&";";FIND("|";SUBSTITUTE(B3;";";"|";LEN(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";")))-LEN(SUBSTITUTE(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";"));";";""))))+1)-FIND("|";SUBSTITUTE(B3;";";"|";LEN(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";")))-LEN(SUBSTITUTE(LEFT(B2; FIND(";" & B4 &";";";" & B2 & ";"));";";""))))-1)
,
使用 SPLIT,MATCH和INDEX Excel公式即可获取
FORMULA
=INDEX(SPLIT(B2,";"),MATCH(B3,SPLIT(B1,";")))
示例:
在B4单元格上应用上述公式
A B
1 COUNTRIES A;B;C;D;E
2 ORG Org1;Org2;Org3;Org4;Org5
3 Search CODE B
4 Result Org2 (=INDEX(SPLIT(B2,";"))))
本文链接:https://www.f2er.com/3167131.html