VBA: Excel Uppercase words between HTML <strong>

[Dave_O]

Hello,
I am saving a website's source code as a text file, then importing it into a worksheet.

I currently use a UDF to vbCrLf existing Uppercase word's,
but I would like to make all words Uppercase between <strong> .....<\/strong>

I am OK with either VBA or UDF.

SAMPLE:
As is:
<strong>Balaabel (2)&nbsp;<\/strong>

To be:
<strong>BALAABEL (2)&nbsp;<\/strong>


**************************************************

Function upperCaseWords(S As String) As String
    Dim RE As Object

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .Pattern = "^|\s(\b[^a-z0-9]+\b\s*\()"
    upperCaseWords = .Replace(S, vbCrLf & "$1")
End With
End Function

**************************************************


Screenshot attached.
Thank you.

 

[Akuini]

Try this:
Put the data in col A, the result is in col B.
If in a cell there's "<strong>" but no "<\/strong>" after that, it will return false.

Sub a1116877a()
'https://www.mrexcel.com/board/threads/vba-excel-uppercase-words-between-html-strong.1116877/
Dim i As Long, j As Long, n As Long
Dim tx As String
Dim va, vb, ary, z
va = Range("A1", Cells(Rows.count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 1)

For i = 1 To UBound(va, 1)
    tx = va(i, 1)
    ary = Split(tx, "<strong>")
    If UBound(ary) > 0 Then
        For j = 1 To UBound(ary)
            z = ary(j):  n = InStr(z, "<\/strong>")
            If n = 0 Then vb(i, 1) = "FALSE": GoTo SKIP:
            ary(j) = UCase(Left(z, n - 1)) & Mid(z, n, Len(z))
        Next
    vb(i, 1) = Replace(Join(ary, "<strong>"), "&NBSP", "&nbsp")
    Else
    vb(i, 1) = va(i, 1)
    End If
SKIP:
Next

Range("B1").Resize(UBound(vb, 1), 1) = vb
End Sub

EXAMPLE:

Book1
	A	B
1	<strong>Balaabel (2)&nbsp;<\/strong>	<strong>BALAABEL (2)&nbsp;<\/strong>
2	<strong> ...abcd..<\/strong>	<strong> ...ABCD..<\/strong>
3	Using the <strong> code<\/strong> in post #21 <strong> this code<\/strong>	Using the <strong> CODE<\/strong> in post #21 <strong> THIS CODE<\/strong>
4
5	I think the <strong> code should do what you want.	False
Sheet5

 

[Dave_O]

Hi Akuini,
This works perfectly.

Thank you very much for your time & effort.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[Norie]

I'm a little confused, why is <\/strong> being used as the closing tag?

Anyway, I've based this code on the closing tag being the 'normal' </strong> .

Function UpperCaseWords(S As String) As String
Dim RE As Object
Dim Matches As Object
Dim Match As Object
Dim strNewString As String

    Set RE = CreateObject("vbscript.regexp")
    
    strNewString = S
    
    With RE
        .Global = True
        .MultiLine = True
        .Pattern = "<strong>(.*?)</strong>"
        Set Matches = .Execute(S)
        
        For Each Match In Matches
            strNewString = Replace(strNewString, Match.Submatches(0), UCase(Match.Submatches(0)), , 1)
        Next Match
        
    End With

    UpperCaseWords = strNewString
    
End Function

 

[Dave_O]

Hi Norrie,
I too am confused why they used <\/ but it is at the end of all of their closing tags, throughout the source code. Mystery.

Anyway, if I do a replace <\/ with </ then your function also works and I thank you for your response.