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

Обновлено: 21.11.2024

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

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

  1. Удалить формулу с помощью специальной вставки
  2. Удалить формулу с помощью функции Mouse Trick
  3. Удалить формулу с помощью функции "Вставить значения".
  4. Удалить формулу с помощью вкладки "Главная".
  5. Удалить формулу с помощью сочетаний клавиш.
  6. Найти и удалить формулу с помощью быстрых клавиш
  7. Удалить формулу с нескольких листов (одновременно)

1) Использование специального метода вставки

Итак, вот как можно использовать специальный метод вставки в MS Excel для удаления формул:

  1. Откройте лист Excel, из которого вы хотите удалить формулу.
  2. Выделите и скопируйте все ячейки с формулами, которые вы хотите удалить.

  1. Нажмите правой кнопкой мыши и выберите параметр "Специальная вставка".

  1. Затем в поле "Специальная вставка" выберите параметр "Значение" и нажмите "ОК".

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

2) Удалить формулу с помощью трюка с мышью

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

  1. Откройте лист Excel, из которого вы хотите удалить формулу.
  2. Выберите ячейки, в которых есть формулы, которые вы хотите удалить.

  1. Переместите курсор к правому краю столбца.

  1. Удерживая правую клавишу мыши, перетащите курсор немного вправо и верните его в ячейку.
  2. Теперь щелкните правой кнопкой мыши и выберите "Копировать сюда только как значение".

  1. Эта опция "Копировать сюда только как значение" сохраняет значение и удаляет формулу.

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

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

  1. Откройте лист Excel, из которого вы хотите удалить формулу.
  2. Выделите все ячейки с формулами, которые вы хотите удалить.

  1. Нажмите на него правой кнопкой мыши и выберите "Копировать", чтобы скопировать все выбранные ячейки.

  1. После этого снова щелкните правой кнопкой мыши, перейдите к параметрам вставки и выберите "Значения".

  1. Теперь вы заметите, что остались только значения, а формулы исчезли!

4) Удалить формулу с помощью вкладки «Главная»

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

  1. Откройте лист Excel, из которого вы хотите удалить формулу.
  2. Теперь выделите все ячейки с формулами, которые вы хотите удалить.

  1. Щелкните правой кнопкой мыши выбранные ячейки и выберите "Копировать".

  1. Теперь перейдите на вкладку "Главная" и нажмите стрелку вниз под параметром вставки.

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

5) Удалить формулу с помощью сочетания клавиш

Если вам удобно использовать сочетание клавиш "Вставить как значение", выполните следующие действия:

  1. Откройте лист Excel, из которого вы хотите удалить формулу.
  2. Выделите все ячейки с формулами, которые вы хотите удалить.

  1. Затем нажмите Ctrl+C, чтобы скопировать все выделенные ячейки, содержащие формулу.

  1. Теперь нажмите ALT + E + S + V + Enter, чтобы выбрать параметр "Вставить как значения".

6) Найти и удалить формулу с помощью сочетаний клавиш

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

  1. Откройте лист Excel, из которого вы хотите удалить формулу.
  2. Нажмите Ctrl + G на клавиатуре, чтобы открыть диалоговое окно, и выберите "Специальный" .

  1. Теперь выберите из списка параметр «Формулы» и нажмите «ОК» . Будут выбраны все ячейки, содержащие формулы.

  1. Далее выделите цветом все ячейки, содержащие формулы.

  1. Теперь нажмите Ctrl + C, чтобы скопировать одну ячейку, содержащую формулу.

  1. Нажмите "Сейчас" и нажмите ALT + E + S + V + Enter, чтобы выбрать параметр "Вставить как значения".
  2. Формула заменена значением.
  3. Далее повторите эти шаги для всех остальных выделенных ячеек.

7) Удалить формулу с нескольких листов (одновременно)

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

В этом разделе мы рассмотрим технику, которая научит нас удалять формулы и сохранять данные в Excel для нескольких листов одновременно.

Для этого нам сначала нужно сгруппировать листы, к которым мы хотим применить операции. Вот как мы можем это сделать:

  1. Перейдите на вкладку "Имя" на первом листе.
  2. Удерживая нажатой клавишу SHIFT, одновременно нажмите на вкладку "Имя" последнего листа.

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

  1. Затем перейдите на первый лист и выберите все ячейки, содержащие формулу.

  1. Нажмите Ctrl+C или щелкните правой кнопкой мыши и выберите Копировать .

  1. Теперь щелкните правой кнопкой мыши, перейдите к параметру "Вставить" и выберите "Значения".

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

Примечание. Теперь, после выполнения задачи, отключите функцию группировки. Щелкните правой кнопкой мыши любой лист и выберите РАЗГРУППИРОВАТЬ листы.

Заключение

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

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

Предположим, что у вас есть два списка данных, как показано на скриншоте слева. Теперь вам нужно удалить или исключить имена в столбце A, если имя существует в столбце D. Как этого добиться? А что, если два списка находятся на двух разных листах? В этой статье представлены два метода.

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

Для этого можно применить следующие формулы. Пожалуйста, сделайте следующее.

<р>1. Выберите пустую ячейку, которая находится рядом с первой ячейкой списка, который вы хотите удалить, затем введите формулу = СЧЁТЕСЛИ ($ D $ 2: $ D $ 6, A2) в строку формул, а затем нажмите клавишу Enter. Смотрите скриншот:

Примечание. В формуле $D$2:$D$6 — это список, на основе которого вы будете удалять значения, а A2 — это первая ячейка списка, который вы собираетесь удалить. Измените их по мере необходимости.

<р>2. Продолжайте выбирать ячейку результата, перетащите маркер заполнения вниз, пока он не достигнет последней ячейки списка. Смотрите скриншот:

<р>3. Продолжайте выбирать список результатов, затем нажмите «Данные» > «Сортировка от А до Я».

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

<р>4. Теперь выберите все строки имен с результатом 1, щелкните правой кнопкой мыши выбранный диапазон и нажмите «Удалить», чтобы удалить их.

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

Примечание. Если «список для удаления» находится в диапазоне A2:A6 другого рабочего листа, такого как Лист2, примените эту формулу =ЕСЛИ(ЕОШИБКА(ВПР(A2,Лист2! $A$2:$A$6,1) , FALSE)), "Сохранить", "Удалить"), чтобы получить все результаты "Сохранить" и "Удалить", отсортировать список результатов от A до Z, а затем вручную удалить все строки имен, содержащие результат "Удалить" на текущем листе.

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

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

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

<р>1. Нажмите Kutools > Выбрать > Выбрать одинаковые и разные ячейки. Смотрите скриншот:

<р>2. В диалоговом окне «Выбор одинаковых и разных ячеек» вам необходимо:

  • 2.1 Выберите список, из которого нужно удалить значения, в поле Найти значения в;
  • 2.2. Выберите список, на основе которого будут удаляться значения, в поле "Согласно";
  • 2.3 выберите параметр "Одна ячейка" в разделе "На основе";
  • 2.4 Нажмите кнопку "ОК". Смотрите скриншот:

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

<р>4. Теперь выбираются значения в столбце A, если они существуют в столбце D. Вы можете нажать клавишу Delete, чтобы удалить их вручную.

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

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

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

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

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

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

Выполните следующие действия:

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

Нажмите "Данные" > "Дополнительно" (в группе "Сортировка и фильтр").

Во всплывающем окне "Расширенный фильтр" выполните одно из следующих действий:

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

Нажмите "Фильтровать список на месте".

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

Нажмите «Копировать в другое место».

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

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

Проверьте только уникальные записи, затем нажмите OK.

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

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

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

Выполните следующие действия:

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

На вкладке "Данные" нажмите "Удалить дубликаты" (в группе "Инструменты данных").

Выполните одно или несколько из следующих действий:

В разделе "Столбцы" выберите один или несколько столбцов.

Чтобы быстро выбрать все столбцы, нажмите "Выбрать все".

Чтобы быстро очистить все столбцы, нажмите "Отменить выбор всех".

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

Примечание. Данные будут удалены из всех столбцов, даже если вы не выберете все столбцы на этом шаге. Например, если вы выберете Column1 и Column2, но не Column3, то «ключ», используемый для поиска дубликатов, будет значением ОБА Column1 и Column2. Если в этих столбцах обнаружен дубликат, вся строка будет удалена, включая другие столбцы в таблице или диапазоне.

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

Отменить изменение, нажав «Отменить» (или нажав Ctrl+Z на клавиатуре).

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

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

Быстрое форматирование

Выполните следующие действия:

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

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

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

Расширенное форматирование

Выполните следующие действия:

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

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

Выполните одно из следующих действий:

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

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

В разделе "Выберите тип правила" нажмите "Форматировать только уникальные или повторяющиеся значения".

В списке «Форматировать все» в разделе «Редактировать описание правила» выберите «уникальное» или «дубликат».

Нажмите «Формат», чтобы открыть всплывающее окно «Формат ячеек».

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

В Excel для Интернета можно удалить повторяющиеся значения.

Удалить повторяющиеся значения

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

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

Выполните следующие действия:

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

На вкладке "Данные" нажмите "Удалить дубликаты" .

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

Примечание. Данные будут удалены из всех столбцов, даже если вы не выберете все столбцы на этом шаге. Например, если вы выберете Column1 и Column2, но не Column3, то «ключ», используемый для поиска дубликатов, будет значением ОБА Column1 и Column2. Если в Column1 и Column2 обнаружен дубликат, то будет удалена вся строка, включая данные из Column3.

Нажмите "ОК". Появится сообщение о том, сколько повторяющихся значений было удалено. Нажмите OK, чтобы закрыть это сообщение.

Примечание. Если вы хотите вернуть свои данные, просто нажмите «Отменить» (или нажмите Ctrl+Z на клавиатуре).

Нужна дополнительная помощь?

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

Как удалить все символы, кроме первых x, из ячеек в Excel?

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

Удалить все символы, кроме первых x, из ячеек с формулой

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

<р>1. Введите эту формулу в пустую ячейку, B1, например, =LEFT(A1,2) , см. снимок экрана:

Советы. В приведенной выше формуле A1 — это ячейка, которую вы хотите использовать, а число 2 указывает количество символов, которые вы хотите сохранить, вы можете изменить их по своему усмотрению.

<р>2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все символы будут удалены из ячеек, но останутся только первые два символа строки. Смотрите скриншот:

Примечание. Если вы хотите удалить все символы, кроме двух последних, вы можете использовать эту формулу: =RIGHT(A1,2) , и вы также можете изменить A1 и цифру 2 на другие, если вам нужно.

Удалить все символы, кроме первых x, из ячеек с помощью Kutools for Excel

Если вы установили Kutools for Excel, с помощью утилиты «Удалить по положению» вы можете быстро удалить первые или последние x символов, которые вам не нужны, и наоборот, вы также можете удалить все символы, кроме первых x.

После установки Kutools for Excel сделайте следующее: (Загрузите бесплатную пробную версию Kutools for Excel Now！)

<р>1. Выберите ячейки, из которых вы хотите удалить все символы, кроме первых двух.

<р>2. Нажмите Kutools > Текст > Удалить по положению, см. снимок экрана:

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

<р>4. Затем нажмите кнопку «ОК» или «Применить», в этом случае все символы будут удалены из текстовой строки, а останутся только первые два символа, см. скриншоты:

Удалить первые / последние x символов из ячеек с помощью Kutools for Excel

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

После установки Kutools for Excel сделайте следующее:

<р>1. Выберите диапазон данных, из которого вы хотите удалить первые два символа.

<р>2. Примените эту функцию, нажав Kutools > Текст > Удалить по положению, в появившемся диалоговом окне «Удалить по положению» укажите количество символов, которые нужно удалить в текстовом поле «Числа». Затем установите флажок «Слева», чтобы удалить первые x символов, и установите флажок «Справа», чтобы удалить последние x символов в разделе «Позиция». Вы можете просмотреть результат на панели предварительного просмотра. Смотрите скриншот:

<р>3. Затем нажмите кнопку "ОК" или "Применить", чтобы получить желаемый результат.

Демонстрация: удаление всех символов, кроме первых x x / удаление первых или последних x символов из ячеек

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

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

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

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

Решение работает в Excel 365, Excel 2021 и Excel 2019

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

Общая формула:

В Excel 365 и 2021 этот вариант также будет работать:

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

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

В Excel 365 – 2019:

=TEXTJOIN("", ИСТИНА, ЕСЛИОШИБКА(СРЕДН(A2, СТРОКА(ДВССЫЛ( "1:"&ДЛСТР(A2))), 1) *1, ""))

В Excel 2019 его необходимо ввести как формулу массива с помощью Ctrl + Shift + Enter . В динамическом массиве Excel это работает как обычная формула, завершаемая клавишей Enter.

В Excel 365 и 2021:

=TEXTJOIN("", ИСТИНА, ЕСЛИОШИБКА(СРЕДН(A2, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(A2)), 1) *1, ""))

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

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

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

Вы используете ROW(INDIRECT("1:"&LEN(string))) или SEQUENCE(LEN(string)) для создания последовательности чисел, соответствующих общему количеству символов в исходной строке, а затем передаете эти порядковые номера в MID функционируют как начальные номера. В B2 эта часть формулы выглядит следующим образом:

Функция MID извлекает каждый символ из A2, начиная с самого первого, и возвращает их в виде массива:

Функция ЕСЛИОШИБКА обрабатывает эти ошибки и заменяет их пустыми строками:

Этот окончательный массив передается функции TEXTJOIN, которая объединяет непустые значения в массиве (аргумент ignore_empty имеет значение TRUE), используя пустую строку ("") в качестве разделителя:< /p>

Совет. Для Excel 2016–2007 решение тоже существует, но формула гораздо сложнее. Вы можете найти его в этом руководстве: Как извлечь числа в Excel.

Пользовательская функция для удаления текста из чисел

Решение работает для всех версий Excel

Если вы используете старую версию Excel или считаете приведенные выше формулы слишком сложными для запоминания, ничто не мешает вам создать собственную функцию с более простым синтаксисом и удобным для пользователя именем, например RemoveText. . Пользовательскую функцию (UDF) можно записать двумя способами:

Код VBA 1:

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

Код 2 VBA:

Код создает объект для обработки регулярного выражения. Используя RegExp, мы удаляем из исходной строки все символы, кроме цифр 0–9.

На небольших листах оба кода работают одинаково хорошо. На больших листах, где функция вызывается сотни или тысячи раз, код 2, использующий VBScript.RegExp, будет работать быстрее.

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

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

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

Примечание. И собственные формулы, и пользовательская функция выводят числовую строку.Чтобы превратить его в число, умножьте результат на 1, или добавьте ноль, или заверните формулу в функцию ЗНАЧЕНИЕ. Например:

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

Решение работает в Excel 365, Excel 2021 и Excel 2019

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

Для Excel 365 – 2019:

TEXTJOIN("", TRUE, IF(ISERR(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) * 1), MID(ячейка, СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ячейка))), 1), ""))

В Excel 2019 не забудьте сделать формулу массива, нажав одновременно клавиши Ctrl + Shift + Enter.

Для Excel 365 и 2021:

TEXTJOIN("", TRUE, IF(ISERROR(MID(cell, SEQUENCE(LEN(cell 1) *1), MID(cell< /em>, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(ячейка)), 1), ""))

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

=TEXTJOIN("", ИСТИНА, ЕСЛИ(ЕОШИБКА(СРЕДН(A2, СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))), 1) *1), СРЕД(A2, СТРОКА(ДВССЫЛ("1 :"&ДЛСТР(A2))), 1), ""))

=TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "" ))

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

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

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

=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))

Теперь ваши результаты просто идеальны!

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

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

Функция ЕОШИБКА перехватывает ошибки и передает результирующий массив логических значений в ЕСЛИ:

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

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

Пользовательская функция для удаления чисел из текста

Решение работает для всех версий Excel

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

Код VBA 1:

Код 2 VBA:

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

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

В нашем случае формула в ячейке B2 выглядит так:

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

Разделить числа и текст на отдельные столбцы

В ситуации, когда вы хотите разделить текст и числа на два столбца, было бы неплохо выполнить эту работу с помощью одной формулы, согласны? Для этого мы просто объединяем код функций RemoveText и RemoveNumbers в одну функцию с именем SplitTextNumbers или просто Split или как вам больше нравится :)

Код VBA 1:

Код 2 VBA:

Наша новая пользовательская функция требует два аргумента:

Где is_remove_text — логическое значение, указывающее, какие символы следует удалить:

  • TRUE или 1 – удалить текст и оставить числа.
  • FALSE или 0 — удалить числа и оставить текст

Для нашего примера набора данных формулы имеют следующий вид:

Чтобы удалить нечисловые символы:

Чтобы удалить числовые символы:

=SplitTextNumbers(A2, FALSE)

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

Специальный инструмент для удаления чисел или текста

Для тех, кто не любит излишне усложнять, я покажу наш собственный способ удаления текста или чисел в Excel.

Предполагая, что наш Ultimate Suite добавлен на вашу ленту Excel, вы делаете следующее:

Совет. Если результаты содержат начальные пробелы, инструмент «Обрезать пробелы» мгновенно их удалит.

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

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