Функция ПРАВСИМВ в 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, т.п. Количество нулей разное. Требуется во всех значениях убрать спереди все нули, но только впереди. Не подскажете, как это сделать? Спасибо.
Как в ячейке Excel удалить символы справа
В этом примере Excel функции ЛЕВСИМВ мы рассмотрим, как удалить из текстовой строки некоторые символы начиная справа.
Общая формула
Описание
Чтобы удалить последние n символов из текстовой строки, вы можете использовать формулу, основанную на функциях ЛЕВСИМВ и ДЛСТР. Вы можете использовать подобную формулу, чтобы удалить последние 3 символа, последние 5 символов начиная справа.
В показанном выше примере в ячейке C5 приведена формула:
которая отсекает «евро» от каждого значения, возвращая только число.
Пояснение
Функция ЛЕВСИМВ идеально подходит для извлечения символов, начинающихся с левой стороны текстовой строки.
Мы используем ЛЕВСИМВ в этой формуле, чтобы извлечь все символы до количества символов, которое мы хотим извлечь.
Проблема для значений переменной длины состоит в том, что мы не знаем точно, сколько символов нужно извлечь.
Вот где используется функция ДЛСТР.
ДЛСТР вычисляет общую длину строки. Для ячейки B5 (5 евро) общая длина равна 6 символов. Чтобы получить количество извлекаемых символов, мы вычитаем 5, что составляет длину » евро», включая пробел. Результатом будет 1, которые передаются в ЛЕВСИМВ как количество символов для извлечения. ЛЕВСИМВ затем возвращает текст «5» как текстовое значение.
Наконец, поскольку нам нужно числовое значение (а не текст), мы пропускаем текст через функцию ЗНАЧЕН, которая преобразует числа в текстовом формате в фактические числа.
Шаги формулы выглядят так:
Удалить первую букву / первые N символов в начале или конце ячейки в Excel
Как удалить первый символ, последний символ или несколько первых/последних символов в ячейке Excel? Эта статья поможет решить любую задачу.
Формулы удаления N символов в начале и конце строк
Для составления таких формул понадобятся функции ЛЕВСИМВ, ПРАВСИМВ и ДЛСТР. Первые две извлекают из текста указанное количество символов слева и справа соответственно, а ДЛСТР возвращает количество символов в тексте, к которому обращается.
Чтобы удалить из ячейки N символов слева, можно взять справа количество символов, равное разнице длины строки в символах (ДЛСТР) и N:
А чтобы удалить из ячейки N символов справа, наоборот, извлекаем слева длину строки минус N:
Удалить символы в начале ячейки (слева)
Если не уверены до конца в том, что делаете, рекомендуется все же перестраховываться, создавая дополнительный столбец.
«Отрезаем» несколько символов слева в ячейках столбца
Удалить символы в конце ячейки (справа):
Процедура потребуется, когда у вас массив фраз, в котором нужно обрезать последние буквы в конце строки, и вы уверены на 100% в их количестве.
Отрезаем символы справа в ячейках столбца
Хотите так же быстро удалять символы в Excel без использования формул, и еще более 500 похожих полезных инструментов по обработке текста? Проапгрейдите ваш Excel:
Удалить слова и символы по другим условиям
Надеюсь, на этой странице вы нашли то, что искали, и смогли решить задачу удаления последних или первых символов в ячейках.
Вас наверняка могут заинтересовать похожие операции, смотрите подробнее:
Удалить лишние символы из ячеек Excel
Когда меня спрашивают, как удалить в Excel лишние символы, я не могу не задать ряд встречных вопросов:
В зависимости от ответов на эти вопросы, решений может быть много и разных. Где-то можно обойтись простейшими функциями, где-то подключить регулярные выражения, а где-то и вовсе не обойтись без готовых программных решений. Итак, по порядку.
Примеры лишних символов
Лишние пробелы
Наиболее часто ненужными считаются повторяющиеся пробелы между словами или пробелы в конце и начале ячейки. Как убрать их, можно узнать тут: Удалить лишние пробелы в Excel
Лишние символы справа / слева
Кто-то видит лишними символы справа или слева от основного текста в ячейке, желая отрезать их от него по позиции или по определенной границе.
Когда же границей является определенный символ и нужно удалить все что перед ним, читаем тут:
Цифры
Бывает, что ненужными символами становятся цифры, которых 10 и хочется более быстрый способ, чем очищать строки от них методом замены на пустоту. Как удалить цифры из текста в ячейках — этот раздел даст ответ на вопрос.
Латиница
Аналогично сложно удалить разом все буквы алфавита, которых 26 или 33 в случае с английскими и русскими символами соответственно. Подробные гайдлайны — Удалить латиницу в Excel.
Удалить все, кроме…
Часты случаи, когда лишними считаются вообще все символы, кроме определенных. Тут речь уже больше об извлечении нужных символов, а не об удалении ненужных.
Удалить все, кроме букв и цифр (удалить пунктуацию)
Удалить все символы, кроме букв и цифр, а иначе говоря, пунктуацию, нетривиальная задача, ведь их могут быть сотни! Но она решается, смотрите статью.
Удалить все, кроме цифр (извлечь цифры)
Номера телефонов, почтовые коды, числовые артикулы, IP адреса… иногда страдают от наличия в ячейках других символов, помимо цифр. Читайте: удалить все, кроме цифр в ячейках Excel
Другое
Хотите узнать, как удалять другие символы в Excel? Оставляйте комментарии под этой статьей.
Другие операции с символами в Excel
Не всегда нужны такие кардинальные меры, как удаление символов. Иногда необходимо просто обнаружить их наличие, извлечь или заменить на какие-то другие. В решении подобных задач вам помогут соответствующие разделы сайта:
Также Microsoft Excel способен на полную мощность задействовать возможности регулярных выражений. Буквы, цифры, знаки препинания, специальные символы — им подвластна работа с любыми данными. Подробнее читайте в статье — регулярные выражения в Excel.