Preciso extrair os últimos 7 valores numéricos (não caracteres) de uma string em uma célula do Excel. A string contém um nome de arquivo (que pode ser uma mistura de letras, números e um travessão) e depois 7 números que precisam ser movidos para outra coluna.
por exemploJohnson Set1- 0 0 1 14 9 54 0
Os valores numéricos variam de 0 a 999, portanto não é possível extrair apenas os últimos 7 caracteres.
Responder1
Portanto, existem alguns truques que você pode usar para conseguir o que deseja. Farei o meu melhor para explicá-los aqui e depois lhe darei uma fórmula que usa esses truques para dar o que você deseja.
A chave é encontrar o espaço antes do início da lista de números. Se o seu exemplo for geral o suficiente, este espaço será sempre o 7º espaço a partir da direita. O problema é que o 7º a partir da direita pode ser qualquer espaço numérico a partir da esquerda, porque parece que não há regras sobre o nome do arquivo ter um número definido de espaços. E a SUBSTITUTEfunção permite especificar qual instância (da esquerda) de uma string substituir.
Para obter o 7º espaço à direita, temos que traduzi-lo em espaços à esquerda. Para fazer isso, precisamos saber o número de espaços em toda a string. Assim que tivermos isso, é apenas uma questão de subtração.
Para encontrar o número total de espaços na string, existe um truque usando as funções SUBSTITUTEe . LENA ideia é substituir todos os espaços da string por nada e então comparar o comprimento da saída com o comprimento da string original. A diferença é, obviamente, o número de espaços na string original. Portanto, para uma string in A1, o seguinte retornará o número de espaços:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Portanto, a instância numérica do espaço à esquerda que você deseja sempre terá mais 6 espaços à sua esquerda.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6
Agora, o próximo problema é que só SUBSTITUTEpermite especificar o número da instância que deseja alterar. FIND, que seria mais conveniente para extrair os números, não possui esse recurso. O truque então é usar SUBSTITUTEpara gerar uma string que permita usar FINDcomo quiser. Se você substituir o sétimo espaço da direita por uma string que não ocorre em nenhum outro lugar da string, você terá uma substring exclusiva para procurar. Então, por exemplo, você pode usar o seguinte para substituir o espaço desejado por "REPLACE!ME":
=SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6)
Agora, basta encontrar o índice de "REPLACE!ME" na string e retornar tudo para a direita. Isso pode ser feito com as funções RIGHT, LENe . FINDA função final é meio repetitiva, mas dá conta do recado.
=RIGHT(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6),LEN(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-FIND("REPLACE!ME",SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-LEN("REPLACE!ME")+1)