Excel works!
Excel работает за вас
- Залогиниться
- Зарегистрироваться
Забыли пароль?
Excel works!
- Главная
- Excel
- Управление проектами
- Книги
- Прочее
- Аналитика
Thanks for Visiting
- Главная
- Excel
- Управление проектами
- Книги
- Прочее
- Аналитика
Как удалить символ в ячейке Excel?
При выгрузке из учетной программы, при скачивании данных из интернета или, если файл заполняется неопытным пользователем, случается, что в ячейках остаются лишние символы, пробелы, точки, знаки переноса строки и т.п. Как быстро и просто удалить любой символ в ячейке Excel?
Самая распространенная ошибка, когда Excel не воспринимает текст как число. Об этом можно читать здесь . Способов как удалить символ в ячейке много, постараюсь рассказать о каждом подробнее.
1. Найти и заменить символ в ячейке Excel
Если вкратце, то существует специальный инструмент во всех продуктах MS Office. Вызывается клавишами Ctrl+F (найти) или Ctrl+H (заменить). В первое поле можно вписать символ, который нужно удалить, во второе ничего не вписывайте.
Подробнее об этом инструменте написано в этой статье
2. Удалить символ в ячейке Excel формулой
Удалить любой символ можно прекрасной функцией =ПОДСТАВИТЬ().
Эта функция имеет следующую конструкцию =ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)
текст — здесь указывается ячейка, где записан текст для изменения
старый_текст — что меняем
новый_текст — на что меняем
номер_вхождения — является необязательным. Он предписывает Excel заменить только символы, начиная с определенного номера. Интересное дополнение
Представим, что вам нужно заменить все точки на запятые, получится формула:
3. =СЖПРОБЕЛЫ()
Часто пробелы не видны невооруженным глазом («Иванов Иван «), нужно писать дополнительную проверку, чтобы найти их, в общем, предпринимать дополнительные действия. Проще всего воспользоваться формулой =СЖПРОБЕЛЫ(). Она убирает все задвоенные пробелы, а так же лишние пробелы в конце и начале текста в ячейке (станет «Иванов Иван») .
Но! Будьте внимательнее — формула удаляет стандартные пробелы. Если вы к примеру копировали текст с сайта — он может содержать символы похожие на пробелы, но функция =СЖПРОБЕЛЫ() не будет их удалять. В таком случае придется пользоваться возможностью Найти и заменить.
Но в 95% случаев сжпробелы помогает на «отлично».
Удаление пробелов я даже вынес в отдельную статью
4. Удалить первый символ
Часто необходимо удалить первый, несколько последних символов и так далее. Как это сделать быстро и не вручную? Воспользуйтесь функцией ПСТР .
Очень подробно об этом написано здесь .
Например, у нас в ячейках задан порядковый номер сотрудника. Нужно удалить первые символы до точки и пробел.
Формула будет выглядеть следующим образом:
Так же можно разделить текст по столбцам и разнести их в разные ячейки.
5. Функция =ПЕЧСИМВ()
Функция с одним аргументом очень похожа на СЖПРОБЕЛЫ, но она удаляет непечатные символы в строке, такие как перенос строки, знаки абзаца, какие-нибудь квадратики и т.п. (первые 32 непечатаемых знаков в 7-разрядном коде ASCII). Особенно полезна она, если нужно удалить перенос строки — мне всегда помогает.
Внимание! ПЕЧСИМВ способна удалить большинство, но не все лишние символы.
Как удалить символы в ячейке Excel? Как удалить первый и последний символ?
Как убирать определённые символы в ячейках?
Как удалить первый символ в ячейке?
Как удалить последний символ в ячейке?
Как удалить заданное число символов в середине строки?
С помощью каких формул это можно сделать?
Чтобы поменять содержимое ячейки нужен макрос,а формулы не меняют содержание других ячеек, формулы только извлекают и обрабатывают информацию из ячеек, входящих в параметры формул.
Чтобы получить содержимое ячейки, содержащей текст ячейки с удаленным первым или последним символом используют функцию выделения подстроки:ПСТР(текст;начальный символ;количество символов)
для определения количества символов используют функцию длины строки ДЛСТР(текст)
если текст "абвгдежзиклмн" находится в ячейке A1 то формулы будут такими:
- строка с удаленным первым символом ячейки =ПСТР(A1;2;ДЛСТР(A1)-1) (результат бвгдежзиклмн)
- строка с удаленным последним символом ячейки =ПСТР(A1;1;ДЛСТР(A1)-1) (результат абвгдежзиклм)
- строка с удаленными первым и последним символами ячейки =ПСТР(A1;2;ДЛСТР(A1)-2) (результат бвгдежзиклм)
- часть строки в 5 символов , начиная с 3-го символа =ПСТР(A1;3;5) (результат вгдеж)
если нужно объединить несколько частей строки или частей разных строк, то подстроки можно соединять знаком &
например: вырезаем 3 символа, начиная с символа 3 и 4 символа, начиная с символа 7:
=ПСТР(A1;3;3)&ПСТР(A1;7;4) (результат вгджзик)
если нужно в результат включить строку, то она тоже добавляется символом &
=ПСТР(A1;3;3)&". "&ПСТР(A1;7;4) (результат вгд. жзик)
Если возникает необходимость удалить символы в ячейке или диапазоне ячеек Excel, то следует использовать специальные формулы.
Рассмотрим различные ситуации.
Удаление первого символа в ячейке
Для этого будем использовать формулу, состоящую из двух функций:
ПРАВСИМВ — возвращает заданное число символов справа.
ДЛСТР — длина строки.
ПРАВСИМВ имеет 2 аргумента:
1) Ссылка на ячейку, из которой нужно вернуть символы. Например, B3.
2) Количество символов. В нашем случае это будет вся строка за исключением первого символа. Поэтому пишем ДЛСТР(B3)-1.
Формула выглядит так:
ПРАВСИМВ(Ссылка на ячейку;ДЛСТР(Ссылка на ячейку)-1).
Удаление последнего символа в ячейке
Если в Excel нужно удалить не первый, а последний символ в ячейке, то нужно использовать функцию ЛЕВСИМВ.
Эта функция работает также, как и ПРАВСИМВ — только возвращает не правые символы, а левые.
Формула для удаления последнего символа будет такой:
ЛЕВСИМВ(Ссылка на ячейку;ДЛСТР(Ссылка на ячейку)-1).
Удаление первого и последнего символа в ячейке
Если требуется одновременно удалить первый и последний символ, то можно воспользоваться несколькими способами.
Во-первых, можно последовательно применить функции ПРАВСИМВ И ЛЕВСИМВ (с промежуточной ячейкой или без).
Также существует функция ПСТР, возвращающая заданное число символов в ячейке. Аргументы:
1) Ссылка на ячейку.
2) Начальная позиция. В нашем случае она равна 2, так как первый символ нас не интересует.
3) Количество знаков, которые нужно извлечь. Пишем ДЛСТР(B3)-2, так как нужно вернуть все символы из ячейки, кроме первого и последнего.
Формула будет такой:
Удаление заданного числа символов в ячейке справа или слева
Иногда бывает, что нужно удалить не 1 крайний символ в ячейке, а несколько.
В этом случае можно использовать всё те же функции, только в аргументах ЛЕВСИМВ и ПРАВСИМВ пишем не ДЛСТР(B3)-1, а ДЛСТР(B3)-n.
n — число символов, которые нужно удалить.
1) ПРАВСИМВ(B3;ДЛСТР(B3)-3). Удаляем 3 первых символа.
2) ЛЕВСИМВ(B3;ДЛСТР(B3)-5). Удаляем 5 символов с конца.
Удаление символов в середине ячейки
Для этого в Excel существует функция ЗАМЕНИТЬ. Она имеет 4 аргумента:
1) Ссылка на ячейку.
2) Начальная позиция. Например, если нам нужно удалять данные с 3 символа, то пишем "3".
3) Число знаков. Сюда пишем число удаляемых символов. Например, "3".
4) Новый текст. Так как нам нужно именно убрать данные символы, то пишем "" (две кавычки без пробела).
Таким образом, формула будет выглядеть так:
Она удаляет с 3 по 5 символ в ячейке.
Писала уже в одном из вопросов на БВ о работе со специальными формулами, их много, с их помощью много чего можно сделать, в данном вопросе обсуждается частный случай, рассмотрим его.
Посмотрим, как удалить первый символ в ячейке. Нам нужна формула из двух функций:
Пусть у нас будет вот такая таблица:
Мы видим, что во втором столбце у нас уже выполнено задание — удален первый символ. Формула использована вот такая: ПРАВСИМВ(B3;ДЛСТР(B3 )-1). То есть что мы с вами делаем: мы длину строки делаем меньше на один символ, при этом количество символов оставшееся программа считает справа, то есть лишним у нас оказывается левый, то есть первый символ, он и удаляется программой.
Несложно догадаться, что для удаления последнего символа нужно использовать немного другую формулу: ЛЕВСИМВ(B3;ДЛСТР(B3)-1) (применительно к этой же таблице. Для удаления первого и последнего символа
Таким же образом можно удалить несколько, а не один символ, справа или слева, просто пишем в формуле не (B3)-1, а, например, (B3)-3, и тогда удалится 3 символа справа или слева в соответствии с полной формулой.
Чтобы удалить символы в середине ячейки, используем другую формулу, применительно все к той же таблице, если мы хотим удалить три центральных символа, эта формула будет выглядеть так: ЗАМЕНИТЬ(B3;3;3;""), где В3, это ячейка с первоначальным текстом с полным набором символов, первая 3 — первый символ, который надо удалить, вторая 3 — количество символов, которые надо удалить, затем идут кавычки, так как это формула замены, то в них можно было бы указать символы, которые мы хотим поставить вместо удаленных, но мы хотим просто удалить три символа, поэтому в кавычках у нас ничего нет. Вот что получилось в итоге: