Excel - Pesquisar lista de substrings dentro de grandes strings

Question

Eu tenho lutado com o seguinte; pode ser um desafio.

Eu tenho um arquivo do Excel que foi exportado de um aplicativo XML.

A folha 1 contém linhas de strings como as abaixo. Cada linha ímpar tem cerca de 10 dessas cadeias, mas isso pode variar. (Estou procurando uma solução para preencher as linhas pares, veja abaixo.)

A Folha 2 , Coluna A, contém um intervalo de strings como Measurement_1 , Ref_No e Maximum_Velocity , chamado Atributos .

Preciso pesquisar cada um desses atributos em cada célula de cada linha de numeração ímpar de Folha 1 e colar todos os atributos exclusivos que aparecem como substring nessas strings em uma nova linha abaixo, cada uma em uma célula própria.

Observe que todos os valores nas linhas geradas precisam ser exclusivos.

Existe algum script de função, regex ou VBA que possa fazer isso? Eu pesquisei extensivamente nos fóruns e em outros lugares e não consegui fazer isso sozinho.

Espero que tudo isso faça sentido, mas estou feliz em explicar qualquer coisa que seja rabugenta!

Linha 1

Cell A1:
<ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" - "></ref></ref>

Cell B1:
"<ref attrid="Ref_No" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Unit" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" "><ref attrid="Vector" equalsign="=" includeattrname="false" resolveto="value" separator=" (Dia)"><ref attrid="Object_Diameter" equalsign="=" includeattrname="false" resolveto="unit" separator=""><ref attrid="Thread_Size" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=","><ref attrid="Object_Length" equalsign="=" includeattrname="false" resolveto="value" separator=" "><ref attrid="Object_Length" equalsign="=" includeattrname="false" resolveto="value" separator=" of ">"

Cell C1:
CONCATENATE("This ", LOWER(VAL("Device_Type")),F(AND(EXACT(VAL("Max_Temperature_Range"),("")),EXACT(VAL("Min_Temperature_Range"),"")),".",CONCATENATE("appeared to operate safely from a minimum temperature of ", VAL("Min_Temperature_Range"),UNIT("Min_Temperature_Range")," to a maximum temperature of ", VAL("Max_Temperature_Range"),UNIT("Max_Temperature_Range"),".")))

A saída desejada na Linha 2 será semelhante a:

Cell A2:Measurement_1
Cell B2:Ref_No
Cell C2:Object_Diameter
Cell D2:Object_Length
Cell E2:Device_Type
Cell F2:Max_Temperature_Range
Cell G2:Min_Temperature_Range

    

Answer 1

Suas strings XML parecem estranhas - muitas tags incompletas?

De início, gostaria de iniciar a partir do arquivo XML e carregá-lo usando o suplemento de consulta de energia. Isso tem um manipulador "From XML" específico e você pode moldar facilmente os dados ao longo do caminho usando os comandos Power Query.

Aqui está um exemplo que construí usando uma versão editada do seu conteúdo XML (adicionei um elemento raiz e tags de fechamento para torná-lo um arquivo XML válido).

A única ação que pareceu necessária para conseguir o que você queria era selecionar a coluna Atributo: attrid e escolher Remover Colunas / Remover Outras Colunas.

Você pode ver o resultado em segundo plano - uma tabela do Excel. Sempre que o arquivo XML for alterado, você apenas pressionará Atualizar para executar novamente a Consulta de energia.