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

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

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

В нашем случае формула принимает следующий вид:

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

Обратите внимание, что ПОДСТАВИТЬ всегда возвращает текстовую строку, даже если результат содержит только числа, как в ячейках B2 и B3 (обратите внимание на выравнивание по левому краю, типичное для текстовых значений).

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

Или вы можете выполнить некоторую математическую операцию, которая не изменит исходное значение, например добавить 0 или умножить на 1:

Удалить сразу несколько символов

Чтобы удалить несколько символов с помощью одной формулы, просто вставьте функции ПОДСТАВКИ одну в другую.

• Функция ЗАМЕНИТЬ чувствительна к регистру, имейте это в виду при работе с буквами.
• Если вы хотите, чтобы результаты были значениями, независимыми от исходных строк, используйте параметр Специальная вставка — значения, чтобы заменить формулы их значениями.
• В ситуации, когда нужно удалить много разных символов, намного удобнее использовать пользовательскую функцию RemoveChars, определенную в LAMBDA.

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

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

Удалить текст из нескольких ячеек

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

При нажатии кнопки Заменить все все замены будут выполнены за один раз:

Удалить определенный текст из ячейки с помощью формулы

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

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

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

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

В ситуации, когда вы хотите удалить определенное вхождение определенного символа, определите последний необязательный аргумент функции ПОДСТАВИТЬ.В общей формуле ниже номер_экземпляра определяет, какой экземпляр указанного символа следует заменить пустой строкой:

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

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

=ПОДСТАВИТЬ(A2, "/", "", 2)

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

Чтобы удалить первый символ с левой стороны строки, вы можете использовать одну из следующих формул. Оба делают одно и то же, но по-разному.

В переводе на человеческий язык формула гласит: в указанной ячейке взять 1 символ (num_chars) с 1-й позиции (start_num) и заменить его пустой строкой ("" ).

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

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

=ВПРАВО(A2, ДЛСТР(A2) - 1)

На снимке экрана ниже показана формула REPLACE. Формула RIGHT LEN даст точно такие же результаты.

Чтобы удалить любые n символов в начале строки, см. статью Удаление символов слева в Excel.

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

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

Логика аналогична формуле RIGHT LEN из предыдущего примера:

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

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

=ЛЕВО(A2, ДЛСТР(A2) - 1)

Чтобы удалить любые n символов с конца строки, см. статью Удаление символов справа в Excel.

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

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

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

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

=LEFT(A2, ПОИСК(":", A2) -1)

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

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

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

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

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

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

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

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

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

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

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

Удаление символов в Excel с помощью Flash Fill

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

Допустим, у вас есть имя и адрес электронной почты в одной ячейке, разделенные запятой. Вы хотите удалить все после запятой (включая саму запятую). Чтобы это сделать, выполните следующие действия:

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

Готово!

Примечание. Если Excel не может распознать шаблон в ваших данных, заполните еще пару ячеек вручную, чтобы предоставить больше примеров. Кроме того, убедитесь, что Flash Fill включен в вашем Excel. Если это все еще не работает, вам придется прибегнуть к какому-то другому методу.

Специальные инструменты для удаления символов или текста в Excel

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

На вкладке Данные Ablebits в группе Текст есть три варианта удаления символов из ячеек Excel:

• Определенные символы и подстроки
• Символы в определенной позиции
• Повторяющиеся символы

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

1. Нажмите Удалить >Удалить символы.
2. Выберите наиболее подходящий вариант.
3. Установите или снимите флажок С учетом регистра.
4. Нажмите Удалить.

Ниже приведены несколько примеров, которые охватывают наиболее типичные сценарии.

Удалить определенный символ

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

Например, мы удаляем все вхождения заглавных букв A и B из диапазона A2:A4:

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

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

• Непечатаемые символы: удаляется любой из первых 32 символов в 7-битном наборе ASCII (кодовые значения от 0 до 31), включая символ табуляции, разрыв строки и т. д.< /li>
• Текстовые символы – удаляет текст и сохраняет числа.
• Цифровые символы – удаляет числа из буквенно-цифровых строк.
• Символы и знаки препинания – удаляет специальные символы и знаки препинания, такие как точка, вопросительный знак, восклицательный знак, запятая и т. д.
  

Удалить часть текста

Чтобы удалить часть строки, выберите параметр Удалить подстроку.

Например, чтобы извлечь имена пользователей из адресов Gmail, мы удаляем подстроку "@gmail.com":

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

Как удалить ненужный текст из ячейки в Excel?

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

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

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

<р>2. В первом мастере преобразования текста в столбцы выберите параметр «С разделителями» и нажмите кнопку «Далее».

<р>3. Во втором мастере установите флажок «Разделители» (здесь я устанавливаю флажок «Пробел», поскольку тексты разделены пробелами в ячейках), а затем нажмите кнопку «Далее».

Примечание. Если тексты разделены другими разделителями, установите флажок "Другое", а затем введите разделитель в следующее поле.

<р>4. В третьем мастере выберите ячейку назначения, чтобы поместить разделенное содержимое, а затем нажмите кнопку "Готово".

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

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

Перед применением Kutools for Excel сначала загрузите и установите его.

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

<р>2. Во всплывающем диалоговом окне «Извлечь адрес электронной почты» выберите пустую ячейку для определения адреса электронной почты и нажмите кнопку «ОК».

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

Примечание:

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

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

Формула Excel для удаления первого или последнего слова из ячейки.

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

Разделы:

Удалить первое слово из ячейки

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

Символы без пробелов

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

Пояснение

Функция ПРАВИЛЬНО получает текст справа от ячейки.Это необходимо, потому что мы извлекаем весь текст, кроме первого слова; это означает, что нам нужно получить все, что находится справа от первого слова в ячейке.

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

Чтобы получить все, кроме первого слова, нам нужно найти пробел, который отделяет первое слово от остального текста. Используйте функцию НАЙТИ, ища пробел " ", чтобы получить количество символов слева от ячейки до первого пробела. В приведенном выше примере это возвращает 5. Это количество символов, которые мы хотим удалить из ячейки.

Чтобы получить число, которое мы хотим сохранить, нам нужно использовать функцию ДЛСТР для подсчета всех символов в ячейке. Результат функции ДЛСТР равен 12, поскольку в ячейке 12 символов.

В приведенной выше формуле мы вычитаем результат НАЙТИ из результата ДЛСТР, который равен 12-5 или 7.

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

Удалить последнее слово из ячейки

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

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

Символы без пробелов

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

Пояснение

В этой формуле используются четыре функции: НАЛЕВО(), ДЛСТР(), НАЙТИ(), ПОДСТАВИТЬ().

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

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

Для этого нам нужно сообщить функции ВЛЕВО, сколько символов слева от ячейки мы хотим в новой ячейке, и это число может варьироваться. Сложная часть формулы — это то, что дает нам это число.

Эта часть формулы ПОДСТАВИТЬ(A1," ","") удаляет все пробелы в ячейке, а затем подсчитывает количество оставшихся символов с помощью функции ДЛСТР ДЛСТР(ПОДСТАВИТЬ(A1," ","")). Из приведенного выше примера результат равен 10.

Затем подсчитывается количество символов в ячейке, когда в ней остаются пробелы: LEN(A1). Результат равен 12.

Затем формула вычитает два вышеуказанных числа, чтобы получить 2 в этом примере. Это число представляет собой общее количество пробелов в ячейке, и оно сообщает другой функции ПОДСТАВИТЬ, какой пробел следует заменить; мы хотим заменить последний пробел, чтобы его было легче найти.

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

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

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

Если вы все еще немного запутались, ознакомьтесь с нашим руководством по функции ПОДСТАВИТЬ в Excel

Более универсальные формулы — предотвращение ошибок

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

Чтобы избежать ошибок, мы можем использовать простую функцию ЕСЛИОШИБКА().

Формула 1:

Формула 2:

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

Прочитайте наш учебник, чтобы узнать больше о функции ЕСЛИОШИБКА в Excel и подавлении ошибок в целом.

Примечания

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

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

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

Извлечение последнего слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта UDF (определяемая пользователем функция) извлекает последнее слово или символы из ячейки в Excel.

Получить первое слово из ячейки в Excel
Учебник: Как использовать формулу для получения первого слова из ячейки в Excel. Это работает для одного c.

Извлечение первого слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта бесплатная функция Excel UDF (определяемая пользователем функция) возвращает первое слово из ячейки в Exce.

Удалить все проверки данных из ячейки в Excel
Макрос: удалите все проверки данных из ячейки в Excel с помощью этого бесплатного макроса Excel. Это здорово.

Удалить все пустые строки или пустые ячейки из диапазона в Excel
Учебник: Как быстро удалить все пустые ячейки или строки из диапазона в Excel. Это позволяет вам .

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена ​​вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Чтобы найти и удалить определенный текст в Excel, мы можем использовать функцию ПОДСТАВИТЬ.

Допустим, в тексте есть знаки дефиса («-»), и мы хотим их удалить. Мы можем сделать это с помощью функции ПОДСТАВИТЬ, используя следующую формулу:

В этом примере мы предоставили ссылку на ячейку (B3), содержимое, которое мы хотим удалить из текста ("-"), а для замещающего текста - пустую строку ("").

Функция ПОДСТАВИТЬ заменяет все дефисы "-" ничем.

Мы можем удалить любой другой символ или текст из основной текстовой строки, заменив дефис sing ("-") в формуле этим символом или текстом.

Удалить более одного персонажа

Мы также можем удалить более одного символа из текста, вложив функции ПОДСТАВКИ.

Допустим, у нас есть скобки и дефисы в тексте, и мы хотим удалить их оба. Мы вложим функции ПОДСТАВКИ три раза, чтобы получить желаемый результат, например:

Сначала Excel удалит из текста символ «(», затем перейдет к внешней вложенной функции ПОДСТАВИТЬ и удалит «)», а затем перейдет к следующей внешней функции ПОДСТАВИТЬ и удалит знак дефиса («- ").

Удалить текст в Google Таблицах

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

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

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

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

  
• Как обрезать фото в фотошопе
  
• Прошивка Zte zxhn h298a для любого провайдера
  
• Как установить драйвер odbc для oracle
  
• Как настроить Microsoft Edge
  
• Что такое процентиль в Excel