Как удалить последние символы в ячейке excel? - про деньги, инвестиции и расходы

Как удалить пробелы до и после текста в Excel

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

Формула удаления лишних пробелов из ячеек проста:

Где A2 – исходная текстовая строка.

Как вы можете видеть на изображении ниже, он удаляет все пробелы перед текстом, после текста и между словами/подстроками, кроме одного символа пробела.

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

Чтобы избавиться от них, преобразуйте неразрывные пробелы в обычные с помощью ЗАМЕНЫ:

Где 160 — это кодовый номер неразрывного пробела ( ).

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

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

Дополнительную информацию см. в разделе Как удалить пробелы в Excel.

Выборочное удаление символов из ячеек Excel

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

2) цифр и любых символов (кроме букв);

3) любых букв, как русского, так и английского алфавита;

4) любых букв и знаков;

5) любых букв и цифр;

6) только знаков (всех, кроме букв и цифр);

7) только букв кириллицы;

букв кириллицы и знаков;

9) только букв латиницы;

10) букв латинского алфавита и любых знаков;

11) символов, заданных вручную либо по ASCII-кодам.

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

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

Удалите 1-ые несколько знаков из столбца текстов
Удалите крайние несколько знаков из столбца текстов
Удалите как 1-ые несколько, так и крайние несколько знаков из столбца текстов.

Удалите 1-ые несколько знаков из столбца текстов

Синтаксис для удаления первых нескольких знаков из столбца текстов:

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

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

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

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

Синтаксис для удаления крайних нескольких знаков из столбца текстов:

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

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

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

Удалите как 1-ые несколько, так и крайние несколько знаков из столбца текстов

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

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

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

Я надеюсь, что этот пост поможет для вас удалить 1-ые либо крайние несколько знаков либо определенные знаки позиции из текста в Microsoft Excel.

Как удалить пробелы в Excel

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

Как извлечь часть текста в Эксель

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

Для получения части текста, в Эксель есть 3 функции:

ЛЕВСИМВ(Строка; Количество_символов) – выводит заданное количество символов с левого края. Например, =ЛЕВСИМВ(А1;10) выведет 10 первых символов строки в ячейке А1 . Функция имеет 2 обязательных аргумента – Строка-источник и количество выводимых символов;

Функция ЛЕВСИМВ в Эксель

ПРАВСИМВ(Строка; Количество_символов) – функция схожа с предыдущей, она выводит заданное количество символов справа. То есть, =ПРАВСИМВ(А1;10) в результате выдаст 10 последних символов из строки А1 .

Функция ПРАВСИМВ в Excel

ПСТР(Строка; Начальный_символ; Количество символов) – выбирает из текста нужное количество знаков, начиная с заданного. Например, =ПСТР(А1;5;3) выведет 3 символа начиная с 5-го (5-7 символы строки).

Функция ПСТР в Эксель

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

Функции ЛЕВИСМВ, ПРАВСИМВ, ПСТР – это простой и мощный инструмент, если используется в комбинации с другими текстовыми функциями. Вы увидите это в уроке-практикуме по строчным функциям.

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

Если вы еще не прочли посты о написании формул и применении функций – обязательно это сделайте, без них изучение функций Excel будет сложнее и дольше!

Замена слова:

Замена выполняется через специальную опцию «Заменить» . В Word и в Excel эта опция находится на главной панели .

Выглядят немного по-разному, но выполняют одну и ту же функцию.

Функцию замены можно вызвать так же сочетанием клавиш ctrl+F (поиск). После появления поля поиска выбрать «Заменить».

В появившемся окне следует внести слово (или словосочетание), которое вы ищите и слово, на которое следует его поменять.

Следует обратить внимание на то, что по умолчанию поиск находит все слова содержащие сочетание указанных символов. Например, если в тексте есть слова «символ», «символика», «символично» и т.д

, замена сочетания «символ» будет выполнена во всех словах

