Como fazer uma comparação parcial de strings no Excel?

Question

Eu tenho duas colunas no Excel. Ambas as colunas possuem uma lista de strings com o sufixo de um caractere aleatório. O formato do prefixo é de duas strings anexadas por um sublinhado, e o sufixo também é adicionado com um sublinhado.

ou seja

ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a") HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"

Como posso comparar se o prefixo na coluna B está na coluna A?

Editar: eu sei que posso fazer isso dividindo a string em três seções e, em seguida, combinando as duas primeiras seções em uma coluna e, em seguida, verificando se a coluna está na outra com um VLOOKUP. Mas eu estava procurando uma função oneliner.

Answer 1

Suponho que nas células A1 e B1 você tenha:

E na coluna C tem um TRUE(VERDADEIRO) ou FALSE(FALSO) que retorna se o prefixo é igual ou não. (desculpe pela foto em português)

A fórmula que criei é:

=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))

---

Explicação:

=EXACT(str1,str2)

Ele compara se as strings str1 e str2 são iguais ou não. Devemos extrair o prefixo das colunas A e B. Para isso, usamos:

=LEFT(text, [num_chars])

Ele extrai [num_chars]caracteres da string text. Para saber quantos caracteres devemos extrair, substituímos o segundo sublinhado _por um caractere fictício \:

=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)

O truque aqui é o argumento opcional [instance]. Definimos como 2, para substituir a segunda ocorrência do caractere sublinhado.

=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)

Com esta fórmula, encontramos a posição do personagem fictício \. Juntando tudo, obtemos a fórmula acima.

Em etapas, se desagregarmos a fórmula, teremos:

Answer 2

Li sua pergunta como se quisesse saber, para cada string da coluna B, se seu prefixo é encontrado em algum lugar da coluna A. Portanto, esta resposta resolve esse problema.

Não consegui colocar em uma linha (porque tem que procurar em todos os lugares da Coluna A). Mas isso requer apenas uma coluna extra.

Primeiro, uma fórmula para extrair o prefixo de qualquer string:

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

• Delimiteré o caractere "_"
• A chamada interna FIND()encontra a localização do primeiro sublinhado. Em seguida, ele usa isso como ponto de partida para FINDa localização do segundo sublinhado.
• A LEFT()função retorna um número de caracteres começando à esquerda da string. Portanto, subtraímos 1da localização do segundo sublinhado e obtemos o prefixo da string.

Então você usa isso para obter uma lista de cada prefixo da coluna A. Em seguida, você usa uma fórmula de matriz para verificar se cada prefixo da Coluna B está presente na lista de prefixos da Coluna A.

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

• Prefix_Aé a lista completa de prefixos da coluna A.
• Esta é a mesma fórmula para extrair prefixos aplicada à Coluna A
• Precisa ser uma fórmula de matriz porque a instrução dentro da OR()função retorna uma matriz de valores TRUEeFALSE

Aqui está uma captura de tela da planilha que criei para responder a isso:

Usei o FormulaChop para gerar essas fórmulas de exemplo (divulgação completa: escrevi o FormulaChop).Aquié uma captura de tela da saída do FormulaChop para a primeira fórmula.Aquié um link para a planilha que criei para responder a essa pergunta.