Solved: Excel - VLOOKUP keeping text formats

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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 [FONT=&quot]bold [/FONT]text, however VLOOKUP does not seem to preserve the [FONT=&quot]bold [/FONT]formatting.

All help gratefully received. Thanks.
 
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.
 

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

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

ZoneRob

Thread Starter
Joined
Mar 31, 2015
Messages
4
Ok - solved by using a VB script rather than a 'simple' VLOOKUP.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top