Extract one specific key and value from a comma separated list in every cell of a column-CodePudding

I'm trying to extract 'manufacturer=acme' from, for example:

attribute1=red,attribute2=medium,manufacturer=acme,attribute4=spherical

from column 'attributes', for which there are 8000 rows.

I can't use left(), right(), split() functions because the manufacturer attribute doesn't have a fixed number of attributes/characters to the left or right of it and split() only works for one character, not a string.

Is there a way I can achieve this, target the string manufacturer= and remove all text from the left and right starting from its encapsulating commas?

CodePudding user response：

Quick mock-up for looping through a split string (untested):

dim stringToArray as variant:  stringToArray = split(target.value, ",")
dim arrayItem as long
for arrayLocation = lbound(stringToArray) to ubound(stringToArray)
    if instr(ucase(stringToArray(arrayLocation)), ucase("manufacturer=")) then
        dim manufacturerName as string:  manufacturerName = right(stringToArray(arrayLocation), len(stringToArray(arrayLocation))-len("manufacturer="))
        exit for
    end if
next arrayLocation
debug.print manufacturerName

CodePudding user response：

I have, maybe, an overkill solution using RegExp. Following is a UDF you can use in a formula

Public Function ExtractManufacturerRE(ByRef r As Range) As String
On Error GoTo RETURN_EMPTY_STR
    Dim matches As Object

    With CreateObject("VBScript.RegExp")
        .Pattern = "manufacturer=[^,] "
        .Global = False
        Set matches = .Execute(r.Value)
        If matches.Count > 0 Then
            ExtractManufacturerRE = matches.Item(0).Value
        End If
    End With

RETURN_EMPTY_STR:
End Function

To be fair, this is sub-optimal, plus it doesn't work on a range but only on a single cell.