Как удалить символы в ячейке Excel

В статье рассматривается, как быстро удалить определенные символы или часть текста из ячейки Excel с помощью формул и встроенных функций.

Мы рассмотрим наиболее типичные примеры удаления символов в ячейке Excel. Мы увидим, как можно удалить определенный текст из нескольких ячеек. В статье представлены формулы, чтобы удалить первый или последний символ из ячейки Excel, весь текст до или после разделителя. Или, возможно, удалить только определенное вхождение какого-то символа. Постараемся рассмотреть как можно больше решений.

Как удалить определенный символ в Excel

Рассмотрим, как можно удалить символ из нескольких ячеек с помощью функции «Найти и заменить»

Принимая во внимание, что удаление символа — это не что иное, как замена его пустотой, вы можете использовать инструмент Excel “Найти и заменить” для выполнения этой задачи.

Рассмотрим процесс пошагово:

Выберите диапазон ячеек, из которых вы хотите удалить определенный символ.
Нажмите комбинацию Ctrl + H, чтобы открыть диалоговое окно Найти и заменить (или выберите на ленте Главная соответствующий пункт меню).
В поле Найти введите символ, который нужно удалить.
Оставьте поле Заменить пустым.
Нажмите Заменить все.

В качестве примера показано, как можно удалить символ # из ячеек от A2 до A6.

удалить символ с помощью инструмента «Найти и заменить»

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

Советы и примечания:

Этот метод удаляет символы непосредственно в исходных данных. Если результат отличается от ожидаемого, нажмите Ctrl + Z, чтобы отменить изменение и вернуть исходные данные.
Если вы имеете дело с буквенными символами, где регистр букв имеет значение, нажмите Параметры, чтобы развернуть диалоговое окно «Найти и заменить», а затем установите флажок Учитывать регистр, чтобы выполнить поиск с учетом регистра.

Удалить определенный символ из текста с помощью формулы

Чтобы удалить определенный символ из любой позиции в ячейке Excel, используйте формулу ПОДСТАВИТЬ (SUBSTITUTE в английской версии):

ПОДСТАВИТЬ(текст; символ; "")

Чтобы удалить лишний символ решетки в ячейке, формула примет такой вид:

=ПОДСТАВИТЬ(A2; "#"; "")

Формула обрабатывает текст в A2 и заменяет каждый символ решетки # пустотой "". То есть, в итоге просто удаляет его.

Запишите эту формулу в B2, скопируйте ее вниз по столбцу, и вы получите такой результат:

Обратите внимание, что функция ПОДСТАВИТЬ всегда возвращает текстовую строку, даже если результат содержит только числа, как в ячейках B2, B3 и В5 (обратите внимание на выравнивание по левому краю, характерное для текстовых значений).

Если вы хотите, чтобы результатом было число, оберните приведенную выше формулу в функцию ЧЗНАЧ:

=ЧЗНАЧ(ПОДСТАВИТЬ(A2;"#";""))

Или вы можете выполнить некоторую математическую операцию, которая не меняет исходное значение, например, добавить 0 или дважды умножить на -1 (двойное отрицание):

=--ПОДСТАВИТЬ(A2; "#"; "")

Как удалить из ячейки несколько символов сразу

Чтобы удалить сразу несколько символов из ячейки Excel с помощью одной формулы, просто вложите несколько функций ПОДСТАВИТЬ друг в друга.

Например, чтобы избавиться от лишних символов решетки (#), косой черты (/) и обратной косой черты (\), используйте следующую формулу:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; "#";""); "/"; ""); "\"; "")

как в excel удалить символы в ячейке excel

Советы и примечания:

Функция ПОДСТАВИТЬ чувствительна к регистру, имейте это в виду при работе с буквами.
Если вы хотите, чтобы результаты представляли собой значения, независимые от первоначального текста, используйте Специальная вставка — Значения для замены формул их значениями.

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

Как удалить текст сразу из нескольких ячеек

Чтобы удалить определенный текст из каждой ячейки в выбранном диапазоне, нажмите Ctrl + H, чтобы отобразить диалоговое окно Найти и заменить, а затем:

Введите нежелательный текст в поле Найти.
Оставьте поле Заменить пустым.

Нажатие кнопки Заменить все приведет к выполнению всех замен одновременно:

Как удалить часть текста из ячейки с помощью формулы

Чтобы удалить из ячейки часть текста, вы снова используете функцию ПОДСТАВИТЬ:

ПОДСТАВИТЬ(текст; символ; "")

Например, чтобы удалить текст "mailto:" из ячейки A2, подойдет такая формула:

=ПОДСТАВИТЬ(A2; "mailto:"; "")

Запишите ее в ячейку B2, а затем скопируйте вниз насколько это необходимо:

Как удалить N-й экземпляр определенного символа

В ситуации, когда вы хотите удалить из ячейки конкретное вхождение определенного символа, нужно использовать последний необязательный аргумент функции ПОДСТАВИТЬ.

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

ПОДСТАВИТЬ(текст, старый_текст; новый_текст; [номер_вхождения])

Например:

Чтобы удалить первое по счёту тире в тексте в ячейке A2, ваша формула может выглядеть так:

=ПОДСТАВИТЬ(A2; "-"; ""; 1)

Чтобы удалить второе тире, изменим только последний параметр:

=ПОДСТАВИТЬ(A3; "-"; ""; 2)

Как удалить первый символ из текста

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

ЗАМЕНИТЬ(ячейка; 1; 1; "")

Формула означает: в указанной ячейке возьмите 1 символ из первой позиции и замените ее пустой строкой ("").

ПРАВСИМВ(ячейка; ДЛСТР(ячейка) - 1)

Здесь мы вычитаем 1 из общей длины текстовой строки, которая рассчитывается функцией ДЛСТР. Полученное значение передается в функцию ПРАВСИМВ, чтобы извлечь это количество символов с конца текста. В результате получим все символы, корме первого.

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

=ЗАМЕНИТЬ(A2; 1; 1; "")

=ПРАВСИМВ(A2;ДЛСТР(A2)-1)

На скриншоте ниже вы видите формулу ЗАМЕНИТЬ. Формула ПРАВСИМВ+ДЛСТР даст точно такие же результаты и успешно удалит из ячейки Excel первый символ слева.

excel как удалить первый символ в ячейке

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

Чтобы удалить несколько начальных символов из текста, вы также можете использовать функции ЗАМЕНИТЬ или ПРАВСИМВ и ДЛСТР, но при этом укажите, сколько именно символов вы хотите удалить.

ЗАМЕНИТЬ(ячейка; 1; число_символов; "")

ПРАВСИМВ(ячейка; ДЛСТР(ячейка) – число_символов)

Например, чтобы удалить первые 2 символа из текста, можно использовать эти формулы:

=ЗАМЕНИТЬ(A2; 1; 2; "")

=ПРАВСИМВ(A2;ДЛСТР(A2)-2)

Аналогично мы можем удалить первые 3 символа:

=ЗАМЕНИТЬ(A2; 1; 3; "")

=ПРАВСИМВ(A2;ДЛСТР(A2)-3)

Пример вы видите на скриншоте ниже.

Как удалить последний символ в ячейке

Чтобы удалить последний символ справа, используется формула:

ЛЕВСИМВ(ячейка;ДЛСТР(ячейка)-1)

Логика аналогична формуле ПРАВСИМВ ДЛСТР из предыдущего примера. Вы вычитаете 1 из общей длины текста и передаете эту разницу в функцию ЛЕВСИМВ, чтобы она могла извлечь такое количество символов начиная с начала строки.

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

=ЛЕВСИМВ(A2;ДЛСТР(A2)-1)

как удалить последний символ в ячейке excel

Как удалить последние N символов справа

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

ЛЕВСИМВ(ячейка;ДЛСТР(ячейка)-N)

где N – число символов для удаления.

Как видите, это почти то же самое, сто в предыдущем примере. Только вместо 1 используем другое число. Например, чтобы удалить 3 символа справа из ячейки A1, используйте формулу

=ЛЕВСИМВ(A1;ДЛСТР(A1)-3)

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

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

ЛЕВСИМВ(ячейка; ПОИСК("[символ]"; ячейка)-1)

Логика вычислений здесь довольно проста: функция ПОИСК вычисляет позицию указанного символа и передает ее функции ЛЕВСИМВ, которая возвращает соответствующее количество символов слева, с начала текста. Чтобы не выводить сам разделитель, мы вычитаем 1 из результата ПОИСК.

На скриншоте ниже ивы можете видеть пример

=ЛЕВСИМВ(A2; ПОИСК(":"; A2) -1)

Мы удалили из ячейки часть текста, что находится после двоеточия.

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

Как и в предыдущем примере, главным моментом является определение позиции символа-разделителя при помощи функции ПОИСК. Также нам нужно найти общее количество символов в ячейке при помощи функцииДЛСТР.

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

ЛЕВСИМВ(ячейка; ДЛСТР(ячейка) - ПОИСК("[символ]"; ячейка))

Кроме того, после разделителя часто встречается пробел. Поскольку нам он также не нужен, обрабатываем полученный текст при помощи функции СЖПРОБЕЛЫ, чтобы убрать лишние пробелы.

Например, чтобы удалить из ячейки часть текста до двоеточия (:) и оставить только емейл, формула в B2 может выглядеть следующим образом:

=СЖПРОБЕЛЫ(ПРАВСИМВ(A2; ДЛСТР(A2) - ПОИСК(":"; A2)))

Более подробно об удалении лишних пробелов в ячейке читайте здесь: 7 примеров удаления пробелов в ячейках.

Специальные инструменты для удаления символов или текста в Excel

В этом последнем разделе представлены специальные решения для удаления текста из ячеек Excel. Если вам нравится находить простые способы решения сложных задач, вам понравятся удобные инструменты, включенные в Ultimate Suite.

На вкладке Данные Ablebits в разделе Текст группы существует три варианта удаления символов из ячеек Excel:

Определенные символы и группы символов
Символы в определенной позиции
Дублирующиеся символы

инструмент для удаления ненужного текста в Excel

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

Нажмите Удалить (Remove) > Удалить символы (Remove Characters).
Выберите вариант, наиболее соответствующий вашим потребностям.
Установите или снимите флажок С учетом регистра (Case-sensitive).
Нажмите Удалить (Remove).

Ниже приведены несколько примеров, охватывающих наиболее типичные ситуации.

Удалить определенный символ

Чтобы удалить определенный символ(ы) из нескольких ячеек одновременно, выберите Remove customcharacters.

Например, мы удаляем все вхождения заглавных букв A и B из диапазона A2:A4:

Удаление определенного набора символов

Чтобы удалить определенный набор символов, выберите Удалить наборы символов (Remove characters sets), а затем выберите один из следующих вариантов:

Непечатаемые символы — удаляет любые из первых 32 символов 7-битного набора ASCII (кодовые значения от 0 до 31), включая символ табуляции, разрыв строки и и так далее.
Текстовые символы — удаляет текст и сохраняет цифры.
Цифровые символы — удаляет цифры из буквенно-цифровых строк.
Символы и знаки препинания — удаляет специальные символы и знаки препинания, такие как точка, вопросительный знак, восклицательный знак, запятая и т.д.