Например, если в тексте есть слова «символ», «символика», «символично» и т.д. , замена сочетания «символ» будет выполнена во всех словах.

Чтобы этого избежать следует открыть дополнительные параметры и указать дополнительные критерии поиска.

Так же можно после искомого слова поставить пробел.

Используйте простой макрос VBA в непосредственном окне

Непосредственное окно — это место в редакторе Excel VB Editor, которое позволяет быстро запустить код макроса (без хлопот, связанных с вставкой модуля или сохранением макроса).

Все, что вам нужно сделать, это использовать код, скопировать и вставить его в непосредственном окне и нажать клавишу ввода.

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

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

Для каждой ячейки в выделении: cell.Value = Right (cell.Value, Len (cell.Value) - 1): следующая ячейка

Вот шаги, чтобы использовать этот код из непосредственного окна:

Выберите ячейки / диапазон, из которых вы хотите удалить первый символ из текстовой строки
Щелкните правой кнопкой мыши имя вкладки листа (тот же лист, где у вас есть эти данные)
Щелкните Просмотреть код. Это откроет бэкэнд VB Editor
В окне редактора VB выберите в меню пункт «Просмотр» и затем нажмите «Немедленное окно». Это приведет к немедленному отображению окна. Этот шаг не нужен, если непосредственное окно уже видно.
Скопируйте и вставьте указанную выше строку кода в ближайшее окно.
Поместите курсор в конец строки
Нажмите клавишу Enter

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

Краткое объяснение строки кода VBA

Для каждой выделенной ячейки: cell.Value = Right (cell.Value, Len (cell.Value) - 1): следующая ячейка

В приведенной выше строке кода используется цикл For Next, который проходит по каждой выделенной ячейке. Затем он использует функции RIGHT и LEN для извлечения всех символов, кроме первого.

Это пять простых методов, которые вы можете использовать, чтобы избавиться от первого символа текстовой строки и извлечь остальные. Вы можете выбрать, какой метод использовать, в зависимости от вашего набора данных и ваших требований.

Например, если вы не хотите использовать дополнительный столбец и хотите получать результаты в тех же ячейках, вам лучше использовать метод Text to Columns или метод VBA Immediate Window.

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

Использование Регулярного Выражения

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

Например, мы можем использовать метод replaceAll() самого класса String , который принимает два параметра: регулярное выражение и замену String

TEST_STRING.replaceAll(".$", "");

Обратите внимание, что, поскольку мы вызываем метод на String – операция, конечно, небезопасна для null|/. Кроме того, replace All() и выражение регулярного выражения может быть сложным на первый взгляд. Вы можете прочитать больше о регулярном выражении здесь , но чтобы сделать логику немного более удобной для пользователя, мы можем обернуть ее в вспомогательный класс:

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

Кроме того, replace All() и выражение регулярного выражения может быть сложным на первый взгляд. Вы можете прочитать больше о регулярном выражении здесь , но чтобы сделать логику немного более удобной для пользователя, мы можем обернуть ее в вспомогательный класс:

public static String removeLastCharRegex(String s) {
    return (s == null) ? null : s.replaceAll(".$", "");
}

Обратите внимание, что если строка | заканчивается новой строкой, то описанный выше метод завершится ошибкой, так как “. ” в регулярном выражении соответствует любому символу, за исключением терминаторов строк. Наконец, давайте перепишем реализацию с Java 8 :

Наконец, давайте перепишем реализацию с Java 8 :

public static String removeLastCharRegexOptional(String s) {
    return Optional.ofNullable(s)
      .map(str -> str.replaceAll(".$", ""))
      .orElse(s);
}

Удаление ячеек при помощи клавиш на ленте

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

Для начала отмечаем хоть каким комфортным методом ячейку, которую необходимо удалить. Переключаемся в главную вкладку и щелкаем по кнопочке “Удалить”, расположенной в группе “Ячейки”.

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

Отмечаем нужный спектр частей, перебегаем во вкладку “Основная” и жмем клавишу “Удалить”.
Итог этого метода ничем не различается от вышеперечисленного. Опосля выполнения операции, выделенная группа частей пропадет, а ячейки снизу от выделенного спектра сдвинутся ввысь.

В случае удаления спектра частей по вертикали, примыкающие ячейки сместятся в другом направлении. Давайте разглядим это на примере.

Отмечаем вертикальный спектр ячеек, и жмем по уже отлично знакомой кнопочке “Удалить” на ленте.
Опосля окончания процедуры, отмеченные ячейки удалились со сдвигом оставшихся справа в левую сторону.

Сейчас, когда мы разглядели базисные операции, можно перейти к наиболее гибкому варианту удаления ячеек. Этот метод предполагает работу с таблицами и спектрами данных как по горизонтали, так и по вертикали.

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

Примечание: Внедрение клавиши “Удалить”, расположенной на ленте наименее функционально в сопоставлении с удалением через контекстное меню, так как оно не дает юзеру возможность настроить сдвиг ячеек.

Но выход есть. Используя набор инструментов на ленте, можно создавать удаление ячеек, за ранее выбрав направление смещения. Давайте разглядим, как это воплотить на примере.

Выделяем область ячеек для удаления. Но сейчас, в разделе “Ячейки” на ленте кликаем не по самой кнопочке «Удалить», а по треугольнику, расположенному справа от нее. В раскрывшемся перечне щелкаем по пт “Удалить ячейки…”.
Мы увидим знакомое окно с вариациями удаления и смещения. Избираем тот, который соответствует нашим целям и нажимаем клавишу OK, чтоб узреть итог. Допустим, пусть это будет сдвиг ввысь.
Операция удаления прошла удачно, и смещение вышло конкретно ввысь.

Удаление слова.

Удаление слова выполняется аналогично его замене. Только в случае удаления в поле «Заменить на» не следует ставить никаких символов. Будет произведена замена всех найденных слов на пустое место, то есть их удаление.

Как показано на скриншоте ниже, вам просто нужно сохранить адреса электронной почты, но удалить остальной нежелательный текст из ячеек. Что вы можете сделать для этого? В этой статье вы подробно узнаете о методах удаления нежелательного текста из ячеек.

Удалите ненужный текст из ячейки с помощью функции Text to Columns

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

1. Выберите диапазон с ячейками, из которых вы хотите удалить ненужный текст, а затем нажмите Данные > Текст в столбцы. Смотрите скриншот:

2. В первом Мастер преобразования текста в столбцы, наведите на разграниченный вариант, а затем щелкните Следующая кнопку.

3. Во втором мастере отметьте Разделители поле (здесь я проверяю Space поле, поскольку тексты разделены пробелами в ячейках), а затем щелкните Следующая кнопку.

Внимание: Если тексты разделены другими разделителями, проверьте Другое поле, а затем введите разделитель в следующее поле. 4

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

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

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

Удалите ненужный текст из ячейки с помощью Kutools for Excel

Для получения адресов электронной почты только из ячеек вы можете использовать Извлечь адрес электронной почты полезности Kutools for Excel. С помощью этой утилиты вы можете легко извлечь из ячейки только адрес электронной почты.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выделите ячейки, в которых вы хотите сохранить только адреса электронной почты, и нажмите Kutools > Текст > Извлечь адрес электронной почты. Смотрите скриншот:

2. Во всплывающем Извлечь адрес электронной почты в диалоговом окне выберите пустую ячейку для поиска адреса электронной почты, а затем щелкните значок OK кнопку.

Теперь адреса электронной почты сразу извлекаются из ячеек.

Внимание:

Освободи Себя Разделить клетки полезности Kutools for Excel может легко помочь вам получить определенный текст, разделив содержимое ячейки на несколько ячеек с определенным разделителем.

Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Как найти программы шпионы на компьютере
Как посчитать знаки в ворд паде
Tablet driver with macrokey manager что это
Как восстановить формулу в excel
Файл в формате iso 2709

Как создать таблицу в Эксель: Удаление содержимого ячеек

Наконец, не забудьте сохранить!

Все эти файлы теперь изменены в редакторе и еще не сохранены на диск.

Используйте Файл -> Сохранить все (или Ctrl + Alt + S )

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

413

Jeff Ward
11 Дек 2019 в 18:47

Чтобы заменить строку в одном файле (открытом в данный момент): CTRL + H

Для замены на уровне рабочей области используйте: CTRL + SHIFT + H

ZF007
10 Июл 2019 в 10:32

Это лучший способ .

Сначала наведите курсор на элемент и нажмите F2.
Затем введите новое имя и нажмите клавишу Enter. Это переименует все вхождения в каждом файле вашего проекта.

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

Чтобы получить больше отличных инструментов, я настоятельно рекомендую: https://johnpapa.net/refactoring-with-visual-studio-code/ а>

DonDaniel
31 Янв 2018 в 16:03

На странице Привязки клавиш кода Visual Studio раздел содержит ссылки на PDF-файлы для каждой основной ОС. После открытия найдите «заменить в файлах» или любой другой ярлык, который может вам понадобиться.

Другой способ — использовать палитру команд ( ctrl / cmd + shift + P ), где вы можете ввести » replace «, чтобы перечислить все связанные команды, включая ту, которую вы хотите:

Magnus Lind Oxlund
15 Май 2019 в 22:34

Существует два технически одинаковых метода

Сначала наведите курсор на слово и нажмите F2. Замените свое слово и нажмите Enter.
Сначала наведите курсор на слово и щелкните его левой кнопкой мыши. Щелкните опцию «Переименовать символ». Замените свое слово и нажмите Enter.

Shuaib Abubakker Bapputty Haji
30 Авг 2020 в 06:32

Обновление на 2020 год

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

Вот типичный результат поиска:

Чтобы преобразовать это в поиск и заменить, вам нужно щелкнуть значок стрелки слева от поля ввода поиска. Это откроет параметры замены, как показано ниже

Обратите внимание, что значок стрелки теперь направлен вниз

Сочетание клавиш также будет работать для доступа к поиску и замене.

Ссылка на документы VSCode по поиску и замене: .

jhovanec
8 Окт 2020 в 14:36

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

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

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

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

Отмечаем 1-ый элемент обычным способом. Дальше, зажав кнопку “Ctrl” по очереди выделяем все оставшиеся нужные ячейки или спектры (вне зависимости от того, на каком расстоянии они находятся друг от друга).
Опосля того, как мы удачно совладали с первым шагом, приступаем к самому удалению, которое можно выполнить ранее рассмотренными методами.

Метод 1: выравниваем область ячеек

Как удалить слово в ячейке Excel?

Как удалить ненужное в Excel?

Функция TRIM (СЖПРОБЕЛЫ) удаляет все лишние пробелы из строки, если они есть. Функция CLEAN (ПЕЧСИМВ) удаляет все непечатные символы из строки. К примеру, ячейка A1 на рисунке ниже содержит строку “Excel Easy” (с двумя пробелами).

Как удалить часть текста в ячейке Excel формула?

Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.

Как удалить определенные значения в Excel?

Для этого выбираем на ленте Excel вкладку «Главная», переходим в группу «Редактирование», нажимаем кнопку «Найти и выделить» и выбираем команду «Выделение группы ячеек». Появляется одноименное диалоговое окно, при помощи которого можно выделить определенные ячейки таблицы и после этого удалить их значения.

Что нужно сделать чтобы удалить лишние символы из текста?

Как удалить лишние символы? Для удаления лишних символов, необходимо выбрать пункт «Только знаки», при этом все символы кроме цифр и букв удаляются, но имеется возможность задать символы-исключения, например, чтобы все слова не слились в одну строку, можно установить флажок в поле «а также символы:» и вписать пробел.

Как удалить все ненужные строки в Excel?

Выделите все отфильтрованные строки: нажмите Ctrl+Home, затем стрелку вниз, чтобы перейти к первой строке с данными, а затем нажмите Ctrl+Shift+End. Кликните правой кнопкой мыши по любой выделенной ячейке и из контекстного меню выберите Delete row (Удалить строку) или просто нажмите Ctrl+-(знак минус).

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

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

Удалите первые N символов с помощью формулы / UDF.
Удалите последние N символов с помощью формулы / UDF.
Пакетное удаление N символов слева, справа или посередине без формул.

Как в Excel убрать текст и оставить только значения?

В нужном файле Excel выделить столбец, в котором нужно удалить числа.
Открыть вкладку Ёxcel → «Ячейки»→ «Редактировать» → «Оставить в ячейке только текст».

Как удалить часть символов из ячейки Excel?

Удаление непечатаемых символов

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN).

Как в Excel убрать первую цифру в столбце?

Выделите диапазон, на панели ! SEMTools в меню УДАЛИТЬ -> Символы -> обрезать выберите «с начала ячейки». В появившемся диалоговом окошке вбейте количество символов, которое хотите удалить, и нажмите ОК.

Как в Экселе удалить часть таблицы?

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

Выделите строки или столбцы таблицы, которые требуется удалить. …
На вкладке Главная в группе Ячейки, щелкните стрелку рядом с кнопкой Удалить, а затем выберите команду Удалить строки таблицы или Удалить столбцы таблицы.

Как в Excel удалить диапазон строк?

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

Как удалить много ячеек в Excel?

Очистка ячеек содержимого или форматов

Выберем ячейки, строки или столбцы, которые нужно очистить. Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
На вкладке «Главная» в группе «Редактирование» щелкните стрелку рядом с кнопкой «Очистить» и сделайте одно из следующего:

Удалить текст после символа с помощью поиска и замены

Если вы хотите быстро удалить весь текст после определенной текстовой строки (или перед текстовой строкой), вы можете сделать это с помощью функции «Найти и заменить» и подстановочных знаков.

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

Ниже приведены шаги для этого:

Вышеуказанные шаги позволят найти запятую в наборе данных и удалить весь текст после запятой (включая запятую).

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

Как это работает?

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

Когда я использую его после запятой (в поле «Найти»), а затем нажимаю кнопку «Заменить все», он находит первую запятую в ячейке и считает ее совпадением.

Это связано с тем, что знак звездочки (*) соответствует всей текстовой строке, следующей за запятой.

Поэтому, когда вы нажимаете кнопку «Заменить все», запятая и весь последующий текст заменяются.

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

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

Защита листа и ячеек в Excel

Функция VBA RegExp для удаления символов и текста в Excel

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

Функция имеет следующий синтаксис:

RegExpReplace (текст; шаблон; замена; ; )

Первые три аргумента являются обязательными, два последних — необязательными.

Где:

Текст – текстовая строка для поиска.
Шаблон – регулярное выражение для поиска.
Замена – текст, на который нужно заменить. Чтобы удалить подстроки, соответствующие шаблону, используйте для замены пустую строку («»).
Instance_num (необязательно) – экземпляр, который нужно заменить. Если не указан, заменяются все найденные совпадения (по умолчанию).
Match_case (необязательно) – логическое значение, указывающее, следует ли учитывать регистр текста или игнорировать его. Для сопоставления с учетом регистра используйте ИСТИНА (по умолчанию); для нечувствительности к регистру – ЛОЖЬ.

Для получения дополнительной информации см. .

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

RegExpReplace(текст, шаблон; «»; ; )

В приведенных ниже примерах показаны различные способы применения этой базовой концепции.

