Excel macro to find words in all caps then surround them in an html tag (e.g., <b> and </b>

[amaneta]

Hello!

I need an Excel macro that finds words or phrases in uppercase, then surrounds them with the html tags for bold. The uppercase words can have as few as 2 letters like "NO", and there can be more than 1 word in uppercase, in which case I want the tags to surround all uppercase words that are next to each other.

For example:

"The fish is NOT allowed to swim in the water AFTER SUNSET." should be changed to "The fish is <b>NOT</b> allowed to swim in the water <b>AFTER SUNSET</b>."

I have to add the tags so that when I import the excel file into another application, it recognizes the formatting.

Any help is greatly appreciated!

 

[Rick Rothstein]

My main comment is that this sort of string manipulation can be extremely difficult to cover all bases.

As an example, your code converts
ANNA SMITH-JONES arrived yesterday
to
Anna Smith-jones arrived yesterday

I suspect that you wouldn't want that "J" converted to lower case?

We could change the pattern so that any upper case letter immediately after a word boundary or certain other characters remained as upper case. That would help with the "J" in SMITH-JONES to give Smith-Jones and the "C" in JAMES O'CONNOR to give James O'Connor
However, that O'Connor example also brings a problem if you have a apostrophe like this: JIM'S where I doubt you would want Jim'S returned.

Don't use StrConv to change text to proper case (as you point out, it does a terrible job of it), rather, use Application.Proper instead (I believe it handle's the above correctly).

 

[Peter_SSs]

Don't use StrConv to change text to proper case (as you point out, it does a terrible job of it), rather, use Application.Proper instead (I believe it handle's the above correctly).

Thanks Rick. I was basically following the OP's method and to be honest I'm not sure about which is "best"

StrConv does not do SMITH-JONES "correctly" but does do JIM'S correctly
Application.Proper simply reverses those results.

 

[Rick Rothstein]

StrConv does not do SMITH-JONES "correctly" but does do JIM'S correctly
Application.Proper simply reverses those results.

Hmm! I wasn't aware that Excel's PROPER function didn't handle 's, 't, etc. at the end of a word correctly. Okay, given that, I am not sure how to work this into your code, besides, I am sure you will develop a RegExp equivalent; but after a code line like this...

ProperCaseVersion = Application.Proper(Itm)

we would need more code along these lines...

If ProperCaseVersion & " " Like "*'?[!A-Z,a-z]*" Then
  Arr = Split(ProperCaseVersion, "'")
  For X = 1 To UBound(Arr)
    If Arr(X) & " " Like "[A-Z][!A-Za-z]*" Then
      Arr(X) = LCase(Left(Arr(X), 1)) & Mid(Arr(X), 2)
    End If
  Next
  ProperCaseVersion = Join(Arr, "'")
End If

 

[Peter_SSs]

I am not sure how to work this into your code,

Me either, but I'm not going to bother until we hear back from abramo as it it may be completely irrelevant to their possible data.

 

[abramo]

Never fails to amaze us all regarding the perpetual stamina/patience/helpfulness/community spirit along with knowledge of @Peter_SSs , @Rick Rothstein & some other old guns at MrExcel.com. While over the years I needed to directly request little, the immense volume of knowledge & sound advice readily available here has been instrumental in resolving all my Excel issues. The term "amazing" is nowadays used too freely, but much deserves to be applied here.

Having said that, the responses provided in my present case deserve respect in the form of careful study before commenting, so please allow me some time before coming back to you.

Thank you, thank you, thank you!

.

 

[Peter_SSs]

Thanks very much for your generous words. We look forward to hearing from you in due course if anything further is required.

 

[abramo]

Many thanks - took just minutes to test/verify that code at #20 in conjuction with #23 performs as advertised. Perfect! Took longer to study its workings, but was immdediately apparent this is a simple/elegant/efficient product of experience, far beyond any of my own capabilities. Learning!

1.- Requirements refer to product data of average 200k rows, sometimes up to 1M.
2.- Incidence of SMITH-JONES much higher than JIM'S, even so have opted for Application.Proper in conjunction with #23 tidying-up code (see below).
3.- For range maybe Range(Selection, Selection.End(xlDown)) - as any full column or selection within may be covered.
4.- Patterns do miracles - what about tidying up Application.Proper with pattern? GREED! (a child in a toy shop?).

Sub ConvertUppercaseWordsToProper_v3()
  Dim RX As Object, ToReplace As Object
  Dim a As Variant, itm As Variant
  Dim ProperCaseVersion As String
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b([A-Z][A-Z,'\-]{2,}[A-Z])\b"
  With Range(Selection, Selection.End(xlDown))
    a = .Value
    For i = 1 To UBound(a)
      ProperCaseVersion = a(i, 1)
      Set ToReplace = RX.Execute(ProperCaseVersion)
      For Each itm In ToReplace
        ProperCaseVersion = Replace(ProperCaseVersion, itm, Application.Proper(itm), 1, -1, 0)
        
If ProperCaseVersion & " " Like "*'?[!A-Z,a-z]*" Then
  Arr = Split(ProperCaseVersion, "'")
  For x = 1 To UBound(Arr)
    If Arr(x) & " " Like "[A-Z][!A-Za-z]*" Then
      Arr(x) = LCase(Left(Arr(x), 1)) & Mid(Arr(x), 2)
    End If
  Next
  ProperCaseVersion = Join(Arr, "'")
End If


      Next itm
      a(i, 1) = ProperCaseVersion
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

 

[Peter_SSs]

4.- Patterns do miracles - what about tidying up Application.Proper with pattern? GREED! (a child in a toy shop?).

I think there are just too many possibilities to get this perfect
Examples of some more possible difficulties (& what your current code returns for them)

MACDONALD - Some families use Macdonald but many use MacDonald (current code gives Macdonald)

LEONARDO DICAPRIO = Leonardo DiCaprio (current code gives Dicaprio)

WE'LL come later as we CAN'T come now = We'll come later as we Can't come now (current code gives We'Ll come later as we CAN't come now)

 

[abramo]

Many thanks!

WE'LL come later as we CAN'T come now

Steady there - there may be minors reading.
(^joke )


Jokes apart, as in life : need to compromise. Perfectionism kills. While compromise is a hard pill to swallow, saves lives (does it?).

Code as it stands now covers correctly a substantial percentage of incidence in my application. Shortly, I will try to add on #23 to cover the more common trouble points as spotted along the way. Maybe we have to wait till AI with inherent learnt knowledge to resolve my mundane issue (not holding my breath!).


Having said that, I hope some unfortunate fellow needing this application may be able to locate this "sub-thread" and be helped - or should I start a new thread with more specific title and OP introduction particular to the subject of this "sub-thread" - easier to locate by Search?


Thanks!


.