Извлечь из адреса город в Excel можно формулой =ПСТР(A2;ПОИСК("г. ";A2)+3;ПОИСК(",";A2&",";ПОИСК("г. ";A2))-ПОИСК("г. ";A2)-3): ПОИСК находит «г. », ПСТР берёт текст после него до ближайшей запятой, а запятая, приписанная к адресу, выручает, если город стоит в конце строки. Извлечь из текста словосочетания по своему списку или все топонимы — около 40 000 городов и стран, с предлогами и без — можно процедурами !SEMTools за секунды даже на сотнях тысяч строк.
Как и извлечение слов, извлечение словосочетаний — недоступная функция в стандартном наборе инструментов Excel. Прежде чем удалить фразы из текста, нужно убедиться, что не будут удалены лишние данные. Для этого их рекомендуется извлечь для удобного просмотра и фильтрации.
Найти одно словосочетание в Excel легко — достаточно воспользоваться автофильтром, — а вот когда фраз много, задача резко осложняется. Надстройка !SEMTools позволяет невероятно быстро извлекать даже из десятков и сотен тысяч ячеек заданные фразы из списка.
Как из адреса вытащить город формулой
| A | B | |
|---|---|---|
| 1 | Адрес | Город |
| 2 | г. Москва, ул. Ленина, 5 | Москва=ПСТР(A2;ПОИСК("г. ";A2)+3;ПОИСК(",";A2&",";ПОИСК("г. ";A2))-ПОИСК("г. ";A2)-3) |
| 3 | Россия, г. Казань, пр. Победы | Казань=ПСТР(A3;ПОИСК("г. ";A3)+3;ПОИСК(",";A3&",";ПОИСК("г. ";A3))-ПОИСК("г. ";A3)-3) |
| 4 | обл. Тверская, г. Ржев | Ржев=ПСТР(A4;ПОИСК("г. ";A4)+3;ПОИСК(",";A4&",";ПОИСК("г. ";A4))-ПОИСК("г. ";A4)-3) |
Формула ищет сокращение «г. » с пробелом. Если в адресах город записан без «г.» или как «город», формулу нужно подстроить под формат данных. В Excel 365 то же короче: =ТЕКСТДО(ТЕКСТПОСЛЕ(A2;"г. ");",";;;1).
В английском Excel: =MID(A2,SEARCH("г. ",A2)+3,SEARCH(",",A2&",",SEARCH("г. ",A2))-SEARCH("г. ",A2)-3)
Если адреса записаны без единого формата и город может стоять где угодно, надёжнее процедура извлечения топонимов: она ищет города по встроенному справочнику, а не по сокращению «г.».
Извлечь города и страны из текста (топонимы)
Извлечение топонимов — один из самых частых кейсов работы с фразами. Все потому, что географические названия могут состоять из нескольких слов. !SEMTools «знает» около 40 000 географических названий со всего света — пропустит разве что редкие населенные пункты. Если задача просто определить наличие города во фразе, можно воспользоваться меню «Найти фразы в списке ячеек».
С предлогами
Бывает важно понять, с какими предлогами используются города в массиве фраз. Извлечение фраз, состоящих из города и предлога перед ним, решает эту задачу. При этом не очень важно, сколько ячеек в вашем массиве фраз, — несколько сотен или несколько сотен тысяч. Благодаря оптимизированным алгоритмам нужны мгновения для обработки.
Извлечь города без предлогов
Базовый кейс, когда нужно просто извлечь из семантического ядра города. Может быть полезно для анализа поискового спроса по регионам.
Извлечь свой список фраз
Для своего списка словосочетаний в том же меню есть отдельная процедура: укажите столбец с фразами, и в ячейках останутся только найденные фразы. Формулой в Excel 365 то же делает =ОБЪЕДИНИТЬ(", ";ИСТИНА;ЕСЛИ(ЕЧИСЛО(ПОИСК(" "&$D$2:$D$10&" ";" "&A2&" "));$D$2:$D$10;"")) — она выводит все фразы списка D2:D10, найденные в ячейке.
Извлечение фраз — частые вопросы
Как из адреса вытащить город?
Формулой с ПСТР и ПОИСК из статьи, если город записан как «г. Название», или процедурой извлечения топонимов !SEMTools для адресов любого формата.
Как извлечь из текста словосочетания по списку?
Процедурой «Извлечь фразы из списка» !SEMTools или формулой с ОБЪЕДИНИТЬ и ПОИСК в Excel 365.
Можно ли извлечь города вместе с предлогами?
Да, в процедуре есть режим «с предлогами»: «в москве», «по казани» — удобно для анализа спроса по регионам.
Извлечь города, страны и свои словосочетания из сотен тысяч строк — с предлогами или без — можно за секунды с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как извлечь фразы из текста
Надеюсь, на этой странице вы нашли то, что искали. Город из адреса единого формата достаёт формула с ПСТР и ПОИСК, а для произвольных текстов и больших списков удобнее процедуры раздела. Вас наверняка заинтересуют и похожие операции: найти фразы и удалить фразы.