Найдите и получите конкретное слово с помощью пунктуации, но затем удалите пунктуацию из результата

Question

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

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

Twelve little pigs rolled down the hill at Huckleberry, Farms: Citizen from Huckleberry

Я пытаюсь придумать формулу Excel, в которой слово «Гекльберри» само по себе будет отображаться в отдельной ячейке, из которой вводятся данные (ячейка B2 в примере).

Структура «Гекльберри» всегда имеет «,» и всегда предшествует следующему слову с двоеточием (:), поэтому оно в основном соответствует формату:

Брусника, Фермы:
Блюфилд, Луга:
Buttercream, Долина:

Я получил довольно близко к тому, что мне нужно, используя формулу:

=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",99)),MAX(1,FIND(",",SUBSTITUTE(B2," ",REPT(" ",99)))-50),99))  

Но он сохраняет запятую, которая дает мне «Гекльберри», когда я хочу «Гекльберри».

Я бы предпочел вариант, если это вообще возможно, который использует «:» из «Фермы:» в качестве отправной точки, работая справа налево, чтобы найти «,» в «Гекльберри», так как иногда встречаются запятые перед запятой, найденной в «Гекльберри». Но на этом этапе любое формальное решение этого вопроса будет высоко оценено.

Answer 1

эта формула массива будет возвращать слово с запятой перед словом с двоеточием независимо от того, сколько запятых появляется до или после:

=CONCAT(IF((RIGHT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)))=",")*(RIGHT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),ROW($1:$40)*99,99)))=":"),LEFT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)),LEN(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)))-1),""))

Будучи формулой массива, она должна быть подтверждена Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.

CONCAT был представлен в Office 365 Excel.

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

Мы проверяем, заканчивается ли слово запятой, а следующий заканчивается двоеточием, затем возвращает слово минус запятая.

У этого есть один недостаток в том, что он будет возвращать каждое слово, за которым следует запятая, где следующее слово заканчивается двоеточием.

Answer 2

Вот простая ванильная нормальная формула, которая будет работать в любой версии Excel:

=MID(B2,FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1))+1,FIND(",",B2,FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1)))-FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1))-1)

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

Недостаток этой формулы в том, что если после слова перед словом перед первым двоеточием не стоит запятая, возвращается ошибка.

Объяснение:

Эту формулу легче понять, если вы используете четыре вспомогательные ячейки:

Формулы в ячейках:

B3:

=LEFT(B2,FIND(":",B2))

B4:

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

B5:

=SUBSTITUTE(B3," ","§",B4-1)

B6:

=FIND("§",B5)

B7:

=MID(B2,B6+1,FIND(",",B2,B6)-B6-1)

Полное решение - это просто результат подключения формул для B3 , B4 , B5 и B6 к B7 .

Заметки:

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

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