Как заменить все после / до определенного символа или пробела в Excel?

Формула для удаления непечатных символов в Excel

Рассмотрим пример. Предположим, у нас есть список сотрудников компании, и мы хотим отфильтровать только тех сотрудников, чья фамилия начинается с определенной буквы (например, с буквы «p»):

Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).
К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).
Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

Использование ПСТР

Основной обязанностью оператора DLP является извлечение заданного количества символов, включая пробелы, из заданного количества символов слева. Функция относится к категории текстовых операторов. Вот ее синтаксис:

Как видно, эта формула имеет три аргумента. Все они являются обязательными.

Есть аргумент с именем «Text», который содержит адрес элемента листа, содержащего текстовое выражение с выделенными символами.

Аргумент «Начальная позиция» указывает, с какой цифры счета, начиная слева, следует начинать извлечение. Первая цифра «1», вторая цифра «2» и т.д. При вычислении учитываются даже пробелы.

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

Пример 1: единичное извлечение

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

Используя эту таблицу, мы можем увидеть, кто работает в компании. Есть три столбца, содержащие имена, фамилии и отчества сотрудников. С помощью оператора PSTR мы должны извлечь только фамилию первого человека в списке Петра Ивановича Николаева и поместить ее в соответствующую ячейку.

Выделяем элемент листа, в который будет производиться извлечение. Щелкаем по кнопке «Вставить функцию», которая расположена около строки формул.

В поле «Текст» введите координаты ячейки, содержащей имя сотрудника. Чтобы не набирать адрес вручную, просто поместите курсор в поле и щелкните левой кнопкой мыши на элементе листа, содержащем нужные данные.

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

В поле «Количество символов» введите количество символов, из которых состоит имя. Он состоит из восьми символов. Однако, поскольку после фамилии в ячейке больше нет символов, мы можем ввести больше символов. Это означает, что в нашем случае мы можем вставить любое число, равное или большее восьми. Например, мы поместим число «10». Однако если бы после имени в ячейке было больше слов, цифр или других символов, мы бы просто ввели точное количество символов («8»).

Пример 2: групповое извлечение

Однако в данном случае практичнее ввести одну фамилию вручную, чем использовать формулу. Если вы передаете группу данных, то функция будет вполне целесообразной.

У нас есть список смартфонов. Перед названием каждой модели стоит слово «Smartphone». Нам нужно поместить в отдельный столбец только названия моделей без этого слова.

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

В поле «Текст» необходимо указать адрес первого элемента в колонке с исходными данными.

В поле «Начальная позиция» необходимо указать количество символов, из которых будут извлечены данные. В нашем случае в каждой ячейке есть слово «Smartphone» и пробел перед названием модели. Таким образом, предложение, которое необходимо извлечь в отдельную ячейку, везде начинается с десятого символа. Поставьте в это поле цифру «10».

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

Если вы хотите «отсоединить» результат от исходного столбца, выполните следующее действие. Выберите столбец, содержащий формулу. Затем перейдите на вкладку «Главная» и нажмите на значок «Копировать» в блоке «Буфер обмена» ленты.

Пример 3: использование комбинации операторов

Тем не менее, приведенный выше пример ограничен тем фактом, что первое слово во всех исходных ячейках должно содержать одинаковое количество символов. Если использовать операторы SEARCH или SEARCHING в сочетании с функцией PSTR, возможности применения формулы значительно расширятся.

В просмотренном тексте операторы SEARCH и find text возвращают позиции указанных символов.

S EARCH синтаксически выглядит следующим образом:

Синтаксис оператора SEARCH выглядит следующим образом:

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

Давайте посмотрим, как использовать оператор SEARCH с функцией SARP. У нас есть таблица, содержащая названия различных моделей компьютерного оборудования с общим названием. Как и в прошлый раз, нам нужно получить имена моделей без общего имени. Сложность заключается в том, что если в предыдущем примере родовое имя было одинаковым для всех позиций («смартфон»), то в этом списке оно разное («компьютер», «монитор», «колонки» и т.д.) с разным количеством символов. Чтобы решить эту проблему, нам понадобится оператор SEARCH, который мы поместим в функцию FTP.

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

В поле «Текст поиска» укажите ссылку на первую ячейку столбца, содержащего исходные данные. Эта ссылка будет идентична той, которая была указана ранее в поле «Текст» окна аргументов BTP.

Поле «Стартовая позиция» является необязательным. Его не нужно заполнять или можно установить значение «1». Если выбран любой из этих вариантов, поиск будет начинаться с начала текста.

Пожалуйста, введите в поле «Количество символов» любое число, большее или равное самому длинному выражению из исходного столбца. Например, мы ввели число «50». В нашем случае этого вполне достаточно.

Функция ПОИСКПОЗ используется с формулой PSTR так же, как и оператор ПОИСКПОЗ.

Как видите, функция SARP — очень удобный инструмент для вывода нужных данных в заранее указанную ячейку. Ее ограниченная популярность отчасти объясняется тем, что многие пользователи при работе с Excel больше внимания уделяют вычислительным функциям, чем текстовым. Функциональность этой формулы еще больше возрастает, если ее комбинировать с другими операторами.

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12677 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Поблагодарить автора, поделившись статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Цель этой заметки — описать, как удалить несколько первых или последних символов или некоторые определенные позиционные символы из текста в Microsoft Excel. Наши темы будут включать:

1. Удалить первые символы текстовой колонки
2. Удалить последние символы текстовой колонки
3. Удалить первый и последний символы текстовой колонки.

Удалите первые несколько символов из столбца текстов

Используя этот синтаксис, удалите первые несколько символов из колонки текста:

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

Например. Если у нас есть колонка с полным текстом с ячейки A3 по A7, и нам нужен текст после удаления первых 2 символов в колонке C, формула будет выглядеть следующим образом

Введите эту формулу в ячейку C3. Нажмите Enter, и текст в ячейке A3 будет отображен без первых двух символов в ячейке C3. Щелкните в любом месте за пределами ячейки C3, затем вернитесь в ячейку C3, чтобы выделить опцию «Заливка». Теперь перетащите формулу в ячейку C7. Это даст тексты без первых двух символов в колонке C для исходных текстов в колонке A.

Удалите последние несколько символов из столбца текстов

Синтаксис для удаления последних символов из колонки текста:

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

Например. Рассмотрим случай, когда у нас есть столбец с полным текстом от ячейки A3 до A7, и нам нужен текст после удаления последних 9 символов в столбце D; формула будет выглядеть следующим образом

Теперь напишите эту формулу в ячейке D3. Нажмите Enter, и на экране появится текст в ячейке A3 без последних 9 символов в ячейке D3. Если вы щелкните в любом месте вне ячейки D3, вы вернетесь в ячейку D3, где вы увидите опцию «Заполнить». Теперь перетащите формулу в ячейку D7. Это даст тексты без последних 9 символов в столбце D для исходных текстов в столбце A.

Удалите как первые несколько, так и последние несколько символов из столбца текстов

Если вы хотите удалить несколько первых и последних символов из колонки текста, синтаксис формулы будет выглядеть так

Например. Если у нас есть столбец с полными текстами в столбце A с ячейки A3 по A7 и нам нужны тексты без первых 2 символов и последних 9 символов в столбце E с ячейки E3 по E7, то формула будет выглядеть следующим образом

Запишите эту формулу в ячейку E3. Когда вы нажмете Enter, текст в ячейке A3 появится без первых 2 и последних 9 символов из ячейки E3. Щелкните где-нибудь за пределами ячейки E3, затем вернитесь в ячейку E3, чтобы проверить параметр Заполнить. Теперь перетащите формулу в ячейку E7. В результате исходные тексты из столбца A будут перенесены в столбец E без первых 2 и последних 9 символов.

Надеюсь, эта статья поможет вам удалить первые или последние символы или определенные элементы из текста в Microsoft Excel.

Введение

В этой статье я представлю лучший способ извлечения символов из строки, чем использование функции VBA InStr с параметром Left, Right или Center.

Ниже приводится разбивка этой статьи:

• Раздел 1: Как извлекать из строк фиксированного размера.
• Раздел 2: Как извлекать из строк переменного размера.
• Раздел 3: Как извлекать из строк переменного размера с помощью функции Split.
• Разделы 4-6: Некоторые конкретные примеры.

Удаление текста в 1-2 клика с !SEMTools

Каждый из этих методов хорош по-своему, но всем им не хватает одного важного фактора: скорости внедрения.

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

Дополнение !SEMTools имеет в меню быстрые процедуры для экономии времени.

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

Данные можно либо изменить мгновенно, либо автоматически вывести в соседний столбец с помощью глобального флажка.

Ищете способ работать с текстовыми данными в Excel так же быстро и в одном месте с более чем 500 похожими процедурами?

Нажмите на кнопку ниже, чтобы загрузить и активировать бесплатную пробную версию дополнения !SEMTools. Процесс займет всего несколько минут.