Рассмотрим их применение.

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

Зная длину лишнего текста — 6 знаков в индексе, вычитаем их из общего числа символов посчитанных функцией ДЛСТР и получаем то количество символов, которое должно остаться в тексте, когда удалим индекс.

Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.

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

ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).

Где …*¹- адрес ячейки, из которой берем текст.

Данная команда позволяет массово удалять из текст множества ячеек весь текст кроме чисел:

Чтобы воспользоваться данной командой выделите ячейки с текстом из которых вы хотите удалить текст и оставить числа, перейдите во вкладку «ЁXCEL» Главного меню, нажмите кнопку «Ячейки» и выберите команду «Оставить в ячейке только числа (удалить весь текст)»:

В выделенных ячейках останутся только числа.

Способы удаления текста и сохранения числовой информации

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

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

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

=ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)))+1)

Разберем основные моменты:

Специальную формулу необходимо вбивать в поле при помощи комбинации кнопок «Ctrl+Shift+Enter».
Стоит заметить, что в таком виде массивная формула может использоваться только с текстовой информацией, в которой число знаков не больше 99. Для увеличения диапазона нужно, к примеру, заменить параметр «СТРОКА($1:$99)» на «СТРОКА($1:$200)». Иными словами, мы вместо показателя 99 вводим число знаков с запасом. Если ввести слишком большой диапазон, то обработка формулы может занять длительное время.
Если в текстовых данных числовые значения разбросаны по всему тексту, то формула не сможет правильно обработать информацию.

Детально рассмотрим специальную массивную формулу на таком примере: «Было доставлено кусков мыла 763шт., а заказывали 780»

В поле А1 располагается сама текстовая информация, из которой мы будем извлекать числовые данные.
Фрагмент: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99))) позволяет определить позицию 1-го значения в поле. Получаем значение 29.
Фрагмент: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)) позволяет определить позицию последнего значения в поле. Получаем значение 31.
Мы получаем такую формулу: =ПСТР(А1;29;31-29+1). Оператор ПСТР позволяет извлечь из текстовой информации, указанной 1-м аргументом, начиная с заданной позиции (29) с числом знаков, заданным 3-м аргументом.
В результате мы получаем:=ПСТР(А1;29;31-29+1)
- =ПСТР(А1;29;2+1)
- =ПСТР(А1;29;3)
- 763

Бывают ситуации обратные, когда нужно реализовать операцию извлечения односоставных текстовых данных, исключив числа.

Нам необходимо сохранить только текстовую информацию.

Специальная формула применяется по аналогичному алгоритму, что и вышерассмотренная. Она выглядит так: =ПСТР(А1;ПОИСК(«-«;А1)+1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР(ПСТР(А1;ПОИСК(«-«;А1)+1;999);СТРОКА($1:$99);1));0)-1)

Второй метод: использование специального макроса

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

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

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

Используя специальную комбинацию клавиш «Alt+F11», производим открытие редактора VBA. Альтернативный вариант – нажать ПКМ по рабочему листу и выбрать элемент «Исходный текст».
Реализуем создание нового модуля. Для осуществления этой процедуры жмем левой клавишей мышки на элемент, имеющий наименование «Insert», а затем выбираем объект «Module».
Производим копирование кода, который располагается выше, и вставляем его в созданный модуль. Копирование реализуем при помощи сочетания клавиш «Ctrl+C», а вставку – «Ctrl+V».
Теперь в необходимой ячейке, в которой мы планируем вывести только числовую информацию, вбиваем такую формулу: =GetNumbers(А1).
Нам нужно растянуть формулу вниз на все ячейки колонки. Для этого наводим указатель на нижний правый уголок ячейки. Курсор принял форму небольшого плюсика темного цвета. Зажимаем левую клавишу мышки и протягиваем формулу вниз до окончания таблички.

Готово! Мы реализовали извлечение числовой информации при помощи специального макроса.