Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».
Бывают ситуации, когда необходимо отрезать/ удалить строго определенное количество символов в тексте справа или слева. Например, когда вы имеете список адресов, и вначале каждого адреса стоит шестизначный индекс. Нужно убрать почтовые индексы из списка, но через замену их не уберешь, так как они имеют совершенно разные значения и не находятся опцией замены.
Функции, которая отрезает лишнее количество символов в ячейке, найти не удается, но существует система из двух функций, которые можно применить. Это система из функции ЛЕВСИМВ (или ПРАВСИМВ) и функции ДЛСТР (длина строки).
Рассмотрим их применение.
Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.
Зная длину лишнего текста — 6 знаков в индексе, вычитаем их из общего числа символов посчитанных функцией ДЛСТР и получаем то количество символов, которое должно остаться в тексте, когда удалим индекс.
Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.
Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:
ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).
Где …*¹- адрес ячейки, из которой берем текст.
Похожее:
- Как суммировать значения ячеек без учета знака — по модулю. Абсолютные величины — ABS.Суммирование значений по модулю (абсолютных величин чисел).
- Определение наличия товара на складах магазинов при помощи таблицы ExcelДля примера представим, что у нас есть.
- Как быстро поменять первые буквы на заглавные в ячейках ExcelФункция, меняющая первую букву на заглавную. Подробное.
Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».: 21 комментарий
Отличная статья. Помогла отнять лишние знаки из диапазона значений с различной длинной текста.
Например: у меня были значения «100.00» «95.00» 15.00″ «5.00» и т.д.
Решил вопрос формулой: =ЛЕВСИМВ(A1;(ДЛСТР(A1)-3))
готовая формула только подставить строки и кол-во символов)
спасибо!
Нужна помощь. Как удалить в каждой строчке все символы ПОСЛЕ указанного знака/знаков?
Добрый день. По аналогии с удалением слов описанном в статье «Копировать, убрать/удалить первое слово или несколько первых слов в ячейке Excel » только вместо пробелов подставить знак(и), о котором вы писали. ссылка — http://ruexcel.ru/slova/
Вот это гемор, и не получается, указываю диапазон ячейки. Укорачивает но в переносит текст в другую ячейку. В гугл таблицах, два раза мышкой щелкнуть.
Здравствуйте!
Есть колонка в Екселе с текстовыми значениями типа 00001, 00001А, 00624/1, 00682А, 01082А, 00240-1 и т.п. Требуется во всех значениях убрать впереди все нули. Но только впереди. Не подскажете, как это сделать? Спасибо.
Добрый день!
Большая таблица, в одном из столбцов указаны даты: 1995, 2006г., 1915, 1934г., 1946г., 2017, 2015 и т.д. Как убрать после цифр года символ «г.» во всех ячейках столбца, чтобы остались одни цифры года автоматически?
Благодарю заранее.
Добрый день.
Выделите ячейки, в которых нужно убрать лишние символы.
Нажмите ctrl+H
В поле «Найти:» введите «г.», в поле «Заменить…» не вводите ничего.
Нажмите «заменить все»
Здравствуйте!
Есть колонка в Excel с текстовыми значениями типа 38/18, 35/35, 32/25 и т.п. Требуется во всех значениях убрать впереди цифры и дробь. Точно также, только убрать сзади. Не подскажете, как это сделать? Спасибо.
Добрый день.
Если текст в ячейке H3, то:
=ЛЕВСИМВ(H3; (ПОИСК(«/»;H3)-1))
или
=ПРАВСИМВ(H3;(ДЛСТР(H3)-ПОИСК(«/»;H3)))
зависит от того, какую часть текста (левую или правую) нужно оставить в ячейке.
Здравствуйте. Подскажите, как из строки формата:
Вмк. У240кв. 20-70. 30м3. #39720. 15.40
поместить в отдельную ячейку цифру после символа #.
=ПРАВСИМВ(B2073;(ДЛСТР(B2073)-ПОИСК(«#»;B2073))), где B2073 — ячейка содержащая «Вмк. У240кв. 20-70. 30м3. #39720. 15.40»
Добрый день! Подскажите как текст в одной ячейке: Иванов Иван Иванович разбить на три ячейки: 1. Иванов 2. Иван 3. Иванович
Добрый день, подскажите, пожалуйста, как сократить количество текста в ячейках столбца на 15% с сохранением логического смысла?
Здравствуйте! У меня в столбике, в каждой строке, к примеру, по 15 символов (к примеру, 20.01.2020 17.45) а нужно сократить до 10 (чтобы осталась только дата) , но не в соседний столбец перенеся, как подразумевает функция ЛЕВСИМВ, а просто сократить в том же, не подскажете, как это сделать?
Здравствуйте!
Есть столбец в Екселе со значениями типа 000010531, 000401325, 002000045, 000104573, т.п. Количество нулей разное. Требуется во всех значениях убрать спереди все нули, но только впереди. Не подскажете, как это сделать? Спасибо.
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). Особенно полезна она, если нужно удалить перенос строки — мне всегда помогает.
Внимание! ПЕЧСИМВ способна удалить большинство, но не все лишние символы.