Como posso pesquisar em uma célula com o VBA para localizar um texto específico e extraí-lo?

Question

Eu tenho uma coluna que pode conter células que possuem dados divididos por um | delimitador. Eu preciso ser capaz de olhar em cada célula e encontrar um texto específico e, se encontrado, extrair todo o texto entre os.

Por exemplo: a célula A2 tem blue story 1|red tree 2|orange flower 3|green car 4

Eu quero ser capaz de extrair red tree 2 usando a função InStr (se houver uma função melhor, por favor, conselho!)

Aqui está o código que tenho até agora:

Sub test()

Dim Field As Variant, rng As Range, cell As Range
Set rng = Range("A1:A5")

For Each cell In rng.Cells
    If InStr(cell.Value, "Red") > 0 Then
    Field = Split(cell.Value, "|")
    Cells(cell.Row, 2).Value = Field
    End If
Next

End Sub

Existe claramente um erro lógico porque gera os primeiros textos da | na matriz e não o real que contém "vermelho".

Edit: Eu preciso que seja capaz de encontrar "Red" em qualquer matriz e, se a saída que o array na coluna ao lado dele.

    

Answer 1

Você não está muito longe: Split () retorna uma matriz de strings baseada em 0 como esta:

Split Array (0): blue story 1
Split Array (1): red tree 2
Split Array (2): orange flower 3
Split Array (3): green car 6

para corrigir seu código, você precisa verificar cada valor na matriz:

Public Sub test()
    Dim fld As Variant, rng As Range, cell As Range, i As Long

    Set rng = Range("A1:A5")

    For Each cell In rng.Cells
        If InStr(1, cell.Value, "Red", vbTextCompare) > 0 Then
            fld = Split(cell.Value, "|")
            For i = 0 To UBound(fld)
                If InStr(1, fld(i), "Red", vbTextCompare) > 0 Then
                    Cells(cell.Row, 2).Value = fld(i)
                    Exit For
                End If
            Next
        End If
    Next
End Sub

Você também pode usar o texto em colunas ou uma fórmula como esta

=MID(A2,SEARCH("Red",A2,1),SEARCH("|",A2,SEARCH("Red",A2,1))-SEARCH("Red",A2,1))