excel 使用公式函数或vba函数提取所需值

llmtgqce 于 2022-12-24 发布在其他

关注(0)|答案(1)|浏览(154)

如何用公式函数或vba函数提取所需值。
样表数据在下面，我想取下所需的值作为下面的结果，请给出解决方案。
谢啦，谢啦
| 项目|预期结果|
| - ------| - ------|
| 材料球形膏|CREM公司|
| 材料卡顿阿布（76）|阿布|
| 材料Katun Ima金色|金色|
| 材料BALLOTELY HIJAU|希饶|
| 军事卡通海军（04）|海军|
| 材料Katun IMA Turqis|图尔基斯|
| 材料丰田粉红色（09）|粉红色|
| 材料Katun IMA PB 08| PB 08|
| 材料丰田HIJAU（63）|希饶|
| 材料丰田fodu腻子|菩提|
| 材料KATUN WALLY克瑞普希豪薄荷（04）|希饶薄荷|
| 材料卡顿·T·阿辛（18）|T.阿辛|
| 材料 cordova FANTA（04）|芬达|
| 材料卡吞伊马温古图阿|温古图阿|
| 材料丰田工大（10A）|库布斯|
| 材料Katun IMA PB 08（A）|PB 08|
| 材料KATUN IMA PB 08（B）|PB 08|
| 材料丰田福都ABU SMA（02）|阿布SMA|
| 材料丰田FODU金（23）|金色|
| 材料IMA铂FANTA（10 A）|芬达|
| 材料IMA铂金粉红色色当（16）|粉红色色当|
| 材料Balotelli T. ASIN（31）|T.阿辛|

excel

来源：https://stackoverflow.com/questions/74863937/extract-the-desired-value-with-formula-function-or-vba-function

1条答案

按热度按时间

kgqe7b3p1#

有很多方法可以做到这一点，如果样本表是输入选项的极限，那么最简单的方法就是使用Case语句：

Function getcode(t as string)
    Select Case t
        Case "MATERIAL BALLOTELY CREM": code = "CREM"
        Case "MATERIAL KATUN ABU (76)": code = "ABU"
        Case "MATERIAL Katun Ima GOLD": code = "GOLD"
        Case "MATERIAL BALLOTELY HIJAU": code = "HIJAU"
        Case "MMATERIAL KATUN NAVY (04)": code = "NAVY"
        Case "MATERIAL KATUN IMA TURQIS": code = "TURQIS"
        Case "MATERIAL TOYOBO PINK (09)": code = "PINK"
        Case "MATERIAL KATUN IMA PB 08": code = "PB 08"
        Case "MATERIAL TOYOBO HIJAU (63)": code = "HIJAU"
        Case "MATERIAL TOYOBO FODU PUTIH": code = "PUTIH"
        Case "MATERIAL KATUN WALLY CREAP HIJAU MINT (04)": code = "HIJAU MINT"
        Case "MATERIAL KATUN T. ASIN (18)": code = "T. ASIN"
        Case "MATERIAL CORDOBA FANTA (04)": code = "FANTA"
        Case "MATERIAL Katun Ima Ungu Tua": code = "Ungu Tua"
        Case "MATERIAL TOYOBO KUBUS (10A)": code = "KUBUS"
        Case "MATERIAL KATUN IMA PB 08 ( A )": code = "PB 08"
        Case "MATERIAL KATUN IMA PB 08 ( B )": code = "PB 08"
        Case "MATERIAL TOYOBO FODU ABU SMA (02)": code = "ABU SMA"
        Case "MATERIAL TOYOBO FODU GOLD (23)": code = "GOLD"
        Case "MATERIAL IMA PLATINUM FANTA (10 A)": code = "FANTA"
        Case "MATERIAL IMA PLATINUM PINK SEDANG (16)": code = "PINK SEDANG"
        Case "MATERIAL BALOTELLI T. ASIN (31)": code = "T. ASIN"
        Case Else: code = "Code not found"
    End Select
    getcode = code
End Function

然而，另一种方法是删除不需要的单词，只留下所需的代码。如果列表要增长，更容易维护：

Function getcode(t As String)
    arrReplace = Array("MATERIAL", "BALLOTELY", "KATUN", "IMA", "TOYOBO", "WALLY", "CREAP", "FODU", "CORDOBA", "PLATINUM", "BALOTELLI")
    For Each strReplace In arrReplace
        t = Replace(t, strReplace & " ", "", , , vbTextCompare)
    Next
    t = Split(t, "(")(0)
    getcode = Trim(t)
End Function

赞(0）回复(0）举报 2022-12-24

我来回答

excel 使用公式函数或vba函数提取所需值

1条答案

相关问题

热门标签

最新问答