How can I extract text in value in the example below in Excel?-CodePudding

I have a list of 1000 option like an example below and I need to extract text in the value. The desired result is 0012 – Senior government managers and officials in the following case.

<option label="0012  –  Senior government managers and officials" value="0012  –  Senior government managers and officials"></option>

CodePudding user response：

This should do the trick, where B2 is your string. This will extract everything between value=" and ".

=MID(B2,FIND("value=",B2) 7,FIND(CHAR(34),B2,FIND("value=",B2) 7)-FIND("value=",B2)-7)

CodePudding user response：

As one option you can use VBA where you can define a UDF:

Function getValues(html As String) As String
    getValues = Split(Split(html, "value")(1), """")(1)
End Function

And then call it in your workbook like a formula:

=getValues(A1)

Personally I find the option to split a string by a delimiter and pull out one of the values very handy so I would define this more generically:

Function strtok(instring As String, delim As String, position As Integer) As String
    strtok = Split(instring, delim)(position - 1)
End Function

And then formula out in the sheet would be a bit more complex:

=strtok(strtok(A1, "value", 2), """", 2)