Довольно часто в Excel нужно убрать из строки символы до определенного знака. Это может быть как один символ (пробел, точка, запятая, двоеточие и т.д.), так и символ из определенного множества (цифра, английская буква).

В зависимости от этих условий, решение может быть простым, а может потребовать использование довольно сложных формул и процедур.

В этой статье я подробно опишу известные мне варианты. Поехали!

Удалить текст до последнего символа

Наиболее просто удалить текст в диапазоне ячеек до последнего вхождения заданного символа или текста. Можно воспользоваться процедурой «Найти и заменить»:

  • Выделить диапазон
  • Вызвать процедуру поиска и замены со вкладки «Главная» или сочетанием клавиш Ctrl+H
  • использовать подстановочный символ * (звездочку) прямо перед искомым в строке поиска
  • оставить пустым второе поле и нажать ОК

Процедура заменит все символы до знака на пустоту, иначе говоря, удалит их. Звездочка как раз и обозначает «все символы».

Удалить текст до первого пробела — формула

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

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

=ЗАМЕНИТЬ(A2;1;ПОИСК(" ";A2);"")
Удалить текст до первого пробела - формула
Результат применения формулы

Формулы для других символов аналогичны:

Убрать текст в ячейке до первой запятой:

=ЗАМЕНИТЬ(A2;1;ПОИСК(",";A2);"")

До точки:

=ЗАМЕНИТЬ(A2;1;ПОИСК(".";A2);"")

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

Здесь из адресов удаляется текст до номера дома. Длина текста в кавычках — 3 символа (д, точка и пробел):

=ЗАМЕНИТЬ(A2;1;ПОИСК("д. ";A2)+2;"")
Удалить текст в ячейке до определенного слова

Удалить текст до первой цифры

Пожалуй, вы уже догадались, но я все же объясню, как работают формулы выше:

  1. Excel-функция ПОИСК ищет позицию первого вхождения текстового паттерна (он всегда в кавычках). Соответствующий фрагмент выделен красным.
  2. функция ЗАМЕНИТЬ принимает эту позицию как аргумент;
  3. она же заменяет фрагмент от первого символа до этой позиции на пустоту (две кавычки подряд), таким образом, удаляя его.

Следовательно, чтобы удалить текст до первой цифры, нужно вычислить позицию первой цифры в строке. Поскольку цифр не одна, а 10, простая формула уже не подойдет. Но можно сделать это вот такой формулой массива:

{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));A1);""))}

А подставив это выражение в исходную формулу, мы сможем решить исходную задачу — получить текст после первой цифры включительно, удалив текст до нее.

{=ЗАМЕНИТЬ(A2;1;МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА($48:$57));A2);""))-1;"")}

Что за числа 48 и 57, можно прочитать подробнее тут:

Функция СИМВОЛ в Excel

Обратите внимание на фигурные скобки! Их не нужно вводить, они появятся сами при вводе формулы сочетанием клавиш Ctrl+Shift+Enter.

Формула выдаст ошибку, если цифр в строке нет.

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

Удаление текста в 1-2 клика с !SEMTools

Каждый из перечисленных способов хорош по-своему, но всем им недостает одного важного фактора — скорости исполнения.

В каждом из случаев нужно вводить много символов и производить много действий.

Для экономии времени я включил быстрые процедуры в меню своей надстройки для Excel — !SEMTools.

Теперь удалить текст до первого или последнего вхождения конкретного символа или подстроки, включая и не включая сам текст, можно в считанные мгновения!

При этом можно как менять данные на месте, так и автоматически выводить в соседний столбец — за это отвечает глобальный чекбокс.

Смотрите пример:

Удаление текста до определенного символа в Excel с !SEMTools
Удаляем символы в тексте до первого или последнего вхождения определенного текста с !SEMTools

Хотите так же быстро обращаться с текстовыми данными в Excel и испытать на практике еще более 500 похожих процедур?

Жмите на кнопку ниже, чтобы бесплатно скачать и подключить пробную версию надстройки !SEMTools. Процедура займет пару минут.


Смотрите также по теме:

Удалить последнее слово из ячейки

Удалить первое слово в ячейках Excel

Удалить первые N символов в ячейках

Извлечь первые N символов ячейки