Folhas de cálculo: como consolidar várias colunas numa

Um problema que volta e meia ocorre é como criar listas a partir de texto como este:

Relatório 1: Localidade1, Localidade2 Relatório 2: Localidade1 Relatório 3: Localidade2, Localidade 3, Localidade 4 ...

Podemos copiar isto para uma folha de cálculo como o Excel ou o Google Sheets, dividir o texto em colunas por ":", para nos livrarmos do texto "Relatório x".

Contudo, depois ficamos com algo assim:

Ou, se transpusermos:

Há muito tempo que procurava uma forma de transformar isto rapidamente numa coluna só na própria folha de cálculo, sem introduzir macros (depois a folha não pode ser partilhada tão facilmente com outras pessoas) ou sem processar com código o texto de origem (que por vezes está malformado e requer muitas exceções particulares, mais rápidas de detetar na folha de cálculo).

Pois bem, hoje o Bing Chat resolveu num instante. E funciona, aqui está em versão Excel de Portugal:

=SE.ERRO(ÍNDICE($A:$C;RESTO(LIN()-LIN($D$1);MÁXIMO(CONTAR.VAL($A:$A);CONTAR.VAL($B:$B);CONTAR.VAL($C:$C)))+1;INT((LIN()-LIN($D$1))/MÁXIMO(CONTAR.VAL($A:$A);CONTAR.VAL($B:$B);CONTAR.VAL($C:$C))+1));"")

No Excel em inglês:

=IFERROR(INDEX($A:$C,MOD(ROW()-ROW($D$1),MAX(COUNTA($A:$A),COUNTA($B:$B),COUNTA($C:$C)))+1,INT((ROW()-ROW($D$1))/MAX(COUNTA($A:$A),COUNTA($B:$B),COUNTA($C:$C))+1)),"")

A explicação vem a seguir. Vem em inglês, porque quando tento que ele use fórmulas em português faz misturas entre as fórmulas das versões de Excel de Portugal e do Brasil: