Looking up a string of text within a string of text

[tobriant]

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?

 

[dominicb]

Good afternoon tobriant

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

=SEARCH("first bank",A1)

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

HTH

Dominic

 

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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