Как в ячейке Excel удалить символы справа
В этом примере Excel функции ЛЕВСИМВ мы рассмотрим, как удалить из текстовой строки некоторые символы начиная справа.
Общая формула
Описание
Чтобы удалить последние n символов из текстовой строки, вы можете использовать формулу, основанную на функциях ЛЕВСИМВ и ДЛСТР. Вы можете использовать подобную формулу, чтобы удалить последние 3 символа, последние 5 символов начиная справа.
В показанном выше примере в ячейке C5 приведена формула:
которая отсекает «евро» от каждого значения, возвращая только число.
Пояснение
Функция ЛЕВСИМВ идеально подходит для извлечения символов, начинающихся с левой стороны текстовой строки.
Мы используем ЛЕВСИМВ в этой формуле, чтобы извлечь все символы до количества символов, которое мы хотим извлечь.
Проблема для значений переменной длины состоит в том, что мы не знаем точно, сколько символов нужно извлечь.
Вот где используется функция ДЛСТР.
ДЛСТР вычисляет общую длину строки. Для ячейки B5 (5 евро) общая длина равна 6 символов. Чтобы получить количество извлекаемых символов, мы вычитаем 5, что составляет длину » евро», включая пробел. Результатом будет 1, которые передаются в ЛЕВСИМВ как количество символов для извлечения. ЛЕВСИМВ затем возвращает текст «5» как текстовое значение.
Наконец, поскольку нам нужно числовое значение (а не текст), мы пропускаем текст через функцию ЗНАЧЕН, которая преобразует числа в текстовом формате в фактические числа.
Шаги формулы выглядят так:
Как удалить первый символ в ячейке таблицы Excel
Часто перед пользователями табличного редактора Эксель встает такая задача, как удаление первого символа в ячейке таблички. Реализовать эту процедуру можно при помощи различных методов, например, используя специальные интегрированные операторы. В статье мы детально на примерах рассмотрим несколько методов, реализующих удаление символов в ячейке табличных данных.
Удаление первого символа в табличке Эксель
Для реализации этой несложной процедуры применяется специальная интегрированная функция. Подробная инструкция по удалению первого символа выглядит так:
Эта цифра для каждого поля рассчитывается путем вычитания одного знака из общего количества символов.
Дополнительно можно воспользоваться специальным оператором, имеющим наименование ПСТР. К примеру, у нас имеются данные, в ячейках которых указан порядковый номер работников. Нам необходимо произвести удаление первых символов до точки или же пробела. Формула будет выглядеть так: =ПСТР(А:А;ПОИСК(«.»;А:А)+2;ДЛСТР(А:А)-ПОИСК(«.»;А:А)).
Удаление знака до символа в табличном редакторе
Бывают такие ситуации, когда необходимо произвести удаление знаков до определенного символа в табличном документе. В этом случае применяется такая простая формула: =ЗАМЕНИТЬ(А1;1;ПОИСК(«символ»;А1);). Результат преобразований:
Дополнительно эту процедуру можно сочетать с очисткой данных «После».
Удаление символа до запятой в табличном редакторе
Бывают такие ситуации, когда необходимо произвести удаление знаков до запятой в табличном документе. В этом случае применяется такая простая формула: =ЗАМЕНИТЬ(А1;1;ПОИСК(«&»;А1);). Результат преобразований:
6
Удаление символов до пробела в табличном редакторе
Бывают такие ситуации, когда необходимо произвести удаление знаков до пробела в табличном документе. В этом случае применяется такая простая формула: =ЗАМЕНИТЬ(А1;1;ПОИСК(«&»;А1);). Результат преобразований:
7
Удаление при помощи оператора ПОДСТАВИТЬ
Удаление символов можно осуществить при помощи простого оператора, имеющего наименование ПОДСТАВИТЬ. Общий вид оператора: =ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения).
К примеру, если нам необходимо реализовать удаление точек, находящихся левее от основного текста, то нужно ввести такую формулу: =ПОДСТАВИТЬ(A1;».»;» «).
Используя эту формулу, мы заменим заданный знак, написанный левее от основного текста, на пробелы. Теперь нам необходимо реализовать удаление этих пробелов. Для осуществления этой процедуры используется оператор, имеющий наименование СЖПРОБЕЛЫ. Функция позволяет отыскать ненужные пробелы и удалить их. Общий вид оператора выглядит так: =СЖПРОБЕЛЫ().
Важно! Эта формула реализует удаление только обычных пробелов. К примеру, если пользователь добавил на рабочий лист скопированную с какого-нибудь сайта информацию, то она может содержать не пробелы, а знаки, похожие на них. В этом случае оператор СЖПРОБЕЛЫ не подойдет для удаления. Здесь нужно будет использовать инструмент «Найти и удалить».
Удаление при помощи оператора ПЕЧСИМВ
Дополнительно можно применить оператор ПЕЧСИМВ. Общий вид оператора по удалению непечатных символов выглядит так: =ПЕЧСИМВ(). Эта функция осуществляет удаление непечатных знаков в строчке (переносы строчек, символы абзаца, различные квадраты и так далее). Оператор необходим в тех случаях, когда требуется реализовать удаление переноса строки.
8
Важно! Оператор удаляет только большую часть лишних знаков.
Заключение и выводы об удаление первого символы
Мы рассмотрели методы, позволяющие удалить первый символ из табличной информации. Методы подразумевают использование интегрированных операторов. Использование функций позволяет значительно ускорить процесс работы с большими объёмами табличной информации.
Удалить первую букву / первые N символов в начале или конце ячейки в Excel
Как удалить первый символ, последний символ или несколько первых/последних символов в ячейке Excel? Эта статья поможет решить любую задачу.
Формулы удаления N символов в начале и конце строк
Для составления таких формул понадобятся функции ЛЕВСИМВ, ПРАВСИМВ и ДЛСТР. Первые две извлекают из текста указанное количество символов слева и справа соответственно, а ДЛСТР возвращает количество символов в тексте, к которому обращается.
Чтобы удалить из ячейки N символов слева, можно взять справа количество символов, равное разнице длины строки в символах (ДЛСТР) и N:
А чтобы удалить из ячейки N символов справа, наоборот, извлекаем слева длину строки минус N:
Удалить символы в начале ячейки (слева)
Если не уверены до конца в том, что делаете, рекомендуется все же перестраховываться, создавая дополнительный столбец.
«Отрезаем» несколько символов слева в ячейках столбца
Удалить символы в конце ячейки (справа):
Процедура потребуется, когда у вас массив фраз, в котором нужно обрезать последние буквы в конце строки, и вы уверены на 100% в их количестве.
Отрезаем символы справа в ячейках столбца
Хотите так же быстро удалять символы в Excel без использования формул, и еще более 500 похожих полезных инструментов по обработке текста? Проапгрейдите ваш Excel:
Удалить слова и символы по другим условиям
Надеюсь, на этой странице вы нашли то, что искали, и смогли решить задачу удаления последних или первых символов в ячейках.
Вас наверняка могут заинтересовать похожие операции, смотрите подробнее:
Функция ПРАВСИМВ в Excel — примеры и советы.
В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части текстовой строки. Как и другие функции для работы с текстом, ПРАВСИМВ очень проста и понятна. Тем не менее, у нее есть несколько неочевидных применений, которые могут оказаться полезными в вашей работе.
Синтаксис.
ПРАВСИМВ возвращает указанное количество символов от конца текста.
ПРАВСИМВ(текст; [число_знаков])
Например, чтобы извлечь последние 6 символов из ячейки A2, запишите:
Результат может выглядеть примерно так:
В реальных таблицах ПРАВСИМВ редко используется в одиночку. В большинстве случаев вы будете использовать ее вместе с другими функциями Excel в составе более сложных формул. Об этом и поговорим далее.
Как получить подстроку после определенного символа.
Скажем, ячейка A2 содержит имя и фамилию, разделенные пробелом, и вы хотите перенести фамилию в другую ячейку. Просто используйте приведенную выше общую формулу:
Формула даст следующий результат:
Аналогичным образом вы можете получить подстроку, которая следует за любым другим символом, например за запятой, точкой с запятой, дефисом и т. д. Например, чтобы извлечь то, что записано после дефиса, используйте выражение:
Результат будет выглядеть примерно так:
Как извлечь подстроку после последнего вхождения разделителя
При работе со сложными текстовыми выражениями, которые содержат несколько вхождений одного и того же разделителя, вам часто может потребоваться получить текст справа от последнего появления разделителя. Чтобы упростить понимание, взгляните на следующие исходные данные и желаемый результат:
рис4
Ключом к поиску решения является определение позиции последнего разделителя (последнее вхождение двоеточия в этом примере). Для этого вам нужно будет выполнить несколько несложных операций:
Чтобы убедиться, что формула работает правильно, вы можете ввести ее в отдельную ячейку, и результатом будет 2, то есть количество двоеточий в ячейке A2.
Если вы знакомы с синтаксисом функции ПОДСТАВИТЬ, вы можете помнить, что у нее есть 4-й необязательный аргумент (номер вхождения), который позволяет заменять только конкретное появление указанного символа. И поскольку мы уже вычислили количество разделителей, просто впишите рассмотренное выше выражение в четвертый аргумент функции ПОДСТАВИТЬ:
Если вы работаете с большим набором данных, где разные ячейки могут содержать различные разделители, вы можете заключить приведенную выше формулу в функцию ЕСЛИОШИБКА, чтобы предотвратить возможные сообщения об ошибках:
=ЕСЛИОШИБКА(ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(«$»; ПОДСТАВИТЬ(A2;»:»;»$»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»))))); A2)
Как показано на скриншоте ниже, формула работает отлично:
Замечание. В случае, если ячейка не содержит ни одного вхождения указанного разделителя, будет возвращена исходная строка целиком.
Как удалить первые N символов из ячейки
Помимо извлечения отрезка из конца строки, ПРАВСИМВ пригодится в ситуациях, когда вы хотите удалить определенное количество символов из её начала.
В наборе данных, использованном в предыдущем примере, вы можете удалить слово «ERROR», которое появляется в начале, и оставить только номер ошибки и описание. Для этого вычтите количество удаляемых символов из общей длины текста и передайте это число в аргумент количество_знаков:
В этом примере мы удаляем первые 6 символов (5 букв и двоеточие) из содержимого A2, поэтому наша формула выглядит следующим образом:
Может ли функция Excel ПРАВСИМВ вернуть число?
Например, чтобы извлечь последние 6 символов (почтовый индекс) из значения A2 и преобразовать их в число, используйте эту формулу:
На рисунке ниже показан результат. Обратите внимание на числа с выравниванием по правому краю в столбце B, в отличие от текстовых значений, которые выровнены по левому краю в столбце A:
Почему ПРАВСИМВ не работает с датами?
Предположим, у вас есть дата 9 августа 2020 года в ячейке A1. Если вы попытаетесь извлечь год с помощью формулы ПРАВСИМВ(A1,4), результатом будет 4052, что является последними четырьмя цифрами числа 44052, представляющего 9 августа 2020года в системе Excel.
На скриншоте показаны результаты:
Если ваши даты записаны в виде текста, что часто бывает при экспорте данных из других программ, то ничто не мешает вам использовать ПРАВСИМВ для извлечения последних нескольких символов, которые представляют определенную часть даты:
Теперь наша попытка извлечь год из даты вполне удачна.
Почему не работает? Причины и решения.
Это может быть связано с одной из следующих причин:
Вот как вы можете использовать ПРАВСИМВ в Excel.
Все описанные выше манипуляции с текстовыми значениями вы можете выполнить без формул при помощи специального инструмента работы с текстом, который является частью надстройки Ultimate Suite. Вы сможете без проблем избавиться от лишних пробелов и ненужных символов, поменять регистр букв, подсчитать символы и слова в ячейке или в диапазоне, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить его по отдельным ячейкам, извлечь отдельные слова, найти и удалить дубли слов.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге.
Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».
Бывают ситуации, когда необходимо отрезать/ удалить строго определенное количество символов в тексте справа или слева. Например, когда вы имеете список адресов, и вначале каждого адреса стоит шестизначный индекс. Нужно убрать почтовые индексы из списка, но через замену их не уберешь, так как они имеют совершенно разные значения и не находятся опцией замены.
Функции, которая отрезает лишнее количество символов в ячейке, найти не удается, но существует система из двух функций, которые можно применить. Это система из функции ЛЕВСИМВ (или ПРАВСИМВ) и функции ДЛСТР (длина строки).
Рассмотрим их применение.
Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.
Зная длину лишнего текста — 6 знаков в индексе, вычитаем их из общего числа символов посчитанных функцией ДЛСТР и получаем то количество символов, которое должно остаться в тексте, когда удалим индекс.
Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.
Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:
ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).
Где …*¹- адрес ячейки, из которой берем текст.
Похожее:
Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».: 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% с сохранением логического смысла?
Здравствуйте!
Есть столбец в Екселе со значениями типа 000010531, 000401325, 002000045, 000104573, т.п. Количество нулей разное. Требуется во всех значениях убрать спереди все нули, но только впереди. Не подскажете, как это сделать? Спасибо.