Fórmula genérica
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Resumo
Para dividir o texto com um delimitador e transformar o resultado em uma matriz, você pode usar a função FILTERXML com ajuda das funções SUBSTITUTE e TRANSPOSE. No exemplo mostrado, a fórmula em D5 é:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Observação: FILTERXML não está disponível no Excel no Mac ou no Excel Online.
Observação: aprendi esse truque com Bill Jelen em um vídeo do MrExcel.
Explicação
O Excel não tem uma função dedicada a dividir o texto em uma matriz, semelhante à função de explosão do PHP ou ao método de divisão do Python. Como solução alternativa, você pode usar a função FILTERXML, depois de adicionar a marcação XML ao texto.
No exemplo mostrado, temos várias strings de texto delimitadas por vírgulas como esta:
"Jim,Brown,33,Seattle,WA"
O objetivo é dividir as informações em colunas separadas usando a vírgula como delimitador.
A primeira tarefa é adicionar marcação XML a este texto, para que possa ser analisado como XML com a função FILTERXML. Faremos arbitrariamente cada campo do texto em um elemento, delimitado por um elemento pai. Começamos com a função SUBSTITUTE aqui:
SUBSTITUTE(B5,",","")
O resultado de SUBSTITUTE é uma string de texto como esta:
"JimBrown33SeattleWA"
Para garantir tags XML bem formadas e envolver todos os elementos em um elemento pai, adicionamos e acrescentamos mais tags XML como esta:
""&SUBSTITUTE(B5,",","")&""
Isso produz uma string de texto como esta (quebras de linha adicionadas para facilitar a leitura)
" Jim Brown 33 Seattle WA "
Este texto é entregue diretamente à função FILTERXML como o argumento xml, com uma expressão Xpath de "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath é uma linguagem de análise e "// y" seleciona todos os elementos. O resultado de FILTERXML é uma matriz vertical como esta:
("Jim";"Brown";33;"Seattle";"WA")
Como queremos uma matriz horizontal nesta instância, envolvemos a função TRANSPOSE em torno de FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
O resultado é uma matriz horizontal como esta:
("Jim","Brown",33,"Seattle","WA")
que se estende para o intervalo D5: H5 no Excel 365.
