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 was wondering if it is possible to search for part of a text string. For example if the following is in cell A9:

[Agent Name & ID: Ted Simpson - 1008]

I am looking for a way to shorten it to

if(a1:a9="1008", t23, "")

rather than

if(a1:a9="Agent Name & ID: Ted Simpson - 1008", t23, "")
share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

Would you be willing to develop a VBA macro?

Did a quick search for what you may want and found this at TechGuy

Some useful links to read up on:

For what you want, you may want to use cell References, like a search box.

=if(search(value, cell)>0, true-value, false-value)

In your case

=if(search("1008", A1:A9)>0, t23, "")

Although, i would probably transform A1:A9 into a 1 column Table format or, at least, an expandable NamedRange so you can reference the Range instead of a defined Column/Row definition.

=if(search("1008", lstAgents)>0, t23, "")

Edited 2012-10-15 @ 15:21

Table/Range Design:

Name | ID | NameID
Ted Simpson | 1008 | =[@Name] & " - " & [@ID]

Now the Name & ID columns can be visually hidden and NameID column, and all others after, can be visual and not actively used, per say.

share|improve this answer
    
Oh and you will have to handle for the Error if Search() is not found. Which makes me want to go more towards the VBA Macro usage than incell formula method. –  GoldBishop Oct 3 '12 at 2:36
    
when I try to use the formula I get #VALUE! rather than result (2) –  Tom Oct 4 '12 at 13:00
    
I got it to work with out the quotes "" –  Tom Oct 4 '12 at 13:04
    
can you tell me if search works for a range of cells? if I point to a cell A21 that contains the 1008 the formula works but if I use a range A9:A28 I get the #VALUE! error –  Tom Oct 4 '12 at 13:32
    
can you give me some exaples on the named range or table format? –  Tom Oct 5 '12 at 15:23
show 1 more comment

You want to use the functions FIND or SEARCH, I guess.

Example, not tested (in excel 2010):

=IF(SEARCH("1008", L6:L17,1),t32,"")
share|improve this answer
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.