Extract string(s) from string

[Gargoolgala]

Hi,

I have data exported from PowerApps/MS Flow into a excel spreadsheet and I need to extract the specific strings from the data to paste into Word.

Sample strings:
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"][{"Id":2,"Value":"Unix/Linux"},{"Id":3,"Value":"VPSX"}][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Single forest multiple domains"}

The strings I need are always after the Value text in the string . There could be multiple "Value"s in one string or the string could be empty and just show []

I am trying to work out how I can extract so the results would be:
Unix/Linux,VPSX - this would need to probably need to be concatenated together
Single forest multiple domains - this is one value and needs the spaces

I have tried using replace to remove all the extra data but that ends up turning into a single string with no spacing - obviously did that wrong.

I have searched for options but cannot wrap my head around it

Any ideas appreciated.[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Have you given us two or three examples, and what results do you want from each of those examples?

 

[Gargoolgala]

I put my desired results in the request

I am trying to work out how I can extract so the results would be:
Unix/Linux,VPSX - this would need to probably need to be concatenated together
Single forest multiple domains - this is one value and needs the spaces

 

[Rick Rothstein]

@mikerickson,

Click "Reply With Quote" on the original message and you will see there are two example text strings, not three.

@Gargoolgala,

Give this UDF (user defined function) a try...

[table="width: 500"]
[tr]
	[td]Function GetValue(S As String) As String
  Dim X As Long, Vals() As String
  Vals = Split(S, """Value"":""")
  For X = 1 To UBound(Vals)
    GetValue = GetValue & "/" & Split(Vals(X), """")(0)
  Next
  GetValue = Mid(GetValue, 2)
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetValue just like it was a built-in Excel function. For example,

=GetValue(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Gargoolgala]

Rick,

you are awesome - thanks very much - this is exactly what I needed.