Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 29

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

  1. #11
    New Member
    Join Date
    Aug 2017
    Posts
    6
    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>

    Quote Originally Posted by Rick Rothstein View Post
    It is 3:17 in the morning where Peter lives as I write this, so he won't be up to answer you for a few hours. I am rusty in my RegExp knowledge, but looking at this line from Peter's code...

    .Pattern = "\b([A-Z][A-Z ,'\-]*[A-Z])\b"

    but if you copy/paste each upper case Spanish letter (from some text source with them in it) that you want the code to recognize immediately after each Z in the code line, I think that should make the code work the way you want.
    GENIUS!! It works perfectly now! Thank you Rick! And thanks so much to everyone who responded. You've helped me a lot and I really appreciate it!

  2. #12
    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 amaneta View Post
    Peter...this works! THANK YOU!!
    You are welcome.

    .. and thanks to Rick for the 'Spanish tweak'.
    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. #13
    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>

    To anybody reading this, greetings.

    I wonder whether a new thread should be started, or not, regarding modification of @Peter_SSs at #4 - to satisfy a different end requirement.

    In particular, instead of adding tags to the full UPPERCASE elements as detected by the current vba to just return said detected elements *converted in proper case*.

    Any solutions much appreciated as usual, needless to say
    Last edited by abramo; Sep 20th, 2019 at 06:58 AM.

  4. #14
    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
    To anybody reading this, greetings.

    I wonder whether a new thread should be started, or not, regarding modification of @Peter_SSs at #4 - to satisfy a different end requirement.

    In particular, instead of adding tags to the full UPPERCASE elements as detected by the current vba to just return said detected elements *converted in proper case*.

    Any solutions much appreciated as usual, needless to say
    Could we have a small sample data and expected results? Or perhaps just the expected results if the sample data was as in column A of post 4?
    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. #15
    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>

    Hello @Peter_SSs - and thanks.
    Example of text in cell:

    Code:
    BEAUTIFUL DAY is it NOT. We are GOING to have  a nice TIME.

    Result:
    Code:
    Beautiful Day is it Not. We are Going to have  a nice Time.


    I have come to this, works fine, perhaps can do with tidying up??:

    Code:
    
    
    Code:
    Sub ConvertUppercaseWordsToProper()
    
    
    Dim ToReplace As Object
    For Each c In Selection.Cells
    
    
     'This generates a list of items that match.
     
     With CreateObject("VBScript.RegExp")
    
    
      .Global = True
      .Pattern = "\b([A-Z][A-Z ,'\-]*[A-Z])\b"
      Set ToReplace = .Execute(c.Value)
      
    End With
    
    
    'This converts to Proper case the item
    
    
     Dim ProperCaseVersion As String
     Dim ItemCt As Integer
     For ItemCt = 0 To ToReplace.Count - 1
     ProperCaseVersion = StrConv((ToReplace.Item(ItemCt)), vbProperCase)
    
    
    'This replaces the item with the Proper case version
     
    With CreateObject("VBScript.RegExp")
    
    
      .Global = True
      .Pattern = ToReplace.Item(ItemCt)
      c.Value = .Replace(c.Value, ProperCaseVersion)
      
      End With
      
      Next ItemCt
      
    Next c
    
    
    End Sub
    Alternatively, leaving any items smaller than 3 characters unchanged:
    Example:
    Code:
    BEAUTIFUL DAY is it NOT. We are GOING to have  a nice TIME.

    Result:
    Code:
    Beautiful DAY is it NOT. We are Going to have  a nice Time.

    Code:
    Sub ConvertUppercaseWordsToProper()
    
    
    Dim ToReplace As Object
    For Each c In Selection.Cells
    
    
     'This generates a list of items that match.
     
     With CreateObject("VBScript.RegExp")
    
    
      .Global = True
      .Pattern = "\b([A-Z][A-Z ,'\-]*[A-Z])\b"
      Set ToReplace = .Execute(c.Value)
      
    End With
    
    
    'This converts to Proper case the item
    
    
     Dim ProperCaseVersion As String
     Dim ItemCt As Integer
     For ItemCt = 0 To ToReplace.Count - 1
     ProperCaseVersion = StrConv((ToReplace.Item(ItemCt)), vbProperCase)
    
    
    'This replaces the item with the Proper case version
     
    With CreateObject("VBScript.RegExp")
    
    
      .Global = True
      .Pattern = ToReplace.Item(ItemCt)
      If Len(c.Value) > 3 Then c.Value = .Replace(c.Value, ProperCaseVersion)
      
      End With
      
      Next ItemCt
      
    Next c
    
    
    End Sub
    Comment? Faster & more efficient?

    Thanks!

    .







  6. #16
    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>

    Nah! alternative version "leaving any items smaller than 3 characters unchanged" does not really work (embarrassed!)
    Any help with that?
    Thanks!

    .

  7. #17
    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>

    Meh! how silly of me, obvious mistake corrected - here is an alternative version "leaving any items smaller than 3 characters unchanged" that does work!
    Code:
    
    
    Code:
    Sub ConvertUppercaseWordsToProper()
    
    
    Dim ToReplace As Object
    For Each c In Selection.Cells
    
    
     'This generates a list of items that match.
     
     With CreateObject("VBScript.RegExp")
    
    
      .Global = True
      .Pattern = "\b([A-Z][A-Z ,'\-]*[A-Z])\b"
      Set ToReplace = .Execute(c.Value)
      
    End With
    
    
    'This converts to Proper case the item
    
    
     Dim ProperCaseVersion As String
     Dim ItemCt As Integer
     For ItemCt = 0 To ToReplace.Count - 1
     ProperCaseVersion = StrConv((ToReplace.Item(ItemCt)), vbProperCase)
    
    
    'This replaces the item with the Proper case version
     
    With CreateObject("VBScript.RegExp")
    
    
      .Global = True
      .Pattern = ToReplace.Item(ItemCt)
      If Len(ProperCaseVersion) > 3 Then c.Value = .Replace(c.Value, ProperCaseVersion)
      
      End With
      
      Next ItemCt
      
    Next c
    
    
    End Sub
    Even so, I was hoping to more tidy & efficient code.
    Any ideas?

    Thanks!

  8. #18
    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>

    Not really - the alternative version "leaving any items smaller than 3 characters unchanged" does not work as required. Sorry!
    Confused, no idea.

    .

  9. #19
    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>

    Any help appreciated.
    .

  10. #20
    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
    Comment? Faster & more efficient?
    In my mind the code below is a bit 'neater' & it should be a bit faster/more efficient though you probably will not notice unless there is a lot of data to process.

    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.

    In any case, this is what I came up with for the equivalent of your first code (that is, not excluding short words).
    Note also that at the moment, my code writes the results in the next column to the right so that it is easy to compare result with original. Eventually you could remove the blue code to over-write the original data.
    This code still converts SMITH-JONES to Smith-jones but we can look at that later if required.

    Code:
    Sub ConvertUppercaseWordsToProper_v2()
      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,'\-]*[A-Z])\b"
      With Selection
        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, StrConv(itm, vbProperCase), 1, -1, 0)
          Next itm
          a(i, 1) = ProperCaseVersion
        Next i
        .Offset(, 1).Value = a
      End With
    End Sub
    Sample data and code results.

    Convert case

    AB
    1The fish is NOT allowed to swim in the water AFTER SUNSET.The fish is Not allowed to swim in the water After Sunset.
    2ANNA SMITH-JONES arrived yesterdayAnna Smith-jones arrived yesterday
    3This is JIM'S bookThis is Jim's book
    4
    5No upper case words here.No upper case words here.
    6IF you look CAREFULLY, YOU WILL see AN insect.If you look Carefully, You Will see An insect.
    7Part of a word in uPPer case does not get convertedPart of a word in uPPer case does not get converted


    Excel tables to the web >> Excel Jeanie HTML 4


    To exclude words of 3 characters or less, just change the Pattern to
    Code:
    RX.Pattern = "\b([A-Z][A-Z,'\-]*[A-Z])\b"
    RX.Pattern = "\b([A-Z][A-Z,'\-]{2,}[A-Z])\b"
    Sample data and code results.

    Convert case

    AB
    1The fish is NOT allowed to swim in the water AFTER SUNSET.The fish is NOT allowed to swim in the water After Sunset.
    2ANNA SMITH-JONES arrived yesterdayAnna Smith-jones arrived yesterday
    3This is JIM'S bookThis is Jim's book
    4
    5No upper case words here.No upper case words here.
    6IF you look CAREFULLY, YOU WILL see AN insect.IF you look Carefully, YOU Will see AN insect.
    7Part of a word in uPPer case does not get convertedPart of a word in uPPer case does not get converted


    Excel tables to the web >> Excel Jeanie HTML 4


    My other comment is that I am not a fan of using 'Selection' as the basis of what to process. If the data is in column A, I would prefer something like this
    Code:
    With Selection
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
    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

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
  •