+ Reply to Thread
Results 1 to 5 of 5

Highlight a specific amount of characters in a cell

  1. #1
    Registered User
    Join Date
    05-05-2022
    Location
    North Carolina, USA
    MS-Off Ver
    2019 Excel
    Posts
    2

    Highlight a specific amount of characters in a cell

    First time post, hello all!

    I have a spreadsheet that I use for researching. My question is, is there a way to have the first 35 characters of a single cell highlights in some way(color, bold, etc..)? I have one cell that I type in product descriptions, max is around 150 characters. I would love it if the first 35 characters would stand out because these are the most critical when doing SEO.

    Also if possible, could the same cell have some alert, color maybe, when I hit the max character count of 150? I have an adjacent cell that count the number of characters now but the other way would be ideal. Thank you.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: Highlight a specific amount of characters in a cell

    Only way to highlight (bold etc) part of string in a cell, is by hand or by VBA.
    Ex:
    Please Login or Register  to view this content.
    Note: Just a demo. So if you edit cell. Code should be re-run while range is selected. You could write event code. But I wasn't sure of your data location etc.

    As for highlighting when character count goes over 150... you can use conditional format.

    Assuming Column A, and data starts on row 2. Select data range. Conditional format -> New Rule. use a formula to determine...
    =LEN($A2)>150

    Set fill color as desired.
    Last edited by CK76; 05-05-2022 at 10:58 AM.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,851

    Re: Highlight a specific amount of characters in a cell

    Hi hassified and welcome to the forum,

    If you select a single cell and use the formula bar and select some characters, you can change the color or font of those individual characters. In VBA the code is for Characters. See https://docs.microsoft.com/en-us/off...cel.characters
    I believe you would need to write some VBA code to go through each cell in your column and change the first 35 characters to bold.

    I think you would be better off by having a helper column of the first 35 characters of your original string using the Left() function.

    Here is some code to turn the first 35 characters of the string in cell A1 to bold.
    Please Login or Register  to view this content.
    It is interesting that when only some of the characters are bold then asking if the whole is bold or not, the answer is "Null" instead of True or False using a Debug.Print Cells(1,"A").Font.Bold = True in the immediate window.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    05-05-2022
    Location
    North Carolina, USA
    MS-Off Ver
    2019 Excel
    Posts
    2

    Re: Highlight a specific amount of characters in a cell

    Thank you MarvinP. I have used your code and like how it BOLDS the first 35. But the unBold code loses me. Does it need to be?

    I used the code like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I added the first bit of code so I can have it run anytime I change the cell automatically. The only not so big issue is, when I delete/clear the text from the cell and type in new, all the text is in bold. I have to turn off the bold in the font tab. Is there a way around this?
    Last edited by hassified; 05-08-2022 at 01:12 AM.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,865

    Re: Highlight a specific amount of characters in a cell

    Try

    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to highlight amount of a specific column of each tab
    By karmanprof in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2014, 02:35 AM
  2. concatenate with specific amount of characters?
    By Dubrock in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-11-2013, 03:14 AM
  3. Need to add spaces to end of text to reach a specific amount of characters.
    By keshido in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2013, 08:37 PM
  4. Trim a files name to a specific amount of characters using vba.
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2011, 11:54 AM
  5. Replies: 5
    Last Post: 09-30-2010, 08:02 PM
  6. setting up specific amount of characters
    By youngw in forum Excel General
    Replies: 1
    Last Post: 03-26-2008, 02:00 PM
  7. [SOLVED] Function to indicate the amount of characters in a cell?
    By ssciarrino in forum Excel General
    Replies: 2
    Last Post: 03-27-2006, 11:10 AM
  8. [SOLVED] Can I limit the amount of characters in a cell?
    By Erin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2005, 09:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1