Page 1 of 3 123 LastLast
Results 1 to 10 of 29

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

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,349
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

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

    Hi amaneta,

    You did not give a lot of information to work with. That said, if your sentence was in Cell A1, this would return your sentence with tags to cell A2. The requirement it does not meet is if there are two consecutive words. Although I have not worked with HTML for a long time, I see no reason why you application would not pick up the bold tags on two consecutive words with two sets of tags.

    Code:
    Sub boldtest()
    
    
        Dim wrd() As String, i As Integer
        Dim w As String
        Dim pd As String
        Dim dot As Boolean
        wrd = Split(Range("A1"), " ")
        If Right(wrd(UBound(wrd)), 1) = "." Then
            dot = True
            pd = wrd(UBound(wrd))
            wrd(UBound(wrd)) = Left(pd, Len(pd) - 1)
        End If
        For i = LBound(wrd) To UBound(wrd)
            If wrd(i) = UCase(wrd(i)) Then
                wrd(i) = " < b >  " & wrd(i) & " < / b >  "
            End If
            w = w & " " & wrd(i)
        Next
        'End If
        w = Mid(w, 2)
        If dot Then w = w & "."
        Range("A2") = w
    
    
    End Sub
    Please note the line in red may have to be edited by you, due to the quirks of posting with that style bracket.

    I hope this helps.
    Last edited by igold; Aug 17th, 2017 at 08:00 PM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  3. #3
    Board Regular
    Join Date
    Apr 2003
    Posts
    199
    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>

    I have written a little macro to do what you want. I can send you a little Excel file if you want and if we can figure out how I do that.
    This macro reads an Input Phrase from cell(10,1) of a worksheet. Then converts it to be internet ready and places the out phrase in
    cell(3,1). Oh! by the way I am 76 and learned basic many years ago. Hopefully this will give you the concept even though others would do it differently.

    Code:
    Sub InternetReadyMac()
    
        Rows("1:3").Select: Selection.Clear    'Clear Output area
        CapAVal = 65: CapZVal = 90              'Ascii values for Capital A and Z
        'Click on Help and search for Ascii Character Set to see all the 0-127 values
        AllCapFlag = True                      'Stays True if the word is all Caps
        Cells(10, 1).Select: InPhrase$ = ActiveCell 'Get the phrase
        LastChar$ = Right(InPhrase$, 1)        'add a space if last word ends in a letter
        If InStr(".,;: ", LastChar$) = 0 Then InPhrase$ = InPhrase$ + " "
        L = Len(InPhrase$)
        OutPhrase$ = "": OutWord$ = ""          'Clear OutPhrase$ and OutWord$
        For J = 1 To L
          A$ = Mid(InPhrase$, J, 1)             'Take character by character
          Cells(1, J).Select: ActiveCell = A$   'Print character in row 1
          AscVal = Asc(A$)                      'find the ascii value
          Cells(2, J).Select: ActiveCell = AscVal  'Print it in row 2
          If InStr(".,;: ", A$) = 0 Then         'is it an end of word character
                                                 'No
            If AscVal < CapAVal Or AscVal > CapZVal Then AllCapFlag = False  'Flag=False if not a Capital
            OutWord$ = OutWord$ + A$             'add char to word
          Else                                   'Yes
            If AllCapFlag = True Then OutWord$ = "" + OutWord$ + ""  'add the internet char if AllCapFlag still True
            OutPhrase$ = OutPhrase$ + OutWord$ + A$  'Add ltest word to OutPhrase
            OutWord$ = "": AllCapFlag = True         'Reset flags
         End If
        Next J
        Do                                        'eliminate any   combinations
          L = Len(OutPhrase$)
          K = InStr(OutPhrase$, " ")
          If K > 0 Then
            FirstPart$ = Left(OutPhrase$, K - 1)
            LastPart$ = Right(OutPhrase$, L - K - 7)
            OutPhrase$ = FirstPart$ + " " + LastPart$
          End If
        Loop Until K = 0
        Cells(3, 1).Select: ActiveCell = OutPhrase$
    End Sub

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    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>

    Welcome to the MrExcel board!

    A few more examples may have been useful to see if there is much variation in your data.
    I have assumed ..
    - Data is in column A, Output in column B (If you want to over-write the original data, just change the "B1" near the end of my code to "A1")
    - If a section of upper case text contains spaces, commas, hyphens or apostrophes then the text should still be treated as a single section.

    Code:
    Sub Add_Tags()
      Dim a As Variant
      Dim i As Long
      
      a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
      With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\b([A-Z][A-Z ,'\-]*[A-Z])\b"
        For i = 1 To UBound(a)
          a(i, 1) = .Replace(a(i, 1), "<b>$1</b>")
        Next i
      End With
      Range("B1").Resize(UBound(a)).Value = a
    End Sub
    Sample data and results:

    Sheet1

    AB
    1The fish is NOT allowed to swim in the water AFTER SUNSET.The fish is <b>NOT</b> allowed to swim in the water <b>AFTER SUNSET</b>.
    2ANNA SMITH-JONES arrived yesterday<b>ANNA SMITH-JONES</b> arrived yesterday
    3This is JIM'S bookThis is <b>JIM'S</b> book
    4
    5No upper case words here.No upper case words here.
    6If you look CAREFULLY, YOU WILL see AN insect.If you look <b>CAREFULLY, YOU WILL</b> see <b>AN</b> insect.
    7Part of a word in uPPer case does not get tagsPart of a word in uPPer case does not get tags


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 17th, 2017 at 10:12 PM.
    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. #5
    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>

    Thanks so much igold! I'm sorry I didn't give enough information. I'm working with a list of exam questions and answers. So, column A contains question numbers. Column B contains the questions. Columns C-F contain the answer options. Like this:

    A B C D E F
    1004 what color is the sun? green blue yellow black








    Anything in columns B-F can have words in all caps that need surrounding tags. I can have anywhere from 80 to 350 rows of questions/answers in a sheet.

    I tried your macro and it works except that the word between the tags is missing.

    If my information helps and you feel like tweaking your macro, I'd love the help! Thanks again!

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

    Wow! Thank you Jaamie! You go! I'll give this a try and post back soon.

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

    Thank you Peter_SSs! This looks like it's exactly what I need. I'll try it and post back soon! Thanks so much!

  8. #8
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,349
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 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 for the feedback. I think it is the way that the mechanics of the forum interpreted my post, it does not like open and close brackets (more than, less than). No problems, it appears as if Peter has given you a good code.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


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

    Peter...this works! THANK YOU!!

    I just have one small issue...the macro doesn't like accented caps, like in the Spanish language. Spanish is it, though, I promise! Thanks again for your help!

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,213
    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 amaneta View Post
    Peter...this works! THANK YOU!!

    I just have one small issue...the macro doesn't like accented caps, like in the Spanish language. Spanish is it, though, I promise! Thanks again for your help!
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •