Извлечь символы из ячеек Excel по положению помогают ЛЕВСИМВ, ПРАВСИМВ и ПСТР, а текст до или после символа — =ЛЕВСИМВ(A2;ПОИСК("@";A2)-1) и =ПСТР(A2;ПОИСК("@";A2)+1;ДЛСТР(A2)) (в Excel 365 короче: ТЕКСТДО и ТЕКСТПОСЛЕ). Извлечь латиницу, цифры, числа, жирный шрифт, домены, e-mail и телефоны по готовым шаблонам можно процедурами !SEMTools.
Когда просто найти символы в Excel недостаточно и нужно их извлечь для дальнейшего принятия решений, пригодится это меню надстройки в группе макросов «ИЗВЛЕЧЬ».
Надстройка для Excel !SEMTools можно извлекать как специфические множества символов, связанные общими признаками:
- извлечь латиницу, кириллицу, цифры
- извлечь любые буквы и цифры (иначе говоря, удалить из ячеек пунктуацию и другие знаки)
- извлечь числа из текста,
- извлечь символы по начертанию, например, извлечь жирный шрифт, курсив или подчеркнутые символы;
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Текст | Цифры | Латиница | Кириллица | Без знаков |
| 2 | iPhone 15, цена: 89 990 ₽! | 1589990=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^\d]";"")) | iPhone=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^A-Za-z]";"")) | цена=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^А-Яа-яЁё]";"")) | iPhone 15 цена 89 990=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^A-Za-zА-Яа-яЁё0-9 ]";"")) |
[^…] — любой символ, кроме перечисленных: его заменяют на пустоту, и остаётся только нужное. Подробнее — извлечь цифры и извлечь латиницу.
так и определенные последовательности символов:
- извлечь первые N символов в ячейке,
- извлечь последние N символов в ячейке,
- извлечь символы до/после первого вхождения паттерна,
- извлечь символы до/после последнего вхождения паттерна,
- извлечь символы с помощью регулярных выражений в Excel, в том числе по готовым паттернам:
- домены из полных URL и иных строк, содержащих URL-ы;
- e-mail;
- даты;
- номера телефонов (RU).
Символы по положению A B C D 1 Код Первые 3 Последние 2 5 символов с 5-го 2 SKU-12345-RU SKU=ЛЕВСИМВ(A2;3) RU=ПРАВСИМВ(A2;2) 12345=ПСТР(A2;5;5) ЛЕВСИМВ и ПРАВСИМВ берут N символов с края, ПСТР — с указанной позиции. Подробнее — первые N символов и последние N символов.
В английском Excel: =LEFT(A2,3)
Другие текстовые операции с символами
Помимо извлечения символов, может быть необходима прямо противоположная операция — удалить символы. Также, после ознакомления с извлеченными данными, вам может пригодиться раздел про изменение символов в Excel, например, изменение регистра букв.
Но в первую очередь перед любыми такими действиями всегда полезно сначала найти определенные вами символы. Ведь если их нет, то и делать дальше уже ничего не нужно :)
Формулы для извлечения символов
| A | B | C | |
|---|---|---|---|
| 1 | До @ | После @ | |
| 2 | ivan@mail.ru | ivan=ЛЕВСИМВ(A2;ПОИСК("@";A2)-1) | mail.ru=ПСТР(A2;ПОИСК("@";A2)+1;ДЛСТР(A2)) |
| 3 | sales.team@company.com | sales.team=ЛЕВСИМВ(A3;ПОИСК("@";A3)-1) | company.com=ПСТР(A3;ПОИСК("@";A3)+1;ДЛСТР(A3)) |
ПОИСК находит позицию символа, ЛЕВСИМВ берёт всё до неё, ПСТР — всё после. Если символа в ячейке нет, формулы вернут #ЗНАЧ! — оберните их в ЕСЛИОШИБКА.
В английском Excel: =LEFT(A2,SEARCH("@",A2)-1)
| Задача | Формула |
|---|---|
| Первые N символов | =ЛЕВСИМВ(A2;N) |
| Последние N символов | =ПРАВСИМВ(A2;N) |
| N символов с позиции P | =ПСТР(A2;P;N) |
| Текст до символа (Excel 365) | =ТЕКСТДО(A2;"@") |
| Текст после символа (Excel 365) | =ТЕКСТПОСЛЕ(A2;"@") |
| A | B | |
|---|---|---|
| 1 | Адрес | Домен |
| 2 | https://semtools.guru/ru/download/ | semtools.guru=ТЕКСТДО(ТЕКСТПОСЛЕ(A2;"//");"/";;;1) |
| 3 | http://site.ru | site.ru=ТЕКСТДО(ТЕКСТПОСЛЕ(A3;"//");"/";;;1) |
ТЕКСТПОСЛЕ отрезает «https://», ТЕКСТДО — всё после первой косой черты. Единица в последнем аргументе считает конец строки разделителем, поэтому адрес без «/» в конце тоже обрабатывается.
В английском Excel: =TEXTBEFORE(TEXTAFTER(A2,"//"),"/",,,1)
Извлечение символов — частые вопросы
Как извлечь текст до определённого символа?
=ЛЕВСИМВ(A2;ПОИСК("@";A2)-1) или =ТЕКСТДО(A2;"@") в Excel 365.
Как извлечь текст после символа?
=ПСТР(A2;ПОИСК("@";A2)+1;ДЛСТР(A2)) или =ТЕКСТПОСЛЕ(A2;"@").
Как извлечь домен из URL?
В Excel 365 — =ТЕКСТДО(ТЕКСТПОСЛЕ(A2;"//");"/";;;1), в любой версии — процедурой извлечения доменов !SEMTools.
Извлечь латиницу, цифры, числа, жирный шрифт, домены, e-mail и телефоны или текст до и после символа сразу во всём диапазоне можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как извлечь символы в Excel
Надеюсь, на этой странице вы нашли то, что искали. Символы по положению и до/после разделителя достают стандартные формулы, а символы по типу и готовым шаблонам — процедуры раздела. Вас наверняка заинтересуют и похожие разделы: удалить символы и найти символы.