Как я могу разобрать строку из ячейки несколько раз в Excel?

Question

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

Пример:

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

Я хотел бы, чтобы следующий столбец содержал:

Fruit
Fruit: apple, Fruit: banana,

Fruit: orange,

Достаточно легко найти первый экземпляр строки (новые строки для удобства чтения):

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

Однако я мог встречаться со строкой любое количество раз и хочу поймать их все.

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

Любые идеи о том, как вернуть все экземпляры строки? Я бы предпочел избегать сценария VBA, если это возможно, и использовать функции рабочего листа, но если это невозможно с функциями, я открыт для VBA.

Answer 1

Типовое решение VBA с регулярными выражениями

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

1. Откройте редактор VBA (ALT+F11)
2. Вставьте новый модуль (!) и вставьте в него приведенный ниже код

3. Вернитесь в Excel и используйте эту формулу в ячейке, где вы хотите выводить

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

Объяснение формулы

• =REGEXTRACT() - ваша новая формула.
• A1 - это ячейка, в которой находятся ваши входные данные
• Fruit: .*?, является регулярным выражением, чтобы найти все вхождения fruit и совпадений до следующей запятой.

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

Подсказка: выражения Look-Behind и Look-Ahead не поддерживаются в движке регулярных выражений VB. Так что нетрудно исключить запятую через RegEx. Но это возможно через обычные строковые операции VBA.

Answer 2

Это нелепо неуклюже, но, похоже, работает. Я бы порекомендовал решение VBA, если честно.

=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))

Это формула массива, и вы должны выбрать как минимум столько ячеек, сколько есть экземпляров «Fruit». Поэтому, если в одном столбце четыре плода, выделите четыре ячейки по ширине, введите эту формулу и подтвердите, нажав Ctrl + Shift + Enter. Если вы выберете дополнительные ячейки, то в конце начнет выдавать ошибку, поэтому теоретически вы можете выбрать максимальное количество ячеек, которое вам понадобится для любого столбца, и выполнить автоматическое заполнение.

Редактировать: Я понял, что если есть только один фрукт, он вернет этот фрукт в каждой клетке. Я не думаю, что есть способ обойти это с этой формулой.

Также предполагается, что у вас не будет названия фруктов длиннее 1000 символов, и что «~~~~~» не будет отображаться в вашем тексте в обычном режиме.

Объяснение: Где n - это число плодов в ячейке, формула использует FIND/SUBSTITUTE для построения массива из n элементов, где первое вхождение "Fruit" заменяется на «~~~~~», а затем второе. и т. д. Затем он использует MID/FIND для извлечения текста. Поскольку в Excel нет формулы "найди вхождение строки x", мы делаем сумасшедшие вещи, подобные этой. IFERROR существует для последнего Fruit, так как я предполагал, что после него может не быть запятой.