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

Обновлено: 21.11.2024

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

Формулы для Google Таблиц для удаления текста из ячеек

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

Google Таблицы: удалите пробелы

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

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

Удалить другие специальные символы из текстовых строк в Google Sheets

Увы, в Google Таблицах нет инструмента для "обрезки" других символов, кроме пробелов. Здесь вам придется иметь дело с формулами.

Совет. Или используйте вместо этого наш инструмент — Power Tools освободит ваш диапазон от любых символов, которые вы укажете, одним щелчком мыши, включая пробелы.

Здесь я обращался с хэштегами перед номерами квартир и номерами телефонов с тире и скобками между ними:

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

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

Давайте посмотрим, какой аргумент требуется функции:

  • text_to_search — это либо текст для обработки, либо ячейка, содержащая этот текст. Обязательно.
  • search_for — это тот символ, который вы хотите найти и удалить. Обязательно.
  • replace_with — символ, который вы вставите вместо нежелательного символа. Обязательно.
  • occurrence_number — если есть несколько экземпляров искомого символа, здесь вы можете указать, какой из них заменить. Это совершенно необязательно, и если вы опустите этот аргумент, все экземпляры будут заменены чем-то новым (replace_for).

Совет. Хэштег также заключен в двойные кавычки, поскольку именно так следует упоминать текстовые строки в формулах Google Sheets.

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

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

Эта формула удаляет символы один за другим, и каждая ЗАМЕНА, начиная с середины, становится диапазоном для следующей ЗАМЕНЫ:

Удалить определенный текст из ячеек в Google Таблицах

Хотя вы можете использовать вышеупомянутую функцию ПОДСТАВИТЬ для Google Таблиц, чтобы удалить текст из ячеек, я хотел бы также показать другую функцию — REGEXREPLACE.

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

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

Как видите, у функции есть три аргумента:

  • текст — здесь вы ищете текстовую строку для удаления. Это может быть сам текст в двойных кавычках или ссылка на ячейку/диапазон с текстом.
  • regular_expression — шаблон поиска, состоящий из различных комбинаций символов.Вы будете искать все строки, соответствующие этому шаблону. В этом споре и происходит все самое интересное, если можно так выразиться.
  • замена — новая желаемая текстовая строка.

Предположим, что мои ячейки с данными также содержат название страны (США), если оно находится в разных местах в ячейках:

Как REGEXREPLACE поможет мне удалить его?


Вот как именно работает формула:

  • сканирует содержимое ячейки A1
  • для совпадений с этой маской: "(.*)US(.*)"

Эта маска указывает функции искать US независимо от того, сколько других символов может предшествовать (.*) или следовать за (.*)< /em> название страны.

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

Совет. Что касается тех оставшихся запятых, то избавиться от них поможет описанная выше функция ПОДСТАВИТЬ ;) Можно даже ЗАМЕНИТЬ REGEXREPLACE с ПОДСТАВКОЙ и решить все одной формулой:

Удалить текст до/после определенных символов во всех выделенных ячейках

Пример 1. Функция REGEXREPLACE для Google Таблиц

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

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

Так как же удалить адреса и оставить в ячейках только номера телефонов?

Вот формула, которую я буду использовать:

    Вот регулярное выражение, которое я использую в этом случае: ".*\n.*(\+.*)"

В первой части — .*\n.* — я использую обратную косую черту+n, чтобы указать, что в моей ячейке больше одной строки. Поэтому я хочу, чтобы функция удаляла все до и после разрыва строки (включая его).

Вторая часть в квадратных скобках (\+.*) говорит о том, что я хочу сохранить знак "плюс" и все, что следует за ним, без изменений. Я взял эту часть в скобки, чтобы сгруппировать ее и запомнить на потом.

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

Подобным образом вы можете удалить все телефонные номера, но сохранить адреса:

Только на этот раз вы указываете функции сгруппировать (и вернуть) все, что находится до разрыва строки, и удалить все остальное:

Пример 2. ПРАВО+ДЛСТР+НАЙТИ

Есть еще несколько функций Google Sheets, которые позволяют удалять текст перед определенным символом. Они ПРАВЫ, ДЛИН и НАЙТИ.

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

Использование этого трио в определенном порядке поможет мне получить тот же результат и удалить весь текст перед символом — знаком плюс:


Позвольте мне объяснить, как работает эта формула:

  • НАЙТИ("+",A1)-1 находит номер позиции знака плюс в ячейке A1 (24) и вычитает 1, чтобы в итоговую сумму не включалось сам плюс: 23.
  • LEN(A1)-(FIND("+",A1)-1) проверяет общее количество символов в A1 (40) и вычитает 23 (подсчитано по НАЙТИ) из него: 17.
  • А затем RIGHT возвращает 17 символов с конца (справа) A1.

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

Ну, все в порядке. Инструмент в конце все равно делает эту работу лучше ;)

Удалить первые/последние N символов из строк в Google Sheets

Всякий раз, когда вам нужно удалить определенное количество разных символов из начала или конца ячейки, REGEXREPLACE и RIGHT/LEFT+LEN также помогут.

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

Итак, как я могу стереть коды с этих телефонных номеров? Или, другими словами, удалить первые 9 символов из ячеек:

    Используйте REGEXREPLACE. Создайте регулярное выражение, которое найдет и удалит все до 9-го символа (включая этот 9-й символ):

.

Примечание. Поскольку ЗАМЕНЕ требуется начальная позиция для обработки текста, она не подойдет, если вам нужно удалить N символов с конца ячейки.

Бесформулированный способ удаления определенного текста в Google Таблицах — надстройка Power Tools

Функции и все в порядке, когда у вас есть время убить. Но знаете ли вы, что есть специальный инструмент, который объединяет все вышеперечисленные способы, и все, что вам нужно сделать, это выбрать нужный переключатель? :) Никаких формул, никаких лишних столбцов — лучшего помощника и пожелать нельзя ;D

Вам не нужно верить мне на слово, просто установите Power Tools и убедитесь в этом сами:

Другой инструмент Power Tools удаляет единицы времени и даты из меток времени. Это называется "Разделить дату и время":

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

Инструмент извлечет единицу даты и заменит ею всю метку времени. Или, другими словами, это дополнение для Google Sheets удалит единицу времени из метки времени:

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

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

Вас также может заинтересовать

33 комментария к статье "Google Таблицы: удалите один и тот же текст или определенные символы сразу из нескольких ячеек"

Это была очень хорошая статья. Его объяснения были четкими и краткими. Спасибо.

У меня есть лист с телефонными номерами, перед каждым из которых стоит цифра 1. Как я могу удалить его из каждого номера, не удаляя все 1 в списке? Я пробовал левый, правый и лен, но не получил результатов.
17137996969
17139565876
12102265350
12107042187

Пожалуйста, ознакомьтесь с этой частью статьи: Как удалить первые N символов

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

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

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

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

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

  1. Выберите диапазон ячеек, из которых вы хотите удалить определенный символ.
  2. Нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить.
  3. В поле Найти введите символ.
  4. Оставьте поле Заменитьна пустым.
  5. Нажмите Заменить все.

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

  • Этот метод удаляет символы непосредственно из ваших исходных данных. Если результат отличается от ожидаемого, нажмите Ctrl + Z, чтобы отменить изменение и вернуть исходные данные.
  • Если вы имеете дело с буквенными символами, где регистр букв имеет значение, нажмите Параметры, чтобы развернуть диалоговое окно Найти и заменить, а затем установите флажок Учитывать регистр< /em> для выполнения поиска с учетом регистра.

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

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

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

Введите приведенную выше формулу в ячейку 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?

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

  • Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Массовое удаление определенного слова с помощью функции "Найти и заменить"

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

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

<р>1. Выберите диапазон с конкретным словом, которое нужно удалить, нажмите клавиши Ctrl + F, чтобы открыть диалоговое окно «Найти и заменить».

<р>2. В открывшемся диалоговом окне «Найти и заменить» щелкните вкладку «Заменить», введите конкретное слово, которое вы хотите удалить, в поле «Найти», оставьте поле «Заменить на» пустым, а затем нажмите кнопку «Заменить все». Смотрите скриншот:

<р>3. Затем появляется диалоговое окно Microsoft Excel, в котором сообщается, сколько замен было сделано. Нажмите кнопку "ОК" и закройте диалоговое окно "Найти и заменить".

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

Формула 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 не требуется.)

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