Find underline and bold text with cells.

  • I am looking for a couple formulas, possibly VBA.
    1. To count all cells in a column which has double underlined text.
    2. To count all cells in a column which has text both in a certain color (purple 10498160) and bold. It cannot just find bold, it must be both bold and that color in the same text, because other words might be in bold in the same cell, but not purple.


    Thank you for whatever help you can offer.

  • I did find this function for counting cells with underline in them, but it appears to count just underline and not specify single or double.


    Function CountUnder(rngInput As Range) As Double
    Application.Volatile


    Dim rngCell As Range
    Dim lngCountUnder As Long
    lngCountUnder = 0


    For Each rngCell In rngInput
    If rngCell.Font.Underline <> xlUnderlineStyleNone Then
    lngCountUnder = lngCountUnder + 1
    End If
    Next rngCell


    CountUnder = lngCountUnder


    End Function

  • Even when changing the style on this it for some reason doesn't seem to work right. It might count cells that only has single underline whereas I have some with both single and double in the same cell.

  • Hello,


    Not sure to fully understand your constraints ...


    A couple of remarks :


    1. Regarding your three categories, you can use

    Code
    .Underline = xlUnderlineStyleNone
            .Underline = xlUnderlineStyleSingle
            .Underline = xlUnderlineStyleDouble


    2. The code is Volatile ... so if you make modifications, you do need to press F9 in order to recalculate ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you, that does confirm the suspicion I had for the three categories, but it still doesn't recognize cells which have both double and single underlines. It ignores them completely, whereas I would like it to still count them.

  • Hello,


    Are you saying that your sheet has cells which combine both single and double underlines within the same cell ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • OK ...


    Below is a modified UDF ... to be tested ...


    Should a single character in any cell be underlined ( single Or double ), it will be included in the count ... :wink:



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I appreciate so much your help!! Unfortunately I think I wasn't clear enough. In the end I would like to have a count of all cells which contain some or any text that is underlined once, then another separate count of all cells which contain some or any text underlined twice. So I am trying to find a formula that will count the single underline in a cell whether it has the double underline or not. Sorry for the confusion, I hope that is clear, and again Thank you!

  • Would you mind attaching a sample file with an example of your expected output ....:wink:


    This would make things a lot clearer for everybody... and .. also ...much easier to solve ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Document uploaded.

    Explanation:
    Column Y is the one I am interested in.


    1. I would like to count how many times the cells (Y3:Y46) have text which has a single underline. It would be really cool if there was a way to determine even if there were breaks in the underline.For instance Y28 has "&" and then later in the same cell "lo spirito" and this would count as 2. However I would be fine with just something that counted every cell which included something underlined. If I counted right the total would be 11.
    2. Same thing for double underlined.
    3. Count how many cells include a text which is the color purple (color code 10498160) and Bold at the same time. As you can see there is other bold text and other purple text, so it has to read that the text is both bold in purple in the same place.


    I hope this is somewhat clearer.

  • Sorry, my estimations or counting is found in Y62 for the purple & bold, Y65 for the double underline, and Y11 for the single underline.

  • Glad your problem is now solved ...:wink:


    Thanks for your Thanks ...AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!