Список городов России

Процедуры из статьи доступны в !SEMTools Pro.

Список городов России нужен, когда в тысячах строк надо найти, вырезать или вытащить в отдельный столбец название города: «доставка цветов ростов на дону», «купить диван в самаре», «грузоперевозки из екатеринбурга». В !SEMTools встроен справочник на 5766 строк: города и посёлки России сразу во всех падежах. С ним города находятся, удаляются или извлекаются из текста в пару кликов, без единой формулы.

Список можно посмотреть в Google-таблице, а вот скачать его бесплатно не получится. Дело не в жадности: каждый город здесь записан в шести падежах, с дефисами и без, и такой справочник собирается неделями. Поэтому он входит в !SEMTools Pro и обновляется прямо из надстройки.

Что входит в список городов России

Главное отличие от списков из интернета в падежах. Люди не пишут «доставка Самара», они пишут «доставка по самаре» и «из самары». Поиск по именительному падежу пропустит большую часть таких запросов.

Зачем искать города в тексте

Список универсальный, им пользуются не только маркетологи. Самые частые задачи:

• Контекстная реклама: отделить запросы с городом от общих. Для региональной кампании чужие города становятся минус-словами, иначе московский салон будет платить за «диван в новосибирске».
• SEO: понять, сколько спроса приходится на каждый город, и решить, нужны ли региональные страницы.
• Логистика и продажи: разобрать адреса и заявки, где город записан где попало: «Ростов, ул. Садовая» и «г. ростов-на-дону».
• Аналитика: посчитать, из каких городов пишут клиенты, по тексту обращений или отзывов.

Про минус-слова и нерелевантные запросы подробнее рассказано в статье про нерелевантные запросы.

Найти города в ячейках

Процедура отвечает на вопрос «есть ли в ячейке слово из списка?» и возвращает ИСТИНА или ЛОЖЬ.

1. Выделите столбец с фразами.
2. Откройте ОБНАРУЖИТЬ → Фразы → Найти маркеры… → Топонимы → Населённые пункты РФ.
3. Готово: у каждой ячейки ИСТИНА, если в ней есть хотя бы одно слово или фраза из списка, и ЛОЖЬ, если нет.

Так это выглядит на реальных фразах:

Регистр не важен, слова сравниваются целиком. По столбцу с ИСТИНА таблицу удобно отфильтровать, а если исходные данные нужно сохранить, включите на панели флажок «Выводить результат справа».

Удалить названия городов из текста

Обратная задача: убрать города и получить «чистые» фразы, например чтобы сгруппировать запросы по смыслу, а не по региону.

1. Выделите ячейки.
2. Откройте УДАЛИТЬ → Фразы → Удалить маркеры… → Топонимы → Населённые пункты РФ.
3. Слова и фразы из списка удалятся прямо в ячейках, лишние пробелы между оставшимися словами схлопнутся.

Предлоги процедура не трогает: «в самаре» превращается в «в». Если предлоги тоже нужно убрать, удаляйте все топонимы сразу командой «Топонимы → Все → С предлогами», она описана в разделе про топонимы.

Извлечь город в отдельный столбец

Когда нужна не отметка, а само найденное слово, его извлекают в ячейку.

1. Выделите столбец с фразами.
2. Откройте ИЗВЛЕЧЬ → Фразы → Маркеры… → Топонимы → Населенные пункты РФ.
3. В ячейках останутся найденные слова; если их несколько, они разделяются обратной косой чертой «\».

Город возвращается в том виде, в каком записан в списке: падеж сохраняется («Самаре», «Екатеринбурга»). Чтобы свести формы к именительному падежу, примените после извлечения лемматизацию.

Найти город формулой по своему списку

Без надстройки можно обойтись формулой, но список городов, да ещё во всех падежах, придётся собрать самостоятельно. Пусть он лежит в F2:F50:

=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$F$2:$F$50&" ";" "&A2&" ")))>0

Формула ищет каждое слово из диапазона F2:F50 в ячейке A2 целиком (пробелы вокруг не дают найти слово внутри другого) и возвращает ИСТИНА, если нашлось хотя бы одно. Извлечь найденные слова в Excel 365 можно так: =ОБЪЕДИНИТЬ("\";ИСТИНА;ФИЛЬТР($F$2:$F$50;ЕЧИСЛО(ПОИСК(" "&$F$2:$F$50&" ";" "&A2&" "));"")). Честно о минусах: 5766 строк на тысячи фраз дают десятки миллионов проверок, и книга будет пересчитываться заметно долго. А удалить города формулой без регулярных выражений не выйдет вовсе.

Почему город не находится или находится лишнее

• Омонимы. Среди городов есть обычные слова и фамилии: Мирный, Орел, Сокол, Луга, Шахты, Бор, Владимир, Королев, Чехов. «Сокол» найдётся и в «станция Сокол», и в «сокол сапсан». На фразах не о географии проверяйте результат, удобнее всего через извлечение: сразу видно, какое слово сработало.
• Буква «ё». В списке города записаны через «е»: «Орел», «Королев». Если в данных «Орёл», сначала замените «ё» на «е» через Ctrl+H.
• Знак препинания вплотную к слову: «г. Самара,». Процедура делит текст по пробелам, поэтому слово с запятой или в скобках не совпадёт. Сначала удалите знаки препинания.
• Список давно не обновлялся. Я его пополняю, а свежую версию надстройка скачивает кнопкой «Обновить все списки» в том же меню.
• Поиск и извлечение смотрят только первый столбец выделения. Удаление обрабатывает все выделенные столбцы.

В бесплатной версии процедуры работают с ограничением: в части ячеек вместо результата будет надпись «Доступно в полной версии !SEMTools».

Список городов России: частые вопросы

Где взять список городов России для Excel?

Посмотреть его можно в Google-таблице по ссылке из статьи. Бесплатно он не скачивается: список встроен в !SEMTools Pro и обновляется из надстройки.

Есть ли в списке падежи?

Да, все шесть: Самара, Самары, Самаре, Самару, Самарой. Поэтому находятся и «доставка по самаре», и «из самары».

Как удалить города из списка запросов?

Процедурой УДАЛИТЬ → Фразы → Удалить маркеры… → Топонимы → Населённые пункты РФ.

Как извлечь город из адреса в Excel?

Процедурой ИЗВЛЕЧЬ → Фразы → Маркеры… → Топонимы → Населенные пункты РФ: город окажется в ячейке отдельно от остального текста.

Итоги: список городов России в Excel

Надеюсь, на этой странице вы нашли то, что искали. Формула по своему списку годится для десятка городов. Для реальных данных, где города стоят в разных падежах и пишутся то с дефисом, то без, быстрее встроенный список: не нужно ни собирать справочник, ни протягивать формулы. Все топонимы мира, включая страны, разобраны в разделе про поиск топонимов. Другие готовые списки (города, имена, марки авто, цвета, минус-слова) собраны в разделе готовые списки фраз.