I need to shorten text search

Question

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, "")

Answer 1

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:

• Search, SearchB - Microsoft Office
• Find, FindB - Microsoft Office

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.

Answer 2

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

Example, not tested (in excel 2010):

=IF(SEARCH("1008", L6:L17,1),t32,"")