Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Remove text leading zero in text string | Excel Worksheet Functions | |||
Finding Specific Text in a Text String | Excel Worksheet Functions |