Eu preciso substituir os últimos 3 caracteres do conteúdo de uma célula com base no valor em outra célula

Question

A coluna 1 contém valores como os seguintes:

AAA
AAB
AABAAA
AABAAB
AABAAC
AABAACAAA

A coluna 2 contém números como 1, 2, 3, 4, 5, 6

Eu preciso alterar os 3 últimos caracteres da coluna 1 com base no valor da coluna 2

A regra é 1 = AAA, 2 = AAB, 3 = AAC, 4 = AAD, etc .....

Por exemplo: se a Coluna 1 contiver AABAAC e o valor na Coluna 2 for 1, preciso alterar os 3 últimos caracteres da Coluna 1 para AAA. O resultado para a coluna 1 seria então AABAAA

    

Answer 1

Existem algumas maneiras de fazer isso ... mas eu recomendaria ter uma folha de pesquisa em algum lugar, caso você precise modificar os valores aos quais os números correspondem:

Na sua coluna 1, o valor deve ser algo assim:

="AAA AAB AABAAA AABAAB AABAAC AABAAC" & LOOKUP(B1,Sheet2!A$1:A$9999,Sheet2!B$1:B$9999)

Na folha 2, você cria algo para a fórmula para pesquisar assim:

... etc, até que você atinja quantos valores quiser consultar.

Eu acho que esse é o método mais fácil ... mas alguns métodos alternativos podem incluir a função CHAR (), que pode pegar o valor numérico, adicionar 40 e fazer o caractere sair do AZ ... desde que você use o numérico valores 1-26 ... Ou você poderia ir a rota mais extrema e criar uma função no VBA que renderizaria o que você considerasse adequado. O último método provavelmente não é a melhor idéia, já que você teria que passar por mais alguns aros para fazê-lo funcionar.

    

Answer 2

O que você quer dizer com "mudar"? É bom o suficiente para criar uma nova coluna que contém os valores manipulados? Em caso afirmativo, configure a tabela de consulta como descrito por TheCompWiz , defina C1 para

=LEFT(A1, LEN(A1)-3)  &  VLOOKUP(B1, Sheet2!A$1:B$9999, 2)

e arraste / preencha. LEFT( X , LEN( X ) - 3) é todo X , exceto pelos 3 caracteres mais à direita e & é concatenação de string.

A pergunta diz:

The rule is 1 = AAA, 2 = AAB, 3 = AAC, 4 = AAD, etc...

Isto parece uma espécie de notação de base 26. Eu acho que 26 = AAZ, 27 = ABA, 260 = AJZ, 261 = AKA, 676 (26 × 26) = AZZ, 677 = BAA, etc. Coincidentemente, isso se parece muito com o modo como o Excel rotula as colunas. Se você tem uma versão suficientemente atual do Excel (2007? 2010?), Você pode obter isso com a fórmula

LEFT(ADDRESS(1, 26*26 + 26 +n, 4), 3)

onde ADDRESS é a função para transformar as coordenadas em um endereço de célula. Por exemplo, ADDRESS(4, 2, …) retorna B4 e ADDRESS(4, 27, …) retorna AA4 . O terceiro parâmetro especifica se é necessário usar o endereçamento absoluto ou relativo; 4 significa que linha e coluna são relativas. Então ADDRESS(1, 26*26 + 26 + 1, 4) é AAA1 , e LEFT( string , 3) retorna os primeiros três caracteres (mais à esquerda) de string . Obviamente, você deve conectar B1 para n .

P.S. Você precisa da versão atual do Excel porque no Excel 2003 e versões anteriores (e não tenho certeza sobre 2007), uma planilha pode ter no máximo 256 colunas, e a função ADDRESS não deixará você gerar o nome de uma célula que não pode existir (ou seja, um para o qual o número da coluna > 256).