Como posso analisar uma string de uma célula várias vezes no excel?

Question

Eu tenho uma coluna na minha folha de dados que geralmente contém uma string que quero colocar em outra coluna. Esta cadeia pode ocorrer várias vezes e eu quero colocar todas as instâncias dela na outra coluna. A coluna é delimitada e gostaria de levar a string correspondente e até o delimitador.

Um exemplo:

Possessions
Fruit: apple, Car: Ford, Fruit: banana,
Car: Saturn,
Fruit: orange,

Eu gostaria que a próxima coluna contivesse:

Fruit
Fruit: apple, Fruit: banana,

Fruit: orange,

É fácil encontrar a primeira instância da string (novas linhas são para facilitar a leitura):

MID(A2, 
    FIND( *first instance of Fruit:* ), 
    FIND( *first comma after Fruit:* ) - FIND( *first instance of Fruit:* )
    )

No entanto, eu poderia encontrar a string quantas vezes quiser e pegar todas elas.

Além disso, a coluna já é um campo calculado (uma referência a outra planilha), portanto não posso usar texto em colunas para dividir no delimitador.

Alguma idéia de como retornar todas as instâncias da string? Eu prefiro evitar um script VBA se possível e usar funções de planilha, mas se não for possível com funções, estou aberto ao VBA.

    

Answer 1

Solução genérica do VBA com expressões regulares

Este método deve cobrir suas necessidades. Ele também pode ser usado por outros usuários para extrair várias strings de uma determinada string com a ajuda de expressões regulares

1. AbraseueditordeVBA(ALT+F11)
2. Insiraumnovomódulo(!)ecoleocódigoabaixonele

3. VolteparaoExceleuseessafórmulaemumacélulaemquevocêdesejasuasaída

   =REGEXTRACT(A1,"Fruit: .*?,")
   

Explicação da fórmula

• =REGEXTRACT() é sua nova fórmula personalizada.
• A1 é a célula onde seus dados de entrada são colocados
• Fruit: .*?, é uma expressão regular para encontrar todas as ocorrências de fruit e corresponde até a próxima vírgula.

Function REGEXTRACT(objCell As Range, strPattern As String)

    Dim objMatches As Object
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")

    RegEx.IgnoreCase = True
    RegEx.Global = True
    RegEx.Pattern = strPattern

    Set objMatches = RegEx.Execute(objCell.Value)

    If objMatches.Count <> 0 Then
        For Each objMatch In objMatches
            REGEXTRACT= REGEXTRACT+ objMatch.Value
        Next objMatch
    Else: REGEXTRACT= ""
    End If

End Function

As expressões

Dica: Look-Behind e Look-Ahead não são suportadas em Mecanismo regex do VB. Portanto, não é trivial excluir a vírgula via RegEx. Mas é possível através de operações com strings VBA normais.

    

Answer 2

Isso é ridiculamente desajeitado, mas parece funcionar. Eu recomendaria uma solução VBA para ser honesta.

=TRANSPOSE(LEFT(MID(A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))),IFERROR(FIND(",",A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))))-1,LEN(A1)-FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit")))))+1)),IFERROR(FIND(",",MID(A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))),IFERROR(FIND(",",A1,FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit"))))))-1,LEN(A1)-FIND("~~~~~~",SUBSTITUTE(A1,"Fruit:","~~~~~~",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"Fruit","")))/LEN("Fruit")))))+1))),1000)-1))

É uma fórmula de matriz e você precisa selecionar pelo menos tantas células quantas forem as ocorrências de "Frutas". Portanto, se você tiver quatro frutas em uma coluna, selecione quatro células, insira essa fórmula e confirme com ctrl + shift + enter. Se você seleciona células extras, ele começa a retornar um erro no final, então você pode, em teoria, selecionar o número máximo de células que você precisará para qualquer coluna e fazer o autofill.

Edit: Eu percebi que, se houver apenas uma fruta, ela retornará essa fruta em cada célula. Eu não acho que há uma maneira de contornar isso com esta fórmula.

Também presume que você não terá um nome de fruto com mais de 1000 caracteres e que "~~~~~" não aparecerá em seu texto normalmente.

Explicação: Onde n é o número de Frutos na célula, a fórmula usa FIND / SUBSTITUTE para construir uma matriz de n elementos onde a primeira ocorrência de "Fruta" é substituída por "~~~~~", então a segundo ... etc. Então ele usa MID / FIND para extrair o texto. Como o Excel não tem uma fórmula de "encontrar nth ocorrência de string x", nos encontramos fazendo coisas malucas como essa. O IFERROR está lá para a última fruta, desde que eu estava assumindo que não pode haver uma vírgula depois.