Tenho endereços em uma célula e quero extraí-los em células diferentes na mesma linha. Algumas células têm quatro linhas de endereço e outras três. Consigo dividir facilmente usando texto em coluna e vários delimitadores para aqueles com três, mas não para aqueles com quatro.
insira a descrição da imagem aqui
No primeiro exemplo tenho quatro linhas e o segundo tem três
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage, AK 99508 US
Eu gostaria que o texto acima fosse dividido em 5 células. Uma célula para endereço, cidade, estado, CEP e país
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage
AK
99508
US
no segundo exemplo abaixo
Providence Alaska Medical Center
3200 Providence Drive
Anchorage, AK 99508 US
Gostaria
Providence Alaska Medical Center
3200 Providence Drive
Anchorage
AK
99508
US
Isso poderia ser feito usando uma fórmula?
Obrigado
Responder1
assumindo que o texto "Anchorage, AK 99508 US" esteja localizado em A1, insira estas fórmulas.
A4 --> =MID(A1,1,(FIND(",",A1,1))-1)
B4 --> =MID($A$1,(FIND(" ",$A$1,A3))+1,C3-(FIND(" ",$A$1,A3))-1)
C4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))-(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))-1)
D4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))+1,LEN(A1))
key: find() foi usado para identificar o valor inicial/final de mid(). aqui encontramos cada "," & " " no texto A1.
Responder2
Ou... você poderia adotar uma abordagem bem diferente. Dois, na verdade.
Uma delas é usar a FILTERXML()técnica. Começa-se pelo trabalho de dividir seus dados em duas partes, o "resto" e a última linha. O "resto" vai para a primeira célula de saída. (Mostrarei abaixo uma maneira bastante fácil de dividir as partes.) A última linha é trabalhada como uma string para substituir o espaço-vírgula e os espaços por tags HTML ( </Something><Something>) transformando parcialmente a string em uma string HTML, então tem a tag de abertura anexada e a tag de fechamento anexada, adicionando ainda as tags HTML externas envolvendo tudo isso. Esta é a entrada real da FILTERXML()função e sua saída são as quatro células que contêm as informações da última linha.
Isso é mais fácil do que parece e se torna pré-fabricado depois de usado algumas vezes, se próximo do tempo. O principal esforço é dividir os dados em partes e prepará-los. Isso ocorre porque cada novo uso parece ter dados empacotados de forma diferente.
Uma vantagem, além da natureza pré-fabricada após a prática, é que ela produz uma saída de array "naturalmente" em vez de precisar {CSE}de entrada ( Ctrl-Shift-Enter). Outra é que você pode selecionar os elementos de saída por número ou com [Last](e esse elemento do endereçamento pode ser construído com técnicas de string) para que você possa extrair dados posicionais exatos, se puder especificá-los.
Uma abordagem diferente é usar um Excel 4 Macrocomando ("E4M") chamado EVALUATE(). Você não pode usá-lo diretamente em uma fórmula de célula, mas deve criar um intervalo nomeado para mantê-lo. Você pode criar o que apresenta nas células ou organizar tudo em uma única fórmula e colocá-la dentro da função no intervalo nomeado. O que funciona melhor para você ou se adapta melhor às suas tendências.
Em qualquer caso, como acontece com FILTERXML(), você deve preparar a entrada. A diferença aqui é que as coisas que você faz dessa maneira são muito familiares e pequenas coisas que dão errado, deixando de fora uma vírgula ou cometendo um erro de digitação, parecem “naturais” e são relativamente fáceis de encontrar. Com FILTERXML()tudo parece "errado" e você, por um tempo, não terá familiaridade com a aparência da saída, então os erros são mais fáceis de cometer e mais difíceis de encontrar. Vantagem, EVALUATE().
Então, como preparar os dados? Você precisa de dois pedaços: a primeira parte (que chamei de "resto" acima) que você apenas extrairá e apresentará, e a parte que deseja que seja trabalhada de maneira especial. Então, na última parte, você criará uma string que se parece com o tipo de array que o Excel produz internamente em suas fórmulas. Um que seria exibido em uma linha poderia ter a mesma aparência {"a","b",1,"K"}e os mesmos dados criados para exibir uma coluna seriam semelhantes {"a";"b";1;"K"}e, claro, eles podem ser misturados para serem exibidos em um intervalo de colunas e linhas. Você deseja criar um como o último, com ponto e vírgula como delimitadores.
Mas você não tem um único delimitador na última linha. Você tem "," e "". Então, primeiro, você pega esse pedaço e coloca-o SUBSTITUTE()para transformar o ", " ("espaço-vírgula") apenas no espaço. Agora você tem apenas um delimitador e pode alterá-lo, com SUBSTITUTE(), para ";" isto que você precisa. Você também pode aplicar a maioria das aspas duplas necessárias em todos os elementos usando ";"em vez de apenas ;. Na verdade, como o Excel torna bastante desagradável o uso de aspas duplas como elementos de fórmula, usá CHAR(34)-las é muito mais fácil. "A maior parte" porque você só pode adicioná-los entre os elementos, SUBSTIITUTE()deixando as aspas duplas de início e fim ainda necessárias. Então você os adiciona nas cordas de abertura e final junto com as chaves apropriadas. Agora você tem um barbante adequado, que EVALUATE()pode rasgar.
Construa isso em uma célula e, quando funcionar, crie o intervalo nomeado e coloque tudo entre EVALUATE()parênteses. Você verá na fórmula abaixo como fica. Não está claro como sua saída ficará em relação aos dados de entrada, então usei apenas as duas linhas abaixo de uma entrada, então insira em A1, produza em A2 e A3. Você pode ajustar conforme desejado. Então, dentro do intervalo nomeado:
=EVALUATE("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND(CHAR(10),A1,SEQUENCE(1,LEN(A1))),""))),", "," ")," ",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")
SEQUENCE()pode estar disponível quando a pergunta foi escrita. Talvez não. Usei-o para simplificar, pois, afinal, ESTOU escrevendo para pessoas que pesquisam o problema, mas desejam uma solução atualizada. No entanto, existiam substitutos para ele na época, o comum usando ROW(1:xxx)formas simples ou complexas.
"No dia", pode ter sido necessário inserir com {CSE}entrada.
Faz todas as coisas já comentadas.
O pequeno elemento aqui é como dividir a entrada. Cada problema é diferente, mas o seu não é muito, muito difícil. Cada linha na célula de entrada termina com Alt-Enterou CHAR(10). Portanto, você pode contá-los usando a técnica "comprimento do conteúdo menos o comprimento do conteúdo após SUBSTITUTE()removê-los". Então você envolve isso a FIND()que faz algo que normalmente não é feito: ele usa o parâmetro "número de posição" (terceiro) para pegar uma matriz de valores de 1 até o comprimento da entrada, para que você tenha uma matriz de FIND()cada um procurando um coloque mais na entrada. Isso produz uma matriz de valores da próxima ocorrência à medida que passa pela entrada com erros no final, já que há coisas depois da última. Não é possível usar esses erros, então IFERROR()apague-os. O número mais alto encontrado é o ponto de ruptura da entrada, então use MAX()para gerar isso. Então você faz duas fórmulas, uma LEFT()com tantos caracteres para ficar mais fácil, basta colocar no lugar as primeiras linhas (quantas, 3,4,5, o que quer que seja). Então, um RIGHT()da entrada do comprimento da entrada menos esse ponto de ruptura coleta o resto. Essa é a entrada para a EVALUATE()função.
Assim como com FILTERXML(), tudo isso é realmente muito fácil, já que a massagem do material tem que ser feita de qualquer maneira que você vá. Então ele simplesmente mergulha EVALUATE().