0

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?

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
eleventy
  • 1
  • 1
  • 2
  • 1
    Please provide enough code so others can better understand or reproduce the problem. – Community Sep 26 '22 at 16:36
  • 1
    "split() only works for one character" that's not true. A better approach might be to first split on the comma, then loop the resulting array looking for "manufacturer=" in each element. – Tim Williams Sep 26 '22 at 16:38
  • Thanks for replying. So can you use split based on multiple characters (not just one)? – eleventy Sep 26 '22 at 16:46
  • @TimWilliams upvoted you after i posted an answer as i missed that you and i had the same thought path. if you end up posting an answer i can take mine down since you got here first. – Cyril Sep 26 '22 at 17:31
  • Have a look at [`FILTERXML()`](https://stackoverflow.com/q/61837696/9758194), for example `=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//s[starts-with(.,'manufacturer=')]")` – JvdV Sep 26 '22 at 17:53
  • 1
    This is interesting. Gonna fire up the windows box. Many thanks – eleventy Sep 26 '22 at 18:33
  • @Cyril - no problem I'm not working on anything here – Tim Williams Sep 26 '22 at 18:40

2 Answers2

0

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
Cyril
  • 6,448
  • 1
  • 18
  • 31
0

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.

milo5m
  • 619
  • 1
  • 3
  • 8