На чтение 5 мин Просмотров 11к. Опубликовано 16.05.2022
Люди, которые только начинают работать в Excel часто встречаются с таким вопросом.
Допустим, у нас есть такая табличка:
Примерно так выглядит удаление всех символов после «,».
Это можно сделать разными способами. Мы рассмотрим несколько.
Итак, начнём!
Содержание
- С помощью функции «Найти и заменить»
- С помощью формул
- С помощью функции «Заполнить»
- С помощью Visual Basic
С помощью функции «Найти и заменить»
Это, наверное, самый быстрый и удобный способ.
Допустим, у нас та же табличка и задача:
Пошаговая инструкция:
- Копируем и вставляем столбик А в В;
- Выделите столбик и щелкните «Главная»;
- Далее — «Найти и выделить» -> «Заменить…»;
- В первом параметре укажите «,*»;
- Второй параметр не меняйте;
- Щелкните «Заменить все».
Готово! Вот результат:
Как это работает?
Символ * означает неопределенное количество символов.
Так как мы используем «,*», то это значит, что программе нужно заменить запятую и все символы после неё на пустое место.
Это будет работать только если в каждой ячейке у вас одна запятая, если же у вас не одна, то первая и все остальные данные будут заменены на пустое место.
С помощью формул
Также, мы можем выполнить нашу задачу и с помощью формул.
Допустим, у нас есть такая табличка:
Формула принимает такой вид:
=ЛЕВСИМВ(A2;НАЙТИ(",";A2)-1)
Функция НАЙТИ возвращает порядковый номер запятой.
Это простой пример, давайте рассмотрим кое-что посложнее.
Теперь у нас такая табличка:
Формула, для этого примера, принимает такой вид:
=ЛЕВСИМВ(A2;НАЙТИ("!";ПОДСТАВИТЬ(A2;",";"!";2))-1)
Итак, также как в прошлый раз — не получится. Так как НАЙТИ будет возвращать порядковый номер первой запятой, а нам надо найти его для второй.
Мы используем небольшую хитрость, а если конкретнее, то заменяем вторую запятую на восклицательный знак, а затем с ним уже проводим операции.
И все бы хорошо, только в этом примере в каждой строке у нас ровно 2 запятые. А что делать если их неопределенное количество? Ведь в больших данных вы не будете выверять сколько запятой в каждой строке.
Вот пример:
Итак, нам нужно найти порядковый номер последней запятой, а после уже проводить с ней операции.
Для этого примера, формула принимает такой вид:
=ЛЕВСИМВ(A2;НАЙТИ("!";ПОДСТАВИТЬ(A2;",";"!";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;",";","))))-1)
Итак, функция ДЛСТР сначала находит количество символов в строчке с запятыми, а потом без них.
А после вычитает из первого — второе. Таким образом мы получаем количество запятых в строчке.
А затем мы заменяем последнюю на восклицательный знак.
Вот так вот можно заменять все после определенного символа с помощью формул. Конечно, с небольшими хитростями.
Плюс этого метода в том, что данные будут динамичны. То есть если что-то поменяется в изначальных данных, все поменяется и в данных после обработки.
С помощью функции «Заполнить»
Функция «Заполнить», это довольно давний инструмент. Он может помочь нам и в этом случае.
Как он работает?
Очень просто — вы просто делаете что угодно и после используете функцию. Она пытается понять логику ваших действий и продолжить её.
Давайте рассмотрим пример.
Допустим, у нас есть та же табличка:
Пошаговая инструкция:
- В первую ячейку столбика В введите то, что должно получиться после обработки;
- В следующую ячейку, то же самое;
- А теперь выделите столбик;
- И щелкните на «Главная» -> «Заполнить» -> «Мгновенное заполнение»;
Готово! Вот результат:
Эту функцию, естественно, можно использовать не только для удаления текста после символа. Она работает там, где есть логика.
Однако, иногда, она может ошибаться. Поэтому всегда проверяйте то, что получилось после обработки.
С помощью Visual Basic
И, как обычно, разберем вариант с помощью Visual Basic.
Мы создадим свою собственную функцию и будем использовать её для обработки данных.
Это крайне удобно, если вы делаете что-либо очень часто. Например, как в нашем случае, удаляете данные после символа.
Код Visual Basic:
Function LastPosition(rCell As Range, rChar As String)
'This function gives the last position of the specified character
'This code has been developed by Sumit Bansal (https://trumpexcel.com)
Dim rLen As Integer
rLen = Len(rCell)
For i = rLen To 1 Step -1
If Mid(rCell, i - 1, 1) = rChar Then
LastPosition = i - 1
Exit Function
End If
Next i E
nd FunctionКод, чтобы он работал, нужно вставить в Visual Basic -> «Insert» -> «Module».
Давайте рассмотрим пример её использования.
Допустим, у нас есть такая табличка. Формула принимает такой вид:
=ЛЕВСИМВ(A2;LastPosition(A2;",")-1)
В нашей функции, первым аргументом мы указали диапазон для поиска, а вторым символ, последнюю позицию которого нам нужно найти.
С помощью Visual Basic все проще.
Вот и все! Если вам нужно сделать что-то подобное 1-2 раза, то лучше всего использовать функцию «Найти и заменить…», а если вы делаете это постоянно, то используйте Visual Basic.
Надеюсь, эта статья оказалась полезна для вас!
В учебнике показано, как использовать функции подстроки в Excel для извлечения текста из ячейки, получения подстроки до или после указанного символа, найти ячейки, содержащие часть строки, и многое другое.
Прежде чем мы начнем обсуждать различные методы работы с подстроками в Excel, давайте уделим немного времени определению термина, чтобы мы могли начать с той же страницы. Итак, что такое подстрока? Проще говоря, это часть текстовой записи. Например, если вы наберете что-то вроде «AA-111» в ячейке, вы назовете это буквенно-цифровая строкаи любая часть строки, скажем, «AA», будет подстрока.
Хотя в Excel нет такой функции, как функция подстроки, существуют три текстовые функции (LEFT, RIGHT и MID) для извлечения подстроки заданной длины. Кроме того, есть функции НАЙТИ и ПОИСК для получения подстроки до или после определенного символа. И есть несколько других функций для выполнения более сложных операций, таких как извлечение чисел из строки, замена одной подстроки на другую, поиск частичного совпадения текста и т. д. Ниже вы найдете примеры формул для выполнения всего этого и многого другого. .
Microsoft Excel предоставляет три различные функции для извлечения текста заданной длины из ячейки. В зависимости от того, где вы хотите начать извлечение, используйте одну из следующих формул:
- Функция LEFT — извлечь подстроку слева.
- Функция ПРАВО — извлечь текст справа.
- Функция MID — для извлечения подстроки из середины текстовой строки, начиная с указанной вами точки.
Как и в случае с другими формулами, функции работы с подстроками в Excel лучше всего изучать на примере, поэтому давайте рассмотрим несколько из них.
Чтобы извлечь текст слева от строки, вы используете функцию Excel LEFT:
СЛЕВА(текст, [num_chars])
Где текст адрес ячейки, содержащей исходную строку, и num_chars количество символов, которые вы хотите извлечь.
Например, чтобы получить первые 4 символа из начала текстовой строки, используйте следующую формулу:
=ЛЕВО(A2,4)
Получить подстроку с конца строки (ВПРАВО)
Чтобы получить подстроку из правой части текстовой строки, воспользуйтесь функцией Excel ПРАВИЛЬНО:
ПРАВИЛЬНО(текст, [num_chars])
Например, чтобы получить последние 4 символа с конца строки, используйте следующую формулу:
=ВПРАВО(A2,4)
Если вы хотите извлечь подстроку, начинающуюся в середине строки, в указанной вами позиции, то MID — это функция, на которую вы можете положиться.
По сравнению с двумя другими текстовыми функциями, MID имеет несколько иной синтаксис:
MID(текст, start_num, num_chars)
Помимо текст (исходная текстовая строка) и num_chars (количество символов для извлечения), вы также указываете start_num (отправная точка).
В нашем примере набора данных, чтобы получить три символа из середины строки, начинающейся с 6-го символа, вы используете следующую формулу:
=СРЕДНЕЕ(A2,6,3)
Кончик. Вывод формул Right, Left и Mid всегда является текстом, даже если вы извлекаете число из текстовой строки. Если вы хотите оперировать результатом как числом, оберните формулу в функцию ЗНАЧЕНИЕ следующим образом:
= ЗНАЧЕНИЕ (СРЕДНЕЕ (A2,6,3))
Как показано в приведенных выше примерах, функции Left, Right и Mid прекрасно справляются с однородными строками. Когда вы имеете дело с текстовыми строками переменной длины, потребуются более сложные манипуляции.
Примечание. Во всех приведенных ниже примерах мы будем использовать функцию SEARCH без учета регистра, чтобы получить позицию символа. Если вам нужна формула с учетом регистра, используйте функцию НАЙТИ.
Как извлечь текст перед определенным символом
Чтобы получить подстроку, предшествующую заданному символу, нужно сделать две вещи: сначала определить позицию интересующего символа, а затем вытянуть все символы перед ним. Точнее, вы используете функцию ПОИСК, чтобы найти положение символа, и вычитаете 1 из результата, потому что вы не хотите включать сам символ в вывод. А затем вы отправляете возвращенный номер непосредственно на num_chars аргумент ЛЕВОЙ функции:
ОСТАВИЛ(клеткаПОИСК(«уголь«, клетка)-1)
Например, чтобы извлечь подстроку перед символом дефиса (-) из ячейки A2, используйте следующую формулу:
=ВЛЕВО(A2, ПОИСК(«-«,A2)-1)
Независимо от того, сколько символов содержит ваша строка Excel, формула извлекает только текст до первого дефиса:
Чтобы получить текст после определенного символа, вы используете несколько иной подход: получите позицию символа с помощью ПОИСК или НАЙТИ, вычтите это число из общей длины строки, возвращаемой функцией ДЛСТР, и извлеките столько же символов из конца строки. Струна.
ПРАВИЛЬНО(клетка,ЛЕН(клетка)-ПОИСК(«уголь«, клетка))
В нашем примере мы будем использовать следующую формулу для извлечения подстроки после первого дефиса:
=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(«-«,A2))
Как извлечь текст между двумя экземплярами символа
Чтобы получить подстроку между двумя вхождениями определенного символа, используйте следующую общую формулу:
СРЕДНЯЯ(клеткаПОИСК(«уголь«, клетка)+1, ПОИСК («уголь«, клеткаПОИСК («чар», клетка)+1) — ПОИСК («уголь«, клетка)-1)
Первые два аргумента этой формулы MID предельно ясны:
Текст это ячейка, содержащая исходную текстовую строку.
Start_num (начальная точка) — простая формула ПОИСК возвращает позицию нужного символа, к которой вы добавляете 1, потому что хотите начать извлечение со следующего символа.
Num_chars (количество символов для извлечения) — самая сложная часть:
- Во-первых, вы определяете положение второго вхождения символа, вкладывая одну функцию поиска в другую.
- После этого вы вычитаете позицию 1-го вхождения из позиции 2-го вхождения и вычитаете 1 из результата, так как вы не хотите включать символ-разделитель в результирующую подстроку.
Например, чтобы извлечь текст, окруженный двумя дефисами, вы должны использовать эту формулу:
=СРЕДН(A2, ПОИСК(«-«,A2) + 1, ПОИСК(«-«,A2,ПОИСК(«-«,A2)+1) — ПОИСК(«-«,A2) — 1)
На скриншоте ниже показан результат:
Если вы хотите извлечь текст между 2-м и 3-м или 3-м и 4-м вхождением одного и того же символа, вы можете использовать более компактную комбинацию ПОИСК ПОДСТАВКИ, чтобы получить позицию символа, как описано в разделе Как найти N-е вхождение символа в строку. :
НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ(клетка,персонаж,СИМВОЛ(1),N-е вхождение))
В нашем случае мы могли бы извлечь подстроку между 2-м и 3-м дефисом по следующей формуле:
=MID(A2, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,»-«,СИМВОЛ(1),2))+1, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,»-«,СИМВОЛ(1) ,3)) — НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ(A2,»-«,СИМВОЛ(1),2))-1)
В ситуациях, когда вы не хотите извлекать подстроку, а хотите найти только ячейки, содержащие ее, вы используете функцию ПОИСК или НАЙТИ, как показано в приведенных выше примерах, но выполняете поиск внутри функции ЕСНИМ. Если ячейка содержит подстроку, функция поиска возвращает позицию первого символа, и если ISNUMBER получает любое число, она возвращает TRUE. Если подстрока не найдена, поиск приводит к ошибке, заставляя ISNUMBER возвращать FALSE.
IНОМЕР(ПОИСК(«подстрока«, клетка))
Предположим, у вас есть список британских почтовых индексов в столбце A, и вы хотите найти те, которые содержат подстроку «1ZZ». Чтобы это сделать, используйте эту формулу:
=ISNUMBER(ПОИСК(«1zz», A2))
Результаты будут выглядеть примерно так:
Если вы хотите вернуть собственное сообщение вместо логических значений ИСТИНА и ЛОЖЬ, вложите приведенную выше формулу в функцию ЕСЛИ:
=ЕСЛИ(IЧИСЛО(ПОИСК(«1zz», A2)), «Да», «»)
Если ячейка содержит подстроку, формула возвращает «Да», в противном случае — пустую строку («»):
Как вы помните, функция ПОИСК в Excel нечувствительна к регистру, поэтому вы используете ее, когда регистр символов не имеет значения. Чтобы ваша формула различала прописные и строчные буквы, выберите функцию НАЙТИ с учетом регистра.
Дополнительные сведения о том, как найти текст и числа в Excel, см. в разделе Если ячейка содержит примеры формул.
Как вы только что видели, Microsoft Excel предоставляет множество различных функций для работы с текстовыми строками. Если вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте задание нашему Ultimate Suite for Excel. Имея эти инструменты в своем арсенале Excel, вы просто переходите к Данные об аблебитах вкладка > Текст группу и нажмите Извлечь:
Теперь вы выбираете исходные ячейки и любые сложные строки, которые они содержат, извлечение подстроки сводится к этим двум простым действиям:
- Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после заданного символа.
- Нажмите Вставить результаты. Сделанный!
Например, чтобы получить доменные имена из списка адресов электронной почты, выберите Все после текста переключатель и введите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите Все перед текстом переключатель, как показано на скриншоте ниже.
И через мгновение вы получите следующие результаты:
Помимо скорости и простоты, инструмент Извлечь текст имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции работы с подстроками в частности. Как? Установив флажок «Вставить как формулу» в нижней части панели, вы гарантируете, что результаты выводятся в виде формул, а не значений.
В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:
- Чтобы извлечь имя пользователя:
=ЕСЛИОШИБКА(СЛЕВА(A2,ПОИСК(«@»,A2)-1),»»)
- Чтобы извлечь домен:
=ЕСЛИОШИБКА(ПРАВО(A2, ДЛСТР(A2)- ПОИСК(«@»,A2) — ДЛСТР(«@») + 1),»»)
Сколько времени у вас ушло бы на то, чтобы вычислить эти формулы самостоятельно? 😉
Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные строки будут внесены какие-либо изменения. Когда в ваш набор данных добавляются новые записи, вы можете копировать формулы в другие ячейки, как обычно, без необходимости заново запускать инструмент «Извлечь текст».
Если вам интересно попробовать это, а также многие другие полезные функции, включенные в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию.
Дополнительные формулы для подстрок в Excel
В этом уроке мы продемонстрировали некоторые классические формулы Excel для извлечения текста из строки. Как вы понимаете, может быть почти бесконечное количество вариаций этих базовых сценариев. Ниже вы найдете еще несколько примеров формул, в которых вам пригодятся функции Text.
Доступные загрузки
Функции работы с подстроками в Excel — практическая рабочая тетрадь (файл .xlsx)
Ultimate Suite — пробная версия (файл .exe)
Skip to content
В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части текстовой строки. Как и другие функции для работы с текстом, ПРАВСИМВ очень проста и понятна. Тем не менее, у нее есть несколько неочевидных применений, которые могут оказаться полезными в вашей работе.
- Правила синтаксиса
- Как вернуть текст после определенного символа
- Извлекаем текст после последнего вхождения разделителя
- Как удалить первые N знаков?
- Как извлечь число при помощи ПРАВСИМВ?
- Почему ПРАВСИМВ не работает с датами?
- Почему не работает? Причины и решения.
Синтаксис.
ПРАВСИМВ возвращает указанное количество символов от конца текста.
Правила написания:
ПРАВСИМВ(текст; [число_знаков])
Где:
- Текст (обязательно) — текст, из которого вы хотите извлечь символы.
- число_знаков (необязательно) — количество символов для извлечения, начиная с самого правого символа.
- Если аргумент опущен, возвращается один последний символ (по умолчанию).
- Когда число знаков для извлечения больше, чем общее количество символов в ячейке, возвращается весь текст.
- Если введено отрицательное число, формула возвращает ошибку #ЗНАЧ!.
Например, чтобы извлечь последние 6 символов из ячейки A2, запишите:
=ПРАВСИМВ(A2; 6)
Результат может выглядеть примерно так:
Важное замечание! ПРАВСИМВ всегда возвращает текст, даже если исходное значение является числом. Чтобы заставить формулу выводить число, используйте ее в сочетании с ЗНАЧЕН, как показано в этом примере .
В реальных таблицах ПРАВСИМВ редко используется в одиночку. В большинстве случаев вы будете использовать ее вместе с другими функциями Excel в составе более сложных формул. Об этом и поговорим далее.
Как получить подстроку после определенного символа.
Если вы хотите извлечь все знаки, следующие за определенным символом, используйте формулу ПОИСК или НАЙТИ, чтобы определить позицию этого символа, вычтите позицию из общей длины текста, возвращаемой функцией ДЛСТР , и отделите это количество символов от его правого края.
ПРАВСИМВ( текст; ДЛСТР( текст ) — ПОИСК( символ ; текст ))
Скажем, ячейка A2 содержит имя и фамилию, разделенные пробелом, и вы хотите перенести фамилию в другую ячейку. Просто используйте приведенную выше общую формулу:
=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(» «;A2))
Формула даст следующий результат:
Аналогичным образом вы можете получить подстроку, которая следует за любым другим символом, например за запятой, точкой с запятой, дефисом и т. д. Например, чтобы извлечь то, что записано после дефиса, используйте выражение:
=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(«-«;A2))
Результат будет выглядеть примерно так:
Как извлечь подстроку после последнего вхождения разделителя
При работе со сложными текстовыми выражениями, которые содержат несколько вхождений одного и того же разделителя, вам часто может потребоваться получить текст справа от последнего появления разделителя. Чтобы упростить понимание, взгляните на следующие исходные данные и желаемый результат:
рис4
Как вы можете видеть на скриншоте выше, столбец A содержит список ошибок. Ваша цель — получить описание ошибки, которое идет после последнего двоеточия. Дополнительная сложность заключается в том, что исходные значения могут содержать разное количество разделителей, например, A3 содержит 3 двоеточия, а A5 — только одно.
Ключом к поиску решения является определение позиции последнего разделителя (последнее вхождение двоеточия в этом примере). Для этого вам нужно будет выполнить несколько несложных операций:
- Подсчитайте количество разделителей в исходной строке. Это несложно:
- Вычисляете общую ее длину с помощью ДЛСТР(A2).
- Определяем длину без разделителей, используя формулу ПОДСТАВИТЬ, которая заменяет все вхождения двоеточия ничем: ДЛСТР(ПОДСТАВИТЬ(A2; «:»; «»))
- Наконец, вы вычитаете длину исходной строки без разделителей из общей длины: ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»))
Чтобы убедиться, что формула работает правильно, вы можете ввести ее в отдельную ячейку, и результатом будет 2, то есть количество двоеточий в ячейке A2.
- Замените последний разделитель на какой-нибудь уникальный символ. Чтобы извлечь текст, который идет после последнего разделителя, нам нужно каким-то образом «пометить» это последнее вхождение разделителя. Для этого давайте заменим последнее вхождение двоеточия символом, который нигде не встречается в исходных значениях, например, знаком доллара ($).
Если вы знакомы с синтаксисом функции ПОДСТАВИТЬ, вы можете помнить, что у нее есть 4-й необязательный аргумент (номер вхождения), который позволяет заменять только конкретное появление указанного символа. И поскольку мы уже вычислили количество разделителей, просто впишите рассмотренное выше выражение в четвертый аргумент функции ПОДСТАВИТЬ:
=ПОДСТАВИТЬ(A2;»:»;»$»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»)))
Если вы поместите эту формулу в отдельную ячейку, она вернет: ERROR: 432 $ Connection timed out
- Определяем позицию последнего разделителя. В зависимости от того, на какой символ вы заменили последний разделитель, используйте ПОИСК (без учета регистра) или НАЙТИ (с учетом регистра), чтобы определить позицию этого символа. Мы заменили последнее двоеточие на знак $, поэтому используем следующую формулу, чтобы узнать его местоположение:
ПОИСК(«$»; ПОДСТАВИТЬ(A2;»:»;»$»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»)))))
В этом примере формула возвращает 10, что является позицией $ в измененном тексте.
- Получаем подстроку справа от последнего разделителя. Теперь, когда вы знаете позицию последнего разделителя, все, что вам нужно сделать, это вычесть это число из общей длины строки и использовать ПРАВСИМВ, чтобы вернуть это количество символов из конца исходного текста:
ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(«$»; ПОДСТАВИТЬ(A2;»:»;»$»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»)))))
Если вы работаете с большим набором данных, где разные ячейки могут содержать различные разделители, вы можете заключить приведенную выше формулу в функцию ЕСЛИОШИБКА, чтобы предотвратить возможные сообщения об ошибках:
=ЕСЛИОШИБКА(ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(«$»; ПОДСТАВИТЬ(A2;»:»;»$»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»))))); A2)
Как показано на скриншоте ниже, формула работает отлично:
Замечание. В случае, если ячейка не содержит ни одного вхождения указанного разделителя, будет возвращена исходная строка целиком.
Как удалить первые N символов из ячейки
Помимо извлечения отрезка из конца строки, ПРАВСИМВ пригодится в ситуациях, когда вы хотите удалить определенное количество символов из её начала.
В наборе данных, использованном в предыдущем примере, вы можете удалить слово «ERROR», которое появляется в начале, и оставить только номер ошибки и описание. Для этого вычтите количество удаляемых символов из общей длины текста и передайте это число в аргумент количество_знаков:
ПРАВСИМВ( текст; ДЛСТР (текст) — число_символов_для_удаления )
В этом примере мы удаляем первые 6 символов (5 букв и двоеточие) из содержимого A2, поэтому наша формула выглядит следующим образом:
=ПРАВСИМВ(A2; ДЛСТР(A2)-6)
Может ли функция Excel ПРАВСИМВ вернуть число?
Как упоминалось в начале этого руководства, ПРАВСИМВ в Excel всегда возвращает текст, даже если исходное значение является числом. Но что, если вы работаете с числовым набором данных и хотите, чтобы результат тоже был числовым? Простой обходной путь — вложить формулу ПРАВСИМВ в функцию ЗНАЧЕН, которая специально разработана для преобразования текста, состоящего из цифр, в число.
Например, чтобы извлечь последние 6 символов (почтовый индекс) из значения A2 и преобразовать их в число, используйте эту формулу:
=ЗНАЧЕН(ПРАВСИМВ(A2; 6))
На рисунке ниже показан результат. Обратите внимание на числа с выравниванием по правому краю в столбце B, в отличие от текстовых значений, которые выровнены по левому краю в столбце A:
Почему ПРАВСИМВ не работает с датами?
Потому что она предназначена для работы с текстовыми значениями, тогда как даты на самом деле являются числами во внутренней системе Excel. Формула ПРАВСИМВ не может получить отдельную часть даты — день, месяц или год. Если вы попытаетесь это сделать, все, что вы получите, — это несколько последних цифр числа, представляющего дату.
Предположим, у вас есть дата 9 августа 2020 года в ячейке A1. Если вы попытаетесь извлечь год с помощью формулы ПРАВСИМВ(A1,4), результатом будет 4052, что является последними четырьмя цифрами числа 44052, представляющего 9 августа 2020года в системе Excel.
«Итак, как мне получить определенную часть даты?», — спросите вы меня. Используя одно из следующих выражений:
- Функция ДЕНЬ для извлечения дня: = ДЕНЬ(A1)
- Функция МЕСЯЦ, чтобы получить месяц: = МЕСЯЦ(A1)
- ГОД, чтобы вытащить год: = ГОД(A1)
На скриншоте показаны результаты:
Если ваши даты записаны в виде текста, что часто бывает при экспорте данных из других программ, то ничто не мешает вам использовать ПРАВСИМВ для извлечения последних нескольких символов, которые представляют определенную часть даты:
Теперь наша попытка извлечь год из даты вполне удачна.
Почему не работает? Причины и решения.
Это может быть связано с одной из следующих причин:
- В исходных данных есть один или несколько концевых пробелов. Чтобы быстро удалить лишние пробелы в ячейках, используйте функцию СЖПРОБЕЛЫ.
- Аргумент число_знаков меньше нуля. Конечно, вряд ли вам захочется специально добавлять отрицательное число в формулу, но если этот аргумент вычисляется какой-то формулой Excel, и вы получаете ошибку #ЗНАЧ!, то обязательно проверьте вложенные выражения на наличие ошибок.
- Исходное значение — дата. Если вы внимательно следовали этому руководству, вы уже знаете, почему формула ПРАВСИМВ не может работать с датами.
Вот как вы можете использовать ПРАВСИМВ в Excel.
Все описанные выше манипуляции с текстовыми значениями вы можете выполнить без формул при помощи специального инструмента работы с текстом, который является частью надстройки Ultimate Suite. Вы сможете без проблем избавиться от лишних пробелов и ненужных символов, поменять регистр букв, подсчитать символы и слова в ячейке или в диапазоне, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить его по отдельным ячейкам, извлечь отдельные слова, найти и удалить дубли слов.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге.
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в… 
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… 
Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с… 
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… 
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди… 
5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)… 
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Еще…Меньше
Windows: 2208 (сборка 15601)
Mac: 16.65 (сборка 220911)
Веб: представлено 15 сентября 2022
г.
iOS: 2.65 (сборка 220905)
Android: 16.0.15629
Возвращает текст, следующий за заданным символом или строкой. Это функция, обратная функции ТЕКСТДО.
Синтаксис
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Синтаксис функции ТЕКСТПОСЛЕ поддерживает следующие аргументы:
текст Текст, в котором производится поиск. Использовать подстановочные знаки не разрешено. Обязательный.
разделитель Текст, помечающий точку, после которой нужно извлечь текст. Обязательный.
номер_вхождения Экземпляр разделителя, после которого вы хотите извлечь текст. По умолчанию номер_вхождения = 1. При отрицательном значении поиск в тексте начинается с конца. Необязательный.
match_mode Определяет, учитывается ли регистр в текстовом поиске. По умолчанию регистр учитывается. Необязательный. Введите одно из следующих значений:
-
0 С учетом регистра.
-
1 Без учета регистра.
match_end Рассматривает конец текста как разделитель. По умолчанию текст является точным совпадением. Необязательный. Введите одно из следующих значений:
-
0 Не сопоставляйте разделитель с концом текста.
-
1 Сопоставьте разделитель с концом текста.
if_not_found Значение возвращается, если совпадение не найдено. По умолчанию возвращается значение #N/A. Необязательный.
Замечания
При поиске с пустым значением разделителя, ТЕКСТПОСЛЕ сразу же совпадает. Он возвращает весь текст при поиске спереди (номер_вхождения положительный) и пустой текст при поиске с конца (если номер_вхождения отрицательный).
|
Пример |
Результат |
|---|---|
|
=ТЕКСТПОСЛЕ(«Красной шапочки, красная шапочка, «, «шапочка») |
‘s, красная шапочка |
|
=ТЕКСТПОСЛЕ(«Красной шапочки, красная шапочка», «») |
Красной шапочки, красная шапочка |
|
=ТЕКСТПОСЛЕ(«Красная шапочка, красная шапочка», «», -1) |
Ошибки
-
Excel возвращает ошибку #Н/Д, если разделитель не содержится в тексте.
-
Excel возвращает ошибку #ЗНАЧ! если instance_num = 0 или его значение превышает длину текста.
-
Excel возвращает ошибку #Н/Д, если номер_вхождения больше количества вхождений разделителя.
Примеры
Скопируйте данные примеров из приведенных ниже таблиц и вставьте их в ячейку A1 нового листа Excel. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Данные |
||
|
красная шапочка Красной шапочки |
||
|
красная шапочка Красной шапочки |
||
|
Формулы |
Результаты |
|
|
=TEXTAFTER(A2,»Red») |
шапочка Красной шапочки |
|
|
=ТЕКСТПОСЛЕ(A2,»корзинка») |
#Н/Д |
|
|
=ТЕКСТПОСЛЕ(A3,»красная»,2) |
шапочка |
|
|
=TEXTAFTER(A3,»red»,-2) |
шапочка Красной шапочки |
|
|
=TEXTAFTER(A3,»Red»,,FALSE) |
#Н/Д |
|
|
=TEXTAFTER(A2,»red»,3) |
#Н/Д |
|
Данные |
|
|
Марк Аврелий |
|
|
Сократ |
|
|
Иммануил Кант |
|
|
Формулы |
Результаты |
|
=ТЕКСТПОСЛЕ(A2;» «;;;1) |
Аврелий |
|
=ТЕКСТПОСЛЕ(A3;» «;;;0) |
#Н/Д |
|
=ТЕКСТПОСЛЕ(A3;» «;;;1) |
|
|
=ЕСЛИ(ТЕКСТПОСЛЕ(A3;» «;;;1)=»»;A3) |
Сократ |
|
=ТЕКСТПОСЛЕ(A4;» «;;;1) |
Кант |
См. также
Текстовые функции (справочник)
Функция ТЕКСТДО
ФУНКЦИЯ TEXTSPLIT
Нужна дополнительная помощь?
ТРЕНИНГИ
Быстрый старт
Расширенный Excel
Мастер Формул
Прогнозирование
Визуализация
Макросы на VBA
КНИГИ
Готовые решения
Мастер Формул
Скульптор данных
ВИДЕОУРОКИ
Бизнес-анализ
Выпадающие списки
Даты и время
Диаграммы
Диапазоны
Дубликаты
Защита данных
Интернет, email
Книги, листы
Макросы
Сводные таблицы
Текст
Форматирование
Функции
Всякое
Коротко
Подробно
Версии
Вопрос-Ответ
Скачать
Купить
ПРОЕКТЫ
ОНЛАЙН-КУРСЫ
ФОРУМ
Excel
Работа
PLEX
© Николай Павлов, Planetaexcel, 2006-2022
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
Техническая поддержка сайта
|
ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |