Разбор несогласованно отформатированных текстовых данных в Excel

У меня проблемы с анализом текста из таблицы. У меня есть электронная таблица с сотнями записей, которые похожи на приведенный ниже пример (это все в одной ячейке):

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

Мне нужно разобрать каждое из следующего в отдельные ячейки:

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

или как это будет выглядеть в листе Excel

У меня проблема в том, что данные для каждой записи противоречивы. Больше примеров:

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

Я хотел бы избежать как можно большего количества ручных манипуляций, но боюсь, что мне не повезет.

Как мне проанализировать данные, если их формат не соответствует?

Источник

2 ответа

Поскольку вы имеете дело с несколькими разделителями, в таком случае я хотел бы предложить вам два возможных решения.

Решение 1 (VBA Macro): 

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

Как это устроено:

  1. Введите этот код как Модуль вместе с Листом.

  2. Напишите эту Формулу в ячейке желаний, затем заполните ее. F2 и закончить с Ctrl+Shift+Enter ,

    {=ReplaceAndSplit(A2,",")}

Замечания:

  1. "," для Comma в качестве разделителя.
  2. Так должно быть " " за Space ,
  3. "[ ]" за Brackets ,

Решение 2 (Формула Excel): 

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

Замечания:

Вам нужно отредактировать эту часть формулы в соответствии с разделителем, прежде чем выполнять ее, например,

  1. SUBSTITUTE($A1,"," за Comma в качестве разделителя.
  2. SUBSTITUTE($A1," " за Space в качестве разделителя.
  3. SUBSTITUTE($A1,"[" за Bracket в качестве разделителя.
Источник

Если ваши примеры действительно репрезентативны, то существует шаблон, который можно извлечь с помощью регулярных выражений. Это может быть реализовано в Excel с использованием VBA.

Шаблон:

  • Начало строки
  • Серия цифр с последующим пробелом
  • Подстрока, которая начинается с одной или нескольких цифр и не содержит других цифр
  • вторая подстрока, которая начинается с одной или нескольких цифр и не содержит других цифр
  • несколько разделенных пробелами групп с запятыми, с необязательной запятой (возврат последней)

Все ваши примеры показывают эту модель. Вот пользовательская функция, которая будет обрабатывать это:

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

Конечно, если ваш пример не является действительно представительным, то это решение не будет работать.

Источник
Другие вопросы по тегам