Extract the desired value with formula function or vba function-CodePudding

How to extract the desired value with formula function or vba function.

The sample table data is below I want to retrieve the desired value as the result below. Please have a solution.

Thanks

ITEM	DESIRED RESULT
MATERIAL BALLOTELY CREM	CREM
MATERIAL KATUN ABU (76)	ABU
MATERIAL Katun Ima GOLD	GOLD
MATERIAL BALLOTELY HIJAU	HIJAU
MMATERIAL KATUN NAVY (04)	NAVY
MATERIAL KATUN IMA TURQIS	TURQIS
MATERIAL TOYOBO PINK (09)	PINK
MATERIAL KATUN IMA PB 08	PB 08
MATERIAL TOYOBO HIJAU (63)	HIJAU
MATERIAL TOYOBO FODU PUTIH	PUTIH
MATERIAL KATUN WALLY CREAP HIJAU MINT (04)	HIJAU MINT
MATERIAL KATUN T. ASIN (18)	T. ASIN
MATERIAL CORDOBA FANTA (04)	FANTA
MATERIAL Katun Ima Ungu Tua	Ungu Tua
MATERIAL TOYOBO KUBUS (10A)	KUBUS
MATERIAL KATUN IMA PB 08 ( A )	PB 08
MATERIAL KATUN IMA PB 08 ( B )	PB 08
MATERIAL TOYOBO FODU ABU SMA (02)	ABU SMA
MATERIAL TOYOBO FODU GOLD (23)	GOLD
MATERIAL IMA PLATINUM FANTA (10 A)	FANTA
MATERIAL IMA PLATINUM PINK SEDANG (16)	PINK SEDANG
MATERIAL BALOTELLI T. ASIN (31)	T. ASIN

CodePudding user response：

There are so many ways of doing this. If the sample table is the limit of the input options, then the simplest way would be to use a Case Statement:

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

However, another method is to remove the words that are not wanted and just leave behind the desired code. Easier to maintain if the list is going to grow:

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