Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

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

  1. #21
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,247
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Peter_SSs View Post
    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).
    Last edited by Rick Rothstein; Sep 20th, 2019 at 11:11 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  2. #22
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Quote Originally Posted by Rick Rothstein View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #23
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,247
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by Peter_SSs View Post
    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...
    Code:
    ProperCaseVersion = Application.Proper(Itm)
    we would need more code along these lines...
    Code:
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #24
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Quote Originally Posted by Rick Rothstein View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #25
    New Member
    Join Date
    May 2013
    Location
    Athens, Greece
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

    .



  6. #26
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Thanks very much for your generous words. We look forward to hearing from you in due course if anything further is required.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #27
    New Member
    Join Date
    May 2013
    Location
    Athens, Greece
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?).

    Code:
    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
    Last edited by abramo; Sep 21st, 2019 at 11:18 AM.

  8. #28
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    Quote Originally Posted by abramo View Post
    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)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #29
    New Member
    Join Date
    May 2013
    Location
    Athens, Greece
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!


    .
    Last edited by abramo; Sep 22nd, 2019 at 08:36 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •