Excel formula to search part of the text in a table

Asked

Viewed 29,901 times

3

I have a table of banks and codes in two columns.

Ex.:

Banco do brasil S/A 001

Banco do Estado do Rio Grande do Sul - Banrisul 041

Banco Santander do Brasil 033

Banco Mercantil do Brasil 208

Banco Itaú S/A 341

Some expressions repeat themselves as "Brazil", "Bank", "S/A". So I have to look for specific terms. Sometimes the specific term is in the middle of the name, as "Rio Grande" or "Mercantil".

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 database that has "Rio Grande" in the name, but I don’t know how the rest of the name is written, how do I search in an entire column an expression within the text of each cell of the column?

excel quest reference fórmula procv

  • Can’t find the code?

  • It is a spreadsheet of filling several data.

  • One of them is the Cód. of the bank. but I had to create a side screen where the user can search the code by the name of the bank, in case he doesn’t know the color code.

1 answer

2


You can use the PROCV asterisked. Assuming that the typed field is in cell A1 and its table of banks and codes is in the range A4:B50, for example, the formula would look like this:

=PROCV("*"&A1&"*";A4:B50;2;FALSO)

Resultado (meu Excel está em inglês)

  • 1

    I didn’t know the asterisk feature. Required!

Browser other questions tagged excel quest reference fórmula procv

You are not signed in. Login or sign up in order to post.