Transformar uma string que está em uma célula, em um array de intervalos de linhas e colunas, de alguma forma?

Se você quiser a string de texto, use:

="={"""&TEXTJOIN(""",""",,SUBSTITUTE($A$2:$A$5," ","""/""")) & """}"

Se você quiser uma matriz real que será usada em outro lugar, use:

IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TRANSPOSE($A$2:$A$5)," ","</b><b>")&"</b></a>","//b["&ROW($A$1:$A$4)&"]"),"")

Isso criará uma matriz 4x4 que pode ser usada em outra fórmula. Dependendo da versão, usar isso em outra fórmula exigirá o uso de Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição para forçar a fórmula de matriz.

Não conheço nenhuma maneira de fazer o que você deseja F9e mostrar a matriz na barra de fórmulas.

Bem, a string literal, menos o erro de digitação "JONH" que eu simplesmente não vou replicar, pode ser obtida com a fórmula de Scott Craner ligeiramente ajustada (e eu usei SEQUENCE()em vez do ROWS()truque, tenho que usar as novas funções para me acostumar eles, hein?), então usado como base para algum trabalho de string:

="={"""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(B234:B237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(C234:C237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(D234:D237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(E234:E237),SEQUENCE(4)))&"""}"

Lembre-se de que, embora forneça a saída literal desejada, a saída NÃO é uma constante de matriz da perspectiva do Excel e NÃO será usada por ela exatamente como F9seria o resultado.

É apenas uma string de texto para todos os cuidados do Excel. Portanto, usá-lo dentro de uma fórmula provavelmente resultará em falha ou sucesso por puro acaso. Talvez funcione bem também, para alguns usos. Mas não é de forma alguma o mesmo ou equivalente ao Excel como a constante de matriz de uso interno destacando algo e pressionando F9cria.

Dito isto, é feio e inchado. Eu não sigo o tópico para frente e para trás nos comentários, eu acho, então não vejo por que Scott Craner usou TRANSPOSE(), o que me forçou a fazê-lo também, pois parece que a saída desejada é da esquerda para a direita (comente acima com "A234 = " Jonh",...) retirando-o e ajustando seu A2:A5 para A1:A4 (ou descarta uma linha para meus experimentos), obtenho:

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(($A$234:$A$237)," ","</b><b>")&"</b></a>","//b["&SEQUENCE(1,4)&"]"),"")

por sua fórmula e retrabalhando a minha, então misturando a dele para usar uma única fórmula, obtenho:

="={"""&TEXTJOIN("""\""",B234:E234)&""","""&TEXTJOIN("""\""",B235:E235)&""", """&TEXTJOIN("""\""",B236:E236)&""","""&TEXTJOIN("""\""",B237:E237)&"""}"

que, a propósito, como é padronizado, repetitivo na construção, pode-se usar os truques antigos CONCATENATE()ou dos union operatordias para construí-lo imensamente com linhas e colunas se ajustando bem antes de finalmente concatenar, colar especial como o valor e, em seguida, um "=" colocado antes dele para torná-lo uma fórmula. Ainda mais fácil agora. Para dados que não estão em quatro linhas pequenas e menos de cinco elementos.

Então, isso fornece a string resultante desejada expressa, embora, novamente, não seja um resultado que o Excel veria como sendo o mesmo que a constante de matriz que você vê depois de usar F9conforme mencionado.

E a fórmula alterada de Scott Craner daria o resultado da célula individual descrito no comentário. Que poderia ser usado dentro de uma fórmula para permitir que o Excel faça a criação dessa constante de matriz interna. Mas NÃO, eu acho, usando a fórmula de string (que é um beco sem saída, calculando), mas sim usando a fórmula de Scott Craner (ajustada) dentro de qualquer fórmula que você precise disso em so Excel will evaluate it to the desiredconstante de matriz e, em vez de preencher essas 16 células, passe-o pela cadeia de cálculo dentro dessa fórmula.

Acabei de perceber que é "Scott Tenorman" (ou próximo) em South Park, aquele de quem Cartman ... se vinga ... Não estou mais vagamente incomodado em dizer "Scott Craner" repetidamente!