Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a sheet containing thousands of cells in a column. These cell contains names of different securities.

Some names of these securities contains a particular string such as "C/O".

How can I use a formula that will return the rows that have these characters?

EG.  
S&P 500 C/O 30/03/12 1380  
MICROSOFT C/O 19/05/12 32  
QUICKSILVER C/O 17/03/12 9

There is no consistency (can't use left/right/mid formula).

Requirement : I want the formula which will return "C/O" in the other cell if this particular cell contains it.

share|improve this question
    
Are you happy to use VBA? –  Dave Rook Aug 30 '12 at 11:44
add comment

2 Answers

Use conditional formatting option Use a formula to determine which cells to format with the following formula:

=IFERROR(FIND("C/O",A1),0)>0

Whenever your cell has C/O in it, it will return a value greater than 0, and you can have any formating applied (e.g. change fill color). When C/O is not present, it will remain formatted normally.

Conditional Find 2

Conditional Find

The FIND function looks for C/O and returns it starting point, which is 1 or greater. If C/O is not present it returns a #Value error. So, this uses IFERROR to return 0 for all error conditions (i.e. when C/O is not present).

EDIT: Using a reference cell for your search text (i.e. C/O in cell C1) will allow you to change your search string and highlight other cells based upon that. Note in the conditional formating window, you'll need to use an absolute reference to make sure that regardless of the evaluated cell, it always looks to $C$1. For example, you can have it find S&P:

enter image description here

share|improve this answer
1  
And to update this a little more, if you don't hard code "C/O") you can reference an external absolute cell which may make changing the criteria easier. Such as =IFERROR(FIND($D$1,A1),0)>0 –  Dave Rook Aug 30 '12 at 11:59
    
Absolutely, if there's any chance you'll want to find/highlight any other string, you should do as @DaveRook suggests, and basically create a search variable to work from. –  dav Aug 30 '12 at 12:01
    
I've updated the answer based upon @DaveRook's suggestion. –  dav Aug 30 '12 at 12:05
    
Nice,Good solution +1 –  Ishikawa Yoshi Aug 30 '12 at 12:43
add comment

You can convert file to CSV and use regexp

share|improve this answer
    
But sometimes it's very quick solution –  Ishikawa Yoshi Aug 30 '12 at 12:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.