Apply bold font format to specific part of text string

  • Hi,


    Not sure if this is possible, but I am stuck on getting VBA to recognise a specific ID format/layout which lies amongst other text within the cell. The format goes something like "UR-ACA-###", where ### are always 3 numerical digits. I am trying to write a code to make only the IDs bold, leaving the rest of the text in the cell normal.


    e.g.

    Quote

    The model shall follow the protocol as set out in document UR-ACA-020. Failure to do so will result in the model being rejected. See also UR-ACA-300 for more details.


    So basically I want the code to find any text in the format "UR-ACA-###" and make only that text in the cell bold. I tried using the Find & Replace function in Excel but that formats the whole cell.


    Hope this makes sense! Thanks in advance!


    Jules

  • Re: Apply bold font format to specific part of text string


    To the best of my knowledge this is not possible in Excel 2003, the Font property is by Cell and cannot be applied to sub-strings within the value of the cell. Others may be able to comment if this is possible in 2007 and/or 2010.

    You may be able to achieve what you want with Text boxes where you can format sub-strings but would require a fair amout of programming.

  • Re: Apply bold font format to specific part of text string


    You can use the Characters property. If it's always the last 3 characters:

    Code
    1. Range("A26").Characters(Len(Range("A26")) - 2, 3).Font.Bold = True


    EDIT: sorry, I didn't read your question properly. I think there might be a way though...

  • Re: Apply bold font format to specific part of text string


    Well you learn something every day...

    Stephen is completely correct. You can use the Characters property to apply formatting to sub-strings within a string that is the value of a cell, even in Excel 2003.

    Your code will need to find the start of each ID. You can then do something like:

    Code
    1. rngTextCell.Characters(lngIDStart, 10).Font.Bold = True



    Where

    rngTextCell is a range specifying the cell with your text.
    lngIDStart is the start of your ID in characters from the first character of the text.

    You can then repeat that process with a new lngIDStart until you have emboldened each ID in the text string.

  • Re: Apply bold font format to specific part of text string


    Put your text in A1 and try this.

  • Re: Apply bold font format to specific part of text string


    Quote from StephenR;518024

    Put your text in A1 and try this.



    Awesome! That works perfectly!! Thank you so much Rob!

  • Re: Apply bold font format to specific part of text string


    Quote from Rob Xaos;518030

    That is all Stephen's work! I just learned from what he knew.



    Ah yes, of course! Thank you Stephen as well! You're both awesome!


    Now, I think I'm being stupid here, but I can't seem to get it to apply the code to all the cells in the range B7:B200. I tried the following but it didn't work (bits I added/changed are in red):


  • Re: Apply bold font format to specific part of text string


    You just had some extraneous ranges.

  • Re: Apply bold font format to specific part of text string


    can we modify it a bit;


    for example, i have an excel sheet with text , texts in cell is some italic and some bold only, i want to reduce font size of italic characters only for the selected range,


    can i do it with excel 2010?


    please help!

  • Re: Apply bold font format to specific part of text string


    Welcome to OzGrid, albatros79


    The policy here is you do not post questions in threads started by other members. Please start a new thread, give it an accurate and concise title as required by the rules here and explain your issue fully. If you think this thread can help clarify your issue, you can include a link by copying the URL from the address bar of your browser and pasting into your message.