Looking up a string of text within a string of text
 

Is it possible to look up a string of text within another string of
text, using some variation of vlookup? For example, I may need to find
a text string like "First Bank" within a column containing strings of
text that may be "The First Bank" or "My First Bank" or some variation
containing "First Bank." Preferably, the lookup would not be case
sensitive.

Any suggestions?


--
tobriant
------------------------------------------------------------------------
tobriant's Profile: http://www.excelforum.com/member.php...o&userid=25155
View this thread: http://www.excelforum.com/showthread...hreadid=469191

Good afternoon tobriant

You can use Find or Search, however you probably want the latter as it
is not case sensitive, and will return the number of characters at
which your string is first found, if your string isn't found then a
#VALUE! error is returned.

=SEARCH("first bank",A1)

will search for the string in cell A1. You can start searching from a
specified point in a string (which would come after the A1) but if not
specified then 1 is used.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=469191

The FIND() function will locate a substring within a string.

Let's say your text strings are in column A. In column B, enter
=FIND("Bank",A1,1)

the #VALUE! will indicate not found

FIND() is case sensitive.
--
Gary''s Student


"tobriant" wrote:


Is it possible to look up a string of text within another string of
text, using some variation of vlookup? For example, I may need to find
a text string like "First Bank" within a column containing strings of
text that may be "The First Bank" or "My First Bank" or some variation
containing "First Bank." Preferably, the lookup would not be case
sensitive.

Any suggestions?


--
tobriant
------------------------------------------------------------------------
tobriant's Profile: http://www.excelforum.com/member.php...o&userid=25155
View this thread: http://www.excelforum.com/showthread...hreadid=469191

On Tue, 20 Sep 2005 10:16:40 -0500, tobriant
wrote:


Is it possible to look up a string of text within another string of
text, using some variation of vlookup? For example, I may need to find
a text string like "First Bank" within a column containing strings of
text that may be "The First Bank" or "My First Bank" or some variation
containing "First Bank." Preferably, the lookup would not be case
sensitive.

Any suggestions?

This **array** formula will tell you which is the first item that matches your
criteria. You can then use that in an Index function to return the rest of the
data.

For example:

=INDEX(rng,MATCH(TRUE,ISNUMBER(SEARCH("first bank",rng))))

will return the entire string in which "first bank" was found. Similar
functions will return other columnar data.

The SEARCH function is case-insensitive.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron

On Tue, 20 Sep 2005 13:41:18 -0400, Ron Rosenfeld
wrote:

On Tue, 20 Sep 2005 10:16:40 -0500, tobriant
wrote:


Is it possible to look up a string of text within another string of
text, using some variation of vlookup? For example, I may need to find
a text string like "First Bank" within a column containing strings of
text that may be "The First Bank" or "My First Bank" or some variation
containing "First Bank." Preferably, the lookup would not be case
sensitive.

Any suggestions?

This **array** formula will tell you which is the first item that matches your
criteria. You can then use that in an Index function to return the rest of the
data.

For example:

=INDEX(rng,MATCH(TRUE,ISNUMBER(SEARCH("first bank",rng))))

will return the entire string in which "first bank" was found. Similar
functions will return other columnar data.

The SEARCH function is case-insensitive.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron

TYPO alert! Formula should be:

=INDEX(rng,MATCH(TRUE,ISNUMBER(SEARCH("last",rng)) ,0))


--ron