Format Text in spreadsheet based on text?

  • Thread starter Thread starter James B
  • Start date Start date
J

James B

I have a really bad spreadsheet that I am unable to sort or create a list
out of without doing a bunch of manual editing so I was thinking that if I
could highlight certain text with a different font, color, bold or whatever
that would be good enough so I can visually make out the data I need and
ignore the rest.

Any way of doing that which is simple, quick and a few clicks away?
 
James,

You can use conditional formatting to change cell fill colors, font
attributes and borders based on the contents of the cell (or other cells,
such as in the same row). You have three conditios you can set up per cell,
each with the designated formatting you require, plus the normal formatting
that is in effect when none of the three conditions is true. If you don't
require all the formatting in the same cell, it's possible to use
conditional formatting in any cell in the row, giving you more conditions,
since a conditionally formatted cell can look at other cells. So you could
have up to three conditional formats in one cell, three more in the next
cell, etc. If you need more than three conditions in the same cell, you'll
need to use a macro.

If you want to use many of Excel's features (sorting, summarizing, charting,
etc.) you need to get the sheet into a database style table, where there's
one row per item. It might be worth rearranging the sheet for that.
 
You could do that--but I've always found my eyesight is less than trustworthy.

If you're looking for text in a single cell, you could use a formula like:

=if(countif(a1,"*test 123*")>0,"found it","")

Then if A1 contained.

The results of test 123 were negative.

You'd see "found it" in that helper column.

Then using Data|filter|autofilter
show blanks or non-blanks depending on what you want

Maybe more useful.

You could even do something similar if you're looking for "test 123" in any of 3
columns:
=if(countif(a1:c1,"*test 123*")>0,"found it","")

===
Using those filters (and sorts) always worked better for me when cleansing data.
 
Back
Top