1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel - VLOOKUP keeping text formats

Discussion in 'Business Applications' started by ZoneRob, Mar 31, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. ZoneRob

    ZoneRob Thread Starter

    Joined:
    Mar 31, 2015
    Messages:
    4
    I am using VLOOKUP in a very old version of Excel (2000) to extract some data, nothing unusual in that except the data I wish to extract is of the following general format in each cell:

    text A
    text B
    TEXT C
    text D

    It is important to keep the formatting, in particular the bold text, however VLOOKUP does not seem to preserve the bold formatting.

    All help gratefully received. Thanks.
     
  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    There's no way to interrogate the text format (bold etc) using a simple formula, although you could use a macro. However, it could be that the bold formatting is dictated by "Text C" meeting some criteria or other and you could use conditional formatting to make it bold in the vlookup cell.
     
  3. ZoneRob

    ZoneRob Thread Starter

    Joined:
    Mar 31, 2015
    Messages:
    4
    Hi, the only criteria that the bold text meets (apart from being bold) is that the characters are all always UPPERCASE
     
  4. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    You could use conditional formatting then ...


    In your conditional formatting options change the option to "Formula Is" and the formula to:


    =exact(upper(a1),a1)


    [the formula is asking whether the cell contents in UPPERCASE are exactly the same as the cell contents as presented, so if the Vlookup pulls through something which is all uppercase, this formula result will be TRUE and the conditional formatting will be applied as a result]


    You can swap out a1 for the address of the first cell in the range you are applying the conditional formatting to


    Then you just need to change the "Format" option to change the font to bold
     
  5. ZoneRob

    ZoneRob Thread Starter

    Joined:
    Mar 31, 2015
    Messages:
    4
    Hi, not sure that would work as it is only part of the cell that is uppercase and bold, TEXT C in my original example. The other text, text A text B and text D are lowercase and not bold.
     
  6. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Ah, then that wouldn't work, unless you knew that a certain number of characters / part of the string was uppercase.
     
  7. ZoneRob

    ZoneRob Thread Starter

    Joined:
    Mar 31, 2015
    Messages:
    4
    Ok - solved by using a VB script rather than a 'simple' VLOOKUP.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1145773

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice