Excel formula for searching part of text in a table
I have a table of banks and codes in two columns.
Ex.:
Banco do brasil S / A 001
State Bank of Rio Grande do Sul-Banrisul 041
Banco Santander do Brasil 033
Banco Mercantil do Brasil 208
Banco Itaú S / A 341
Some expressions are repeated as "Brazil", "Bank", "S/A". So I have to look for specific terms. Sometimes the specific term is in the middle of the name, such as "Rio Grande" or "Mercantile".
I can only use "PROCV" if I know the exact sequence from the beginning of the name, and I still run the risk of locating another bank with the same expression. If I know that at some point in the column there is a bank that has "Rio Grande" in the name, but I do not know how the rest of the name is written, How do I look in an entire column for an expression within the one text of each cell of the column?
1 answers
You can use the PROCV
with asterisks. Assuming the entered field is in cell A1 and its banks and codes table is in the range A4: B50, for example, the formula would look like this:
=PROCV("*"&A1&"*";A4:B50;2;FALSO)