No excel 2010, substitua qualquer número correspondente ao padrão pelo valor de pesquisa

Question

Estou tentando descobrir uma maneira de substituir / substituir / regex um padrão específico em uma tabela do Excel com o valor correspondente de outra tabela. O padrão, que aparece x vezes dentro de um bloco de texto, segue sempre estas regras:

{[4,9]{1}[0-9]{5}} (entre chaves, um "4" ou um "9" seguido por 5 números, cada um dos quais pode ser qualquer coisa entre 0 e 9, como "{412345}" ou "{961723}" .)

A fórmula desejada deve então retirar os colchetes, procurar o número em outra tabela e retornar a entrada correspondente de outra coluna.

Consegui fazer isso com quatro números predefinidos:

Noentanto,precisoexpandirafórmulaparaincluirqualquernúmeroquecorrespondaaopadrão.EusuponhoqueumUDFVBAéocaminhoapercorrer,emboraeunãotenhaamenoridéiadecomofazerisso...

PS:sealguémestiverinteressadoemaumentarumpoucomaisoslimites,opróximopassocomoqualvouterquelidaréadicionar.Xaosnúmerosdentrodaschaves,ondeXrepresentaatabeladentrodaqualosvaloresdevemserverificados.Porexemplo:"{412345.2}" significaria "marque 412345 na tabela 2 e substitua pelo valor encontrado", e "{912345.5}" deveria ser "marque 912345 na tabela 5 e substitua pelo valor encontrado".

    

Answer 1

Usando macros / VBA:

Public Function patSub(theValue As String) As String
    result = theValue
    Dim rango As Range
    Set rango = ActiveSheet.Range("E1:F6")
    strPattern = "{[4,9]{1}[0-9]{5}}"
    Dim regEx As New RegExp
    Dim matches
    With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
    End With
    Set matches = regEx.Execute(theValue)
    For Each Match In matches
        On Error Resume Next
        lookupmatch = Match.Value
        lenMatch = Len(lookupmatch)
        lookupmatch1 = Mid(lookupmatch, 2, lenMatch - 2)
        lookupValue = Application.VLookup(lookupmatch1 + 0, rango, 2, Falso)
        If lookupValue <> "Error 2042" Then
            result = Replace(result, lookupmatch, lookupValue)
        End If
    Next
    patSub = result
End Function

Abra VBA / Macros com alt + F11, insira um novo módulo em ThisWorkbook e cole o código no lado direito.

Para fazer com que o Regular Expressions funcione com o VBA, é necessário fazer referência a Microsoft VBScript Regular Expressions 5.5 :

• Selecione "Ferramentas / Referências"
• Marque a caixa ao lado de Expressões regulares do Microsoft VBScript 5.5
• Clique em "OK"

Esta UDF usa duas variáveis:

• rango1 : A tabela de referência que contém os itens.
• strPattern : a expressão regular.

Se os dados estiverem na célula A1 , então, em B1 , você precisará colocar =patSub(A1) .