No Excel, como substituo todas as ocorrências de texto em uma célula se algum texto corresponder a valores de um intervalo de células?

Question

Para facilitar as coisas para os usuários dessa pasta de trabalho, configurei a coluna J com validação de dados (obtendo valores da Planilha2) que permite várias seleções de tipos de eventos (Calendário Acadêmico, Exibição de Arte, etc.).

Eu tive que usar algum código para conseguir isso, mas encontrei um vídeo do YouTube que me acompanhou, então minha falta de experiência em codificação do Excel não me atrapalhou.

Para importar esta planilha para outro programa, não posso ter "Calendário Acadêmico" como o Tipo de Evento, preciso do código correspondente, "et-acacal". O código correspondente está na próxima coluna, na planilha que contém meus tipos de eventos.

Essencialmente, eu preciso substituir cada instância do "Calendário Acadêmico" por "et-acacal" e "Art Exhibits" por "et-artgal", bem como as várias combinações de tipos de evento como "Calendário Acadêmico, Exibição de Arte". "et-acacal, et-artgal"

Eu descobri uma maneira muito ruim de conseguir isso, usando várias funções SUBSTITUTE (veja abaixo), mas foi tedioso configurar e eu tenho que acreditar que há uma maneira mais fácil de fazer isso. Eu só tinha 30 tipos de eventos diferentes, o que acontece se eu tivesse 100 ou 200?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2,Sheet2!$A$30,Sheet2!$B$30),Sheet2!$A$29,Sheet2!$B$29),Sheet2!$A$28,Sheet2!$B$28),Sheet2!$A$27,Sheet2!$B$27),Sheet2!$A$26,Sheet2!$B$26),Sheet2!$A$25,Sheet2!$B$25),Sheet2!$A$24,Sheet2!$B$24),Sheet2!$A$23,Sheet2!$B$23),Sheet2!$A$22,Sheet2!$B$22),Sheet2!$A$21,Sheet2!$B$21),Sheet2!$A$20,Sheet2!$B$20),Sheet2!$A$19,Sheet2!$B$19),Sheet2!$A$18,Sheet2!$B$18),Sheet2!$A$17,Sheet2!$B$17),Sheet2!$A$16,Sheet2!$B$16),Sheet2!$A$15,Sheet2!$B$15),Sheet2!$A$14,Sheet2!$B$14),Sheet2!$A$13,Sheet2!$B$13),Sheet2!$A$12,Sheet2!$B$12),Sheet2!$A$11,Sheet2!$B$11),Sheet2!$A$10,Sheet2!$B$10),Sheet2!$A$9,Sheet2!$B$9),Sheet2!$A$8,Sheet2!$B$8),Sheet2!$A$7,Sheet2!$B$7),Sheet2!$A$6,Sheet2!$B$6),Sheet2!$A$5,Sheet2!$B$5),Sheet2!$A$4,Sheet2!$B$4),Sheet2!$A$3,Sheet2!$B$3),Sheet2!$A$2,Sheet2!$B$2)

    

Answer 1

A melhor maneira de gerenciar isso é usando a função VLOOKUP . Isso irá procurar o código do evento relevante para um nome de evento na célula J2.

=VLOOKUP(J2,Sheet2!$A$1:$B$999,2,0)

No entanto, você tem a complicação adicional de vários critérios na mesma célula. Para conseguir um VLOOKUP em múltiplos critérios na mesma célula, você precisaria substituir uma função SUBSTITUTE aninhada excessivamente complicada por outra - dado que é por isso que você está perguntando, eu não vou entrar em como isso funciona (muitos IF s, MID s e SEARCH es).

Sua melhor opção é tentar reorganizar sua planilha, para que você não tente incluir todos os seus critérios em uma única célula. O Excel não é realmente construído para fazer isso - a célula deve ser a unidade mais básica (daí o nome). Tente usar várias colunas e faça VLOOKUP s para cada uma delas.