Analisar dados de texto formatados de forma inconsistente usando o Excel

Question

Estou com dificuldade em analisar o texto de uma planilha. Eu tenho uma planilha com centenas de registros parecidos com o exemplo abaixo (isso é tudo em uma célula):

7431340 03 POOLS E TECHNOLOGIES INC 90 NEW ENGLAND DRIVE 16 0.00 6,900 7,060

Eu preciso analisar cada um dos seguintes itens em células separadas:

[7431340] [03 POOLS E TECHNOLOGIES INC] [90 NEW ENGLAND DRIVE] [7,060]

ou como apareceria em uma planilha do Excel

O problema que estou tendo é que os dados são inconsistentes para cada registro. Mais exemplos:

7170258 111 HARBOR POINT OWNER LLC 115 TOWNE STREET 16 0.00 189,200 196,730
4469000 4 KIDZ INC 62 SOUTHFIELD AVENUE 16 0.00 3,000 9,500
6369875 3 HERMANOS LLC 912 EAST MAIN STREET 16 0.00 4,640 4 640

Eu gostaria de evitar o máximo de manipulação manual possível, mas temo que possa estar sem sorte.

Como faço para analisar os dados quando o formato não é consistente?

    

Answer 1

Se os seus exemplos forem realmente representativos, existe um padrão que pode ser extraído usando Expressões Regulares. Isso pode ser implementado no Excel usando o VBA.

Padrão:

• Início da string
• Série de dígitos seguida por um espaço
• Substring que começa com um ou mais dígitos e não contém outros dígitos
• um segundo Substring que começa com um ou mais dígitos e não contém outros dígitos
• vários grupos de vírgula de dígitos separados por espaços, com uma vírgula opcional (retorne o último)

Todos os seus exemplos mostram esse padrão. Aqui está uma função definida pelo usuário que irá lidar com isso:

Option Explicit
Function extrAddressPart(sAddr As String, lPart As Long)
    Dim RE As Object, MC As Object
    Const sPat As String = "^(\d+)\s+(\d+\D+)\s+(\d+\D+)\s+.*\s+([\d,]+)$"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = sPat
    .MultiLine = True
    .Global = False
    If .Test(sAddr) = True Then
        Set MC = .Execute(sAddr)
        extrAddressPart = MC(0).submatches(lPart - 1)
    End If
End With
End Function

É claro que, se o seu exemplo não for verdadeiramente representativo, essa solução não funcionará.

    

Answer 2

Como você está lidando com Delimitadores Múltiplos, nesse caso, gostaria de sugerir duas possíveis soluções.

Solução 1 (macro VBA):

Function ReplaceAndSplit(ByRef Text As String, ByRef DelimChars As String) As String()
    Dim DelimLen As Long, Delim As Long
    Dim strTemp As String, Delim1 As String, Arr() As String, ThisDelim As String
    strTemp = Text
    Delim1 = Left$(DelimChars, 1)
    DelimLen = Len(DelimChars)
    For Delim = 2 To DelimLen
        ThisDelim = Mid$(DelimChars, Delim, 1)
        If InStr(strTemp, ThisDelim) <> 0 Then _
            strTemp = Replace(strTemp, ThisDelim, Delim1)
    Next
    ReplaceAndSplit = Split(strTemp, Delim1)
End Function

Como funciona:

1. Insira este código como um módulo junto com o Folha.

2. Escreva esta fórmula na célula do desejo e preencha-a Até que seja necessário, pressione F2 e termine com Ctrl+Shift+Enter .

   {=ReplaceAndSplit(A2,",")}

Nota:

1. "," é para Comma como Delimitador.
2. Deve ser " " para Space .
3. "[ ]" para Brackets .

Solução 2 (fórmula do Excel):

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",256)),(COLUMNS($A:A)-1)*255+1,255)),"]","")

Nota:

Você precisa editar esta parte da fórmula de acordo com o Delimitador antes de executá-la, por exemplo,

1. SUBSTITUTE($A1,"," para Comma como Delimitador.
2. SUBSTITUTE($A1," " para Space como Delimitador.
3. SUBSTITUTE($A1,"[" para Bracket as  Delimitador.