Excel вырезает текст между символами

Обновлено: 21.11.2024

Прежде чем мы начнем обсуждать различные методы работы с подстроками в Excel, давайте уделим немного времени определению термина, чтобы мы могли начать с той же страницы. Итак, что такое подстрока? Проще говоря, это часть текстовой записи. Например, если вы введете в ячейку что-то вроде "AA-111", вы назовете ее буквенно-цифровой строкой, а любая часть строки, скажем, "AA", будет подстрока.

Хотя в Excel нет такой функции, как функция "Подстрока", существуют три функции "Текст" (ЛЕВАЯ, ПРАВАЯ и СРЕДНЯЯ) для извлечения подстроки заданной длины. Кроме того, есть функции НАЙТИ и ПОИСК для получения подстроки до или после определенного символа. И есть несколько других функций для выполнения более сложных операций, таких как извлечение чисел из строки, замена одной подстроки на другую, поиск частичного совпадения текста и т. д. Ниже вы найдете примеры формул для выполнения всего этого и многого другого. .

Как извлечь подстроку определенной длины

Microsoft Excel предоставляет три различные функции для извлечения текста заданной длины из ячейки. В зависимости от того, где вы хотите начать извлечение, используйте одну из следующих формул:

    - извлечь подстроку слева. - извлечь текст справа. - для извлечения подстроки из середины текстовой строки, начиная с указанной вами точки.

Как и в случае с другими формулами, функции работы с подстроками Excel лучше всего изучать на примере, поэтому давайте рассмотрим несколько из них.

Извлечь подстроку из начала строки (СЛЕВА)

Чтобы извлечь текст слева от строки, используйте функцию Excel НАЛЕВО:

Где text — это адрес ячейки, содержащей исходную строку, а num_chars — количество символов, которые вы хотите извлечь.

Например, чтобы получить первые 4 символа из начала текстовой строки, используйте следующую формулу:

Получить подстроку с конца строки (ВПРАВО)

Чтобы получить подстроку из правой части текстовой строки, используйте функцию Excel ПРАВИЛЬНО:

Например, чтобы получить последние 4 символа с конца строки, используйте следующую формулу:

Извлечь текст из середины строки (MID)

Если вы хотите извлечь подстроку, начинающуюся в середине строки, в указанной вами позиции, MID — это функция, на которую вы можете положиться.

По сравнению с двумя другими текстовыми функциями, MID имеет несколько иной синтаксис:

Помимо text (исходная текстовая строка) и num_chars (количество символов для извлечения), вы также указываете start_num (значение отправная точка).

В нашем примере набора данных, чтобы получить три символа из середины строки, начинающейся с 6-го символа, вы используете следующую формулу:

Совет. Вывод формул Right, Left и Mid всегда является текстом, даже если вы извлекаете число из текстовой строки. Если вы хотите оперировать результатом как числом, оберните формулу функцией ЗНАЧЕНИЕ следующим образом: =ЗНАЧ(СРЕДН(A2,6,3))

Извлечь подстроку до или после заданного символа

Как показано в приведенных выше примерах, функции Left, Right и Mid прекрасно справляются с однородными строками. Когда вы имеете дело с текстовыми строками переменной длины, потребуются более сложные манипуляции.

Примечание. Во всех приведенных ниже примерах мы будем использовать функцию SEARCH без учета регистра, чтобы получить позицию символа. Если вам нужна формула с учетом регистра, используйте функцию НАЙТИ.

Как извлечь текст перед определенным символом

Чтобы получить подстроку, предшествующую заданному символу, необходимо сделать две вещи: во-первых, определить позицию интересующего символа, а затем вытянуть все символы перед ним. Точнее, вы используете функцию ПОИСК, чтобы найти положение символа, и вычитаете 1 из результата, потому что вы не хотите включать сам символ в вывод. Затем вы отправляете возвращенное число непосредственно в аргумент num_chars функции LEFT:

Например, чтобы извлечь подстроку перед символом дефиса (-) из ячейки A2, используйте следующую формулу:

Независимо от того, сколько символов содержит ваша строка Excel, формула извлекает только текст до первого дефиса:

Как извлечь текст после символа

Чтобы получить текст после определенного символа, вы используете несколько иной подход: получите позицию символа с помощью ПОИСК или НАЙТИ, вычтите это число из общей длины строки, возвращаемой функцией ДЛСТР, и извлеките это количество символов из конец строки.

В нашем примере мы будем использовать следующую формулу для извлечения подстроки после первого дефиса:

Как извлечь текст между двумя экземплярами символа

Чтобы получить подстроку между двумя вхождениями определенного символа, используйте следующую общую формулу:

Первые два аргумента этой формулы MID предельно ясны:

Текст – это ячейка, содержащая исходную текстовую строку.

Начальный_номер (начальная точка) — простая формула ПОИСК возвращает позицию нужного символа, к которой вы добавляете 1, потому что хотите начать извлечение со следующего символа.

Num_chars (количество символов для извлечения) — самая сложная часть:

  • Во-первых, вы определяете положение второго вхождения символа, вкладывая одну функцию поиска в другую.
  • После этого вы вычитаете позицию 1-го вхождения из позиции 2-го вхождения и вычитаете 1 из результата, так как вы не хотите включать символ-разделитель в результирующую подстроку.

Например, чтобы извлечь текст, заключенный в два дефиса, используйте следующую формулу:

=MID(A2, ПОИСК("-",A2) + 1, ПОИСК("-",A2,ПОИСК("-",A2)+1) - ПОИСК("-",A2) - 1)

На снимке экрана ниже показан результат:

Если вы хотите извлечь текст между 2-м и 3-м или 3-м и 4-м вхождением одного и того же символа, вы можете использовать более компактную комбинацию ПОДСТАВКИ ПОИСКА, чтобы получить позицию символа, как описано в разделе Как найти N-й появление символа в строке:

В нашем случае мы могли бы извлечь подстроку между 2-м и 3-м дефисом по следующей формуле:

=MID(A2, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,"-",СИМВОЛ(1),2))+1, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,"-",СИМВОЛ (1),3)) - НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ(A2,"-",СИМВОЛ(1),2))-1)

Как найти подстроку в Excel

В ситуациях, когда вы не хотите извлекать подстроку, а хотите только найти ячейки, содержащие ее, вы используете функцию ПОИСК или НАЙТИ, как показано в приведенных выше примерах, но выполняете поиск в рамках функции ЕЧИСЛО. Если ячейка содержит подстроку, функция поиска возвращает позицию первого символа, и если ISNUMBER получает какое-либо число, она возвращает TRUE. Если подстрока не найдена, поиск приводит к ошибке, заставляя ISNUMBER возвращать FALSE.

Предположим, у вас есть список британских почтовых индексов в столбце A, и вы хотите найти те, которые содержат подстроку "1ZZ". Для этого используйте следующую формулу:

Результаты будут выглядеть примерно так:

Если вы хотите вернуть собственное сообщение вместо логических значений ИСТИНА и ЛОЖЬ, вложите приведенную выше формулу в функцию ЕСЛИ:

=IF(ISNUMBER(ПОИСК("1zz", A2)), "Да", "")

Если ячейка содержит подстроку, формула возвращает "Да", пустую строку (""), в противном случае:

Как вы помните, функция ПОИСК в Excel нечувствительна к регистру, поэтому вы используете ее, когда регистр символов не имеет значения. Чтобы формула различала прописные и строчные буквы, выберите функцию НАЙТИ с учетом регистра.

Дополнительную информацию о том, как найти текст и числа в Excel, см. в разделе Если ячейка содержит примеры формул.

Как извлечь текст из ячейки с помощью Ultimate Suite for Excel

Как вы только что видели, Microsoft Excel предоставляет множество различных функций для работы с текстовыми строками. Если вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте задание нашему Ultimate Suite for Excel. Имея эти инструменты в своем арсенале Excel, просто перейдите на вкладку Данные Ablebits > группу Текст и нажмите Извлечь:

Теперь вы выбираете исходные ячейки и любые сложные строки, которые они содержат, извлечение подстроки сводится к этим двум простым действиям:

  1. Укажите, сколько символов вы хотите получить от начала, конца или середины строки; или выберите извлечение всего текста до или после заданного символа.
  2. Нажмите Вставить результаты. Готово!

Например, чтобы извлечь доменные имена из списка адресов электронной почты, выберите переключатель Все после текста и введите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все перед текстом, как показано на снимке экрана ниже.

И через мгновение вы получите следующие результаты:

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции работы с подстроками в частности. Как? Установив флажок Вставить как формулу в нижней части панели, вы гарантируете, что результаты выводятся в виде формул, а не значений.

В этом примере, если вы выберите ячейки B2 и C2, вы увидите следующие формулы соответственно:

    Чтобы извлечь имя пользователя:

Сколько времени вам потребуется, чтобы самостоятельно вычислить эти формулы? ;)

Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные строки будут внесены какие-либо изменения. Когда в набор данных добавляются новые записи, вы можете копировать формулы в другие ячейки, как обычно, без необходимости заново запускать инструмент «Извлечь текст».

Если вам интересно попробовать это, а также многие другие полезные функции, включенные в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию.

Дополнительные формулы для подстрок в Excel

В этом руководстве мы продемонстрировали некоторые классические формулы Excel для извлечения текста из строки. Как вы понимаете, может быть почти бесконечное количество вариаций этих базовых сценариев. Ниже вы найдете еще несколько примеров формул, в которых функции Text могут пригодиться.

    - примеры формул для получения числа из различных текстовых строк. - как использовать MID в сочетании с 4 другими функциями, чтобы получить любое N-е слово из ячейки. - еще одна нетривиальная формула MID, которая извлекает слово, содержащее определенный символ (символы), из любой точки исходной строки. - используя этот подход, вы можете получить подстроку между любыми двумя указанными вами символами. - как использовать функцию подстроки Excel (LEFT, RIGHT или MID) в сочетании с Vlookup для извлечения значений на основе частичного совпадения. - формула для поиска и замены определенного символа или подстроки в текстовой строке. - как использовать функцию СЧЕТЕСЛИ с подстановочными знаками для подсчета ячеек, содержащих определенный текст, т.е. подсчет с частичным совпадением. - как извлекать текст и специальные символы с помощью регулярных выражений.

Доступные загрузки

215 комментариев к "Подстрока Excel: как извлечь текст из ячейки"

У меня есть данные в этом формате на листе Excel.

Мне нужен результат в виде любых данных, кроме начальных 7 символов.

Здравствуйте!
Чтобы извлечь часть текста, начинающуюся с определенной позиции, используйте функцию MID -

Буду признателен за любую помощь. У меня есть большой объем следующих данных (расположенных в одной ячейке);

"Выигрышная комбинация: 2/1/1/1/1/2/1/1/3/1
Статус: Официальный
Результаты: (9/10)
Победители: 2015.43
Дивиденды: 42,60 рандов
Результаты: (10/10)
Победители: 141,00
Дивиденды: 2 439,80 рандов"

Мне нужно извлечь;

2015,43
42,60
141
2439,80

Здравствуйте!
Чтобы извлечь числа из текста, вы можете использовать регулярные выражения. Вы можете найти подробные инструкции и примеры в этой статье: Как извлечь подстроки в Excel с помощью регулярных выражений (Regex).

Вы можете помочь? Я могу найти только инструкции по извлечению а) после одного символа (не строки) и никаких инструкций по указанию длины извлекаемой строки.
Спасибо!!

Здравствуйте!
Используйте следующую формулу -

В статье выше есть вся необходимая информация. Надеюсь, это будет полезно.

Спасибо за это!! Я искал что-то подобное.

Здравствуйте!
Чтобы заменить сообщение об ошибке значением, используйте функцию ЕСЛИОШИБКА.
Вы можете использовать эту формулу:

Как я могу выбрать сахар и лен из этой формулы

"Молоко, сахар, растительное масло, рапсовое масло, сухое молоко, эссенция кардамона, цельнозерновая мука, семена льна, изюм, сахар, миндаль, кешью"

Какую формулу я могу использовать для извлечения только символов в ячейке без ПОСЛЕДНИХ 4 ЦИФР
например, "Добавка витамина А 6-11 месяцев 2019" результаты должны быть "Добавка витамина А 6-11 месяцев"
>и
результаты "LLIN для беременных женщин 2021" должны быть "LLIN для беременных женщин"

Здравствуйте!
Извлечь из текста все символы, кроме последних четырех. Используйте функции ВЛЕВО и ДЛСТР.

Чтобы разделить текстовую строку по определенному символу с помощью формулы, вы можете использовать комбинацию функций ВЛЕВО, ВПРАВО, ДЛСТР и НАЙТИ. В показанном примере формула в C5 выглядит так:

И формула в D5:

В этом примере цель состоит в том, чтобы разделить текстовую строку по символу подчеркивания ("_") с помощью формулы. Обратите внимание, что расположение подчеркивания в каждой строке разное. Это означает, что формула должна сначала найти положение символа подчеркивания, прежде чем будет извлечен какой-либо текст.

Левая сторона

Чтобы извлечь текст слева от подчеркивания, используйте формулу в C5:

В этой формуле используется функция НАЙТИ, чтобы найти символ подчеркивания ("_") в тексте, а затем вычитается 1, чтобы вернуться на один символ назад:

НАЙТИ возвращает 11, поэтому результат после вычитания 1 равен 10. Этот результат передается в функцию LEFT в качестве аргумента num_chars, количества символов, которые нужно извлечь из B5, начиная с первого символа. слева:

Функция LEFT возвращает строку "Оценка" в качестве конечного результата.

Правая сторона

Чтобы извлечь текст справа от подчеркивания, мы используем функцию ПРАВИЛЬНО:

Как и выше, в этой формуле также используется функция НАЙТИ, чтобы найти символ подчеркивания ("_") в позиции 11. Однако в этом случае мы хотим извлечь текст с правой стороны строки. строка, поэтому нам нужно рассчитать количество символов, которые нужно извлечь из справа. Это делается путем вычитания результата НАЙТИ (11) из общей длины текста в ячейке B5 (21), рассчитанной функцией ДЛСТР:

Результат равен 10, который возвращается функции RIGHT как num_chars, количество символов, которое нужно извлечь из справа:

Конечным результатом в D5 является строка "10 января".

Поскольку приведенные выше формулы копируются вниз, они извлекают текст слева и справа от знака подчеркивания в каждой строке, создавая результаты, показанные в столбцах C и D.

Чтобы извлечь текст между любыми символами, мы можем использовать формулу, основанную на функциях MID и FIND.

Например, чтобы извлечь данные между скобками, мы будем использовать следующую формулу:

Разбивка формулы

Эта формула работает следующим образом: функция MID извлекает количество символов из текстовой строки между началом скобки «(» и закрывающей скобкой «)». Для этого нам понадобятся позиции скобок или круглых скобок. И мы воспользуемся функцией НАЙТИ, чтобы узнать эти позиции.

Давайте разберем формулу:

Функции НАЙТИ

Функция First FIND находит позицию левой скобки «(» в текстовой строке. Мы добавили 1 к результату функции FIND, чтобы исключить левую скобку при извлечении текста.

Результат этой части формулы будет служить start_num или вторым аргументом функции MID.

Вторая функция НАЙТИ находит позицию правой скобки «)» в текстовой строке.

Мы добавляем третью функцию НАЙТИ для вычитания из второй функции НАЙТИ.Это дает нам длину текста между скобками.

И результирующее значение этой формулы будет третьим и последним аргументом (аргумент num_chars) функции MID.

Функция MID

Значения обоих этих аргументов вводятся в функцию MID. Это даст нам текст между скобками.

Функция ОБРЕЗКИ

Мы также можем добавить функцию TRIM перед функцией MID, чтобы удалить все лишние пробелы.

Извлечение текста между символами в Google Таблицах

Формула для извлечения текста между символами работает точно так же в Google Таблицах, как и в Excel:

Практический лист Excel

Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!

В последних нескольких статьях мы рассмотрели различные способы удаления символов из строк в Excel. Сегодня мы рассмотрим еще один вариант использования — как удалить все до или после определенного символа.

Удалить текст до, после или между двумя символами с помощью функции «Найти и заменить»

Для манипуляций с данными в нескольких ячейках лучше всего подходит инструмент «Найти и заменить». Чтобы удалить часть строки, предшествующую или следующую за определенным символом, выполните следующие действия:

  1. Выделите все ячейки, в которых вы хотите удалить текст.
  2. Нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить.
  3. В поле Найти введите одну из следующих комбинаций:
    • Чтобы удалить текст перед заданным символом, введите символ, перед которым стоит звездочка (*char).
    • Чтобы удалить текст после определенного символа, введите символ, за которым следует звездочка (char*).
    • Чтобы удалить подстроку между двумя символами, введите звездочку, окруженную двумя символами (char*char).
  4. Оставьте поле Заменитьна пустым.
  5. Нажмите Заменить все.

Например, чтобы удалить все после запятой, включая саму запятую, поставьте запятую и знак звездочки (,*) в поле Найти что, и вы получите следующий результат:

Чтобы удалить подстроку перед запятой, введите звездочку, запятую и пробел (*, ) в поле Найти.

Обратите внимание, что мы заменяем не только запятую, но и запятую, и пробел, чтобы избежать начальных пробелов в результатах. Если ваши данные разделены запятыми без пробелов, используйте звездочку, за которой следует запятая (*,).

Чтобы удалить текст между двумя запятыми, используйте звездочку, окруженную запятыми (,*,).

Совет. Если вы предпочитаете, чтобы имена и номера телефонов были разделены запятой, введите запятую (,) в поле Заменить на.

Удалить часть текста с помощью Flash Fill

В современных версиях Excel (2013 и более поздних версиях) есть еще один простой способ удалить текст, который предшествует определенному символу или следует за ним, — функция мгновенного заполнения. Вот как это работает:

  1. В ячейке рядом с первой ячейкой с вашими данными введите ожидаемый результат и нажмите Enter .
  2. Начните вводить соответствующее значение в следующей ячейке.Как только Excel обнаружит шаблон во введенных вами значениях, он отобразит предварительный просмотр для остальных ячеек, следующих тому же шаблону.
  3. Нажмите клавишу Enter, чтобы принять предложение.

Готово!

Удалить текст с помощью формул

В Microsoft Excel многие операции с данными, выполняемые с помощью встроенных функций, также можно выполнять с помощью формулы. В отличие от предыдущих методов, формулы не вносят никаких изменений в исходные данные и дают вам больший контроль над результатами.

Как удалить все после определенного символа

Чтобы удалить текст после определенного символа, используйте общую формулу:

Здесь мы используем функцию ПОИСК, чтобы получить позицию символа и передать ее функции ВЛЕВО, чтобы она извлекла соответствующее количество символов из начала строки. Один символ вычитается из числа, возвращаемого функцией ПОИСК, чтобы исключить разделитель из результатов.

Например, чтобы удалить часть строки после запятой, введите приведенную ниже формулу в ячейке B2 и перетащите ее вниз через ячейку B7:

=ЛЕВО(A2, ПОИСК(",", A2) -1)

Как удалить все до определенного символа

Чтобы удалить часть текстовой строки перед определенным символом, используйте общую формулу:

Здесь мы снова вычисляем позицию целевого символа с помощью ПОИСКА, вычитаем ее из общей длины строки, возвращаемой функцией ДЛСТР, и передаем разницу в функцию ПРАВИЛЬНО, поэтому она извлекает столько символов с конца строка.

Например, чтобы удалить текст перед запятой, используйте следующую формулу:

=ВПРАВО(A2, ДЛСТР(A2) - ПОИСК(",", A2))

В нашем случае за запятой следует пробел. Чтобы избежать начальных пробелов в результатах, мы заключаем основную формулу в функцию TRIM:

=ОТРЕЗАТЬ(ПРАВО(A2, ДЛСТР(A2) - ПОИСК(",", A2)))

  • В обоих приведенных выше примерах предполагается, что в исходной строке есть только один экземпляр разделителя. При наличии нескольких вхождений текст будет удален до/после первого вхождения.
  • Функция ПОИСК не чувствительна к регистру, то есть она не делает различий между строчными и прописными буквами. Если вашим конкретным символом является буква и вы хотите различать регистр букв, используйте функцию НАЙТИ с учетом регистра вместо ПОИСК.

Как удалить текст после N-го появления символа

В ситуации, когда исходная строка содержит несколько экземпляров разделителя, вам может потребоваться удалить текст после определенного экземпляра. Для этого используйте следующую формулу:

Где n — это вхождение символа, после которого следует удалить текст.

Например, чтобы удалить все после второй запятой в A2 (и самой запятой), используйте следующую формулу:

Как работает эта формула:

Ключевой частью формулы является функция НАЙТИ, которая вычисляет положение n-го разделителя (в нашем случае запятой). Вот как:

Заменяем 2-ю запятую в A2 символом решетки (или любым другим символом, которого нет в ваших данных) с помощью ПОДСТАВКИ:

Это число передается непосредственно аргументу num_chars функции LEFT, запрашивая получение первых 12 символов из A2:

Как удалить текст перед N-м вхождением символа

Общая формула для удаления подстроки перед определенным символом:

Например, чтобы удалить текст перед второй запятой в A2, используйте следующую формулу:

Чтобы исключить начальный пробел, мы снова используем функцию TRIM в качестве оболочки:

Как работает эта формула:

Итак, мы выясняем, сколько символов стоит после n-го разделителя, и извлекаем справа подстроку соответствующей длины. Ниже приведена расшифровка формулы:

Во-первых, мы заменяем вторую запятую в A2 символом решетки:

Результирующая строка помещается в аргумент text функции RIGHT:

Далее нам нужно определить, сколько символов нужно извлечь из конца строки. Для этого мы находим позицию символа решетки в приведенной выше строке (равной 13):

И вычтите его из общей длины строки (равной 28):

Разница (15) переходит ко второму аргументу RIGHT, предписывая извлечь последние 15 символов из строки в первом аргументе:

Вывод представляет собой подстроку "(102) 123-4568", которая очень близка к желаемому результату, за исключением начального пробела. Итак, мы используем функцию TRIM, чтобы избавиться от него.

Как удалить текст после последнего вхождения символа

Если ваши значения разделены переменным числом разделителей, вы можете удалить все после последнего экземпляра этого разделителя. Это можно сделать по следующей формуле:

Предположим, что столбец A содержит различную информацию о сотрудниках, но значение после последней запятой всегда является номером телефона. Ваша цель — удалить номера телефонов и сохранить все остальные данные.

Для достижения цели вы можете удалить текст после последней запятой в A2 с помощью этой формулы:

Скопируйте формулу вниз по столбцу, и вы получите следующий результат:

Как работает эта формула:

Суть формулы заключается в том, что мы определяем положение последнего разделителя (запятой) в строке и вытягиваем подстроку слева вверх до разделителя. Получение позиции разделителя — самая сложная часть, и вот как мы с этим справимся:

Сначала мы узнаем, сколько запятых в исходной строке. Для этого заменяем каждую запятую ничем ("") и подаем полученную строку в функцию ДЛСТР:

Для A2 результат равен 35 — количеству символов в A2 без запятых.

Вычтите указанное выше число из общей длины строки (38 символов):

… и вы получите 3, что является общим количеством запятых в A2 (а также порядковым номером последней запятой).

Далее вы используете уже знакомую комбинацию функций НАЙТИ и ПОДСТАВИТЬ, чтобы получить позицию последней запятой в строке. Номер экземпляра (3-я запятая в нашем случае) предоставляется вышеупомянутой формулой LEN SUBSTITUTE:

Похоже, что 3-я запятая — это 23-й символ в A2, то есть нам нужно извлечь 22 предшествующих ей символа. Таким образом, мы помещаем приведенную выше формулу минус 1 в аргумент num_chars функции LEFT:

Как удалить текст перед последним вхождением символа

Чтобы удалить все до последнего экземпляра определенного символа, используйте общую формулу:

В нашем образце таблицы для удаления текста перед последней запятой формула принимает следующий вид:

В качестве последнего штриха мы вкладываем его в функцию TRIM, чтобы убрать начальные пробелы:

Как работает эта формула:

Итак, мы получаем позицию последней запятой, как описано в предыдущем примере, и вычитаем ее из общей длины строки:

В результате мы получаем количество символов после последней запятой и передаем его в функцию ПРАВИЛЬНО, поэтому она возвращает столько символов с конца строки.

Пользовательская функция для удаления текста по обе стороны от символа

Как вы видели в приведенных выше примерах, вы можете решить практически любой вариант использования, используя собственные функции Excel в различных комбинациях. Проблема в том, что вам нужно запомнить несколько хитрых формул. Хм, а что, если мы напишем собственную функцию для всех сценариев? Звучит как хорошая идея. Итак, добавьте следующий код VBA в свою книгу (подробные шаги по вставке VBA в Excel здесь):

Наша функция называется RemoveText и имеет следующий синтаксис:

Строка — исходная текстовая строка. Может быть представлен ссылкой на ячейку.

Разделитель - символ, до/после которого следует удалить текст.

Вхождение — экземпляр разделителя.

Is_after — логическое значение, указывающее, с какой стороны разделителя следует удалить текст. Может быть одиночным символом или последовательностью символов.

  • TRUE — удалить все после разделителя (включая сам разделитель).
  • FALSE — удалить все до разделителя (включая сам разделитель).

После того как код функции будет вставлен в книгу, вы сможете удалять подстроки из ячеек, используя компактные и элегантные формулы.

Например, чтобы стереть все после первой запятой в A2, формула в B2 будет следующей:

=УдалитьТекст(A3, ", ", 1, ИСТИНА)

Чтобы удалить все до первой запятой в A2, используйте формулу в C2:

=УдалитьТекст(A3, ", ", 1, ЛОЖЬ)

Поскольку наша пользовательская функция принимает строку в качестве разделителя, мы ставим запятую и пробел (",") во 2-м аргументе, чтобы впоследствии избавить себя от необходимости обрезать начальные пробелы.

Наша пользовательская функция прекрасно работает, не так ли? Но если вы думаете, что это комплексное решение, то следующего примера вы еще не видели :)

Удалить все до, после или между символами

Чтобы получить еще больше возможностей для удаления отдельных символов или текста из нескольких ячеек по совпадению или положению, добавьте наш Ultimate Suite в свой набор инструментов Excel.

Здесь мы более подробно рассмотрим функцию «Удалить по положению», расположенную на вкладке Данные Ablebits > группа Текст > Удалить .

Ниже мы рассмотрим два наиболее распространенных сценария.

Удалить все до или после определенного текста

Предположим, что все ваши исходные строки содержат какое-то общее слово или текст, и вы хотите удалить все до или после этого текста. Для этого выберите исходные данные, запустите инструмент Удалить по положению и настройте его, как показано ниже:

  1. Выберите параметр Все символы перед текстом или Все символы после текста и введите ключевой текст (или символ) в поле рядом с ним.
  2. В зависимости от того, следует ли рассматривать прописные и строчные буквы как разные или как одни и те же символы, установите или снимите флажок С учетом регистра.
  3. Нажмите Удалить.

В этом примере мы удаляем все символы, предшествующие слову "ошибка" в ячейках A2:A8:

И получить именно тот результат, который нам нужен:

Удалить текст между двумя символами

В ситуации, когда нерелевантная информация находится между двумя определенными символами, вы можете быстро удалить ее следующим образом:

В качестве примера мы удаляем все, что находится между двумя символами тильды (~), и в результате получаем идеально очищенные строки:

Чтобы попробовать другие полезные функции, включенные в этот многофункциональный инструмент, я рекомендую вам загрузить ознакомительную версию в конце этого поста. Спасибо за внимание и надеемся увидеть вас в нашем блоге на следующей неделе!

Читайте также: