Как подставлять определенный символ в начало предложения, которое содержит определенное слово?

Question

[gethighagency]

Добрый день, подскажите, пожалуйста, как сделать чтоб в эксель/таблицы автоматически добавить определенный символ в начале предложения, которое содержит слово "доставка, доставим, доставкой"?
Символ должен добавляться не в начало ячейки, а в начало предложения. Например, "В интернет-магазине Матрешки вы с легкостью обновите гардероб качественными и практичными вещами. Заказывайте шорты с комфортной посадкой для мужчин, созданные для путешествий. Быстро доставим покупки."
Здесь надо добавить символ в начале предложения "Быстро доставим покупки"

Comments

[Александр]

Нужно использовать регулярное выражение с ретроспективной проверкой.

[gethighagency]

Александр, пробовал эту формулу, но выдает ошибку =ARRAYFORMULA(CONCATENATE(REGEXREPLACE(IFERROR(SPLIT(B2, ".", TRUE)), "(доставка)", "!!! $1") & ". "))

[Akina]

На VBA это будет куда как понятнее и удобнее... что-то типа (draft)

static busy as boolean = false
const addr as string = "x2:x10"

sub worksheet.change(target as range)
    if busy then exit sub
    busy = true
    for each onecell in intersect(target, range(addr))
        tmp = split(onecell.value, ".")
        for i = lbound(tmp) to ubound(tmp)
            if instr(tmp(i), "достав") > 0 and left(tmp(i),3) <> "!!!" then 
                tmp(i) = "!!! " & tmp(i)
           end if
        next
        onecell.value = join (tmp, ".")
    next
    busy = false
end sub

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

[gethighagency]

Akina, спасибо, в итоге так сделал =ARRAYFORMULA(IFERROR(REGEXREPLACE(B2:B; "([.!?]\s+|^)([^.!?]*(доставка|доставим|доставкой)[^.!?]*[.!?])"; "$1 $2"); B2:B))