Extract all delimited Word <b>…</b>

[pociners]

Hi.. everyone,

I have a problem in extracting word in MS Excel. I have a multiple sentences with HTML Format in row and want to extract all of the word that has been delimited with

[COLOR=#303336][FONT=Consolas][FONT=inherit]<[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]b[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]>...< /<!--"<!--</span---->[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]<!--</span-->[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]b[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]>[/FONT][/FONT][/COLOR]

Below is the example word:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#303336][FONT=inherit]<[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit]buat<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]< /[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<[/FONT][/COLOR][COLOR=#303336][FONT=inherit]i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit]v[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=inherit]i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]< /[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] kerjakan[/FONT][/COLOR][COLOR=#303336][FONT=inherit];[/FONT][/COLOR][COLOR=#303336][FONT=inherit] lakukan[/FONT][/COLOR][COLOR=#303336][FONT=inherit];[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]2<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]< /[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] bikin[/FONT][/COLOR][COLOR=#303336][FONT=inherit];<[/FONT][/COLOR][COLOR=#303336][FONT=inherit]br[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>--<[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit]cendol<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=inherit]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]<!--</span-->[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]< /[/FONT][/COLOR][COLOR=#303336][FONT=inherit]b[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR]</code>

and I want to extract the words: "buat", "1", "2", "cendol"

Can you help me to solve my problem? Any code in excel/ vba is appreciated
Thanks,
Budyono

 

[sandy666]

look here: https://www.poweredsolutions.co/2018/04/02/replacing-values-in-power-bi-power-query-html-code/

 

[Rick Rothstein]

Assuming you do not really have a space in "< /b>" within your text, you can use this function to retrieve the words between the bold tags...

Function GetBolds(ByVal S As String) As String
  Dim X As Long, Arr As Variant
  Arr = Split(Replace(S, "</b>", "<b>"), "<b>")
  For X = 1 To UBound(Arr) Step 2
    GetBolds = GetBolds & ", " & Arr(X)
  Next
  GetBolds = Mid(GetBolds, 3)
End Function