Fórmula genérica
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Resumo
Para dividir o texto em um delimitador arbitrário (vírgula, espaço, barra vertical, etc.), você pode usar uma fórmula baseada nas funções TRIM, MID, SUBSTITUTE, REPT e LEN. No exemplo mostrado, a fórmula em C5 é:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Nota: as referências a B5 e C4 são referências mistas para permitir que a fórmula seja copiada transversalmente e para baixo.
Explicação
A essência desta fórmula é substituir um determinado delimitador por um grande número de espaços usando SUBSTITUTE e REPT e, em seguida, usar a função MID para extrair o texto relacionado à "enésima ocorrência" e a função TRIM para se livrar do espaço extra.
Neste snippet, o delimitador (delim) é substituído por um número de espaços igual ao comprimento total da string:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Em seguida, a fórmula usa a função MID para extrair a enésima substring. O ponto de partida é calculado com o código abaixo, onde N representa "enésimo":
(N-1)*LEN(A1)+1
O total de caracteres extraídos é igual ao comprimento da string de texto completo. A função TRIM remove todos os espaços extras e retorna apenas a enésima string.
Extraia apenas uma instância
Embora o exemplo seja configurado para extrair 5 substrings do texto na coluna B, você pode extrair facilmente apenas 1 instância. Por exemplo, para extrair apenas o 4º item (cidade), você pode usar:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Recurso Texto para Colunas
Para conversões manuais pontuais, o Excel possui um recurso interno chamado "Texto para colunas", que pode dividir o texto em células com um delimitador de sua escolha. Você encontrará esse recurso na guia Dados da faixa de opções na seção Ferramentas de dados.
