Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

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

Author Xiaoyang Last modified

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

doc-strip-non-numeric-1


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

В Excel 2019 и Office 365 новая функция TEXTJOIN в сочетании с функциями IFERROR, MID, ROW и INDIRECT может помочь вам извлечь только числа из текстовой строки. Общий синтаксис выглядит так:

=TEXTJOIN("",TRUE,IFERROR(MID(text,ROW(INDIRECT("1:100")),1)+0,""))
  • text: текстовая строка или значение ячейки, из которой вы хотите удалить все нечисловые символы.

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

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

2. Затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить первый результат, см. скриншот:

doc-strip-non-numeric-2

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

doc-strip-non-numeric-3


Объяснение формулы:

ROW(INDIRECT("1:100"):Число 1:100 в формуле INDIRECT означает, что функция MID оценивает 100 символов текстовой строки. Этот массив будет содержать 100 чисел, например: {1;2;3;4;5;6;7;8....98;99;100}.
Примечание: Если ваша текстовая строка намного длиннее, вы можете изменить число 100 на большее по необходимости.

MID(A2,ROW(INDIRECT("1:100")),1: Эта функция MID используется для извлечения текста из ячейки A2, чтобы получить один символ, и она создаст массив, подобный этому:
{"5";"0";"0";" ";"K";"u";"t";"o";"o";"l";"s";" ";"f";"o";"r";" ";"E";"x";"c";"e";"l";"";"";"";"";"";""...}

MID(A2,ROW(INDIRECT("1:100")),1)+0: Добавление значения 0 после этого массива используется для преобразования текста в число. Числовые текстовые значения будут преобразованы в числа, а нечисловые значения будут отображаться как ошибочные значения #VALUE, например:
{"5";"0";"0";#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! !;#VALUE! !;#VALUE!...}

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0: Эта функция IFERROR используется для замены всех ошибочных значений пустой строкой, например:
{"5"; "0";"0";"";""; "";"";"";"";"";""; … }

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): Наконец, эта функция TEXTJOIN объединит все непустые значения в массиве, который вернула функция IFERROR, и вернет результат.

doc-strip-non-numeric-4


Примечания:

1. С помощью приведенной выше формулы числа будут возвращены в текстовом формате. Если вам нужен реальный числовой формат, используйте эту формулу, не забудьте нажать клавиши Ctrl + Shift + Enter вместе, чтобы получить правильный результат.

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))+0

2. В ранних версиях Excel эта формула не будет работать. В этом случае следующая формула может помочь. Пожалуйста, скопируйте или введите эту формулу в пустую ячейку:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

doc-strip-non-numeric-5


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

Может быть, слишком сложно запомнить все эти формулы, поэтому позвольте представить вам Kutools for Excel. С его функцией Remove Characters вы сможете удалять числовые, буквенные, непечатаемые или буквенно-цифровые символы из текстовых строк всего за несколько кликов. Нажмите, чтобы скачать Kutools for Excel для бесплатной пробной версии!


Связанные функции:

  • TEXTJOIN:
  • Функция TEXTJOIN объединяет несколько значений из строки, столбца или диапазона ячеек с определенным разделителем.
  • MID:
  • Функция MID используется для поиска и возврата определенного количества символов из середины заданной текстовой строки.
  • ROW:
  • Функция ROW в Excel возвращает номер строки ссылки.
  • INDIRECT:
  • Функция INDIRECT в Excel преобразует текстовую строку в действительную ссылку.
  • IFERROR:
  • Функция IFERROR используется для возврата пользовательского результата, когда формула выдает ошибку, и возвращает нормальный результат, если ошибки нет.

Больше статей:

  • Удалить разрывы строк из ячеек в Excel
  • Это руководство предоставляет три формулы, которые помогут вам удалить разрывы строк (которые возникают при нажатии клавиш Alt + Enter в ячейке) из определенных ячеек в Excel.

Лучшие инструменты для повышения производительности Office

Kutools для Excel - Помогает вам выделиться из толпы

🤖 KUTOOLS AI Помощник: Революционизируйте анализ данных на основе: Интеллектуальное выполнение   |  Генерация кода  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools
Популярные функции: Поиск, Выделение или Отметка дубликатов  |  Удалить пустые строки  |  Объединить столбцы или ячейки без потери данных  |  Округлить без формулы ...
Супер VLookup: Множественные критерии  |  Множественные значения  |  На нескольких листах  |  Распознавание нечетких соответствий...
Расширенный раскрывающийся список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Раскрывающийся список с множественным выбором...
Управление столбцами: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы для выбора одинаковых и разных ячеек ...
Основные функции: Сетка фокусировки  |  Дизайн листа  |  Улучшенная строка формул  |  Управление книгами и листами | Библиотека автотекста (Авто текст)  |  Выбор даты  |  Объединить данные  |  Шифрование/Расшифровка ячеек  |  Отправка писем по списку  |  Супер фильтр  |  Специальный фильтр (фильтр жирного/курсивного/зачеркнутого текста...) ...
Топ-15 наборов инструментов:  12 инструментов для работы с текстом (Добавить текст, Удалить определенные символы ...)  |  50+ типов диаграмм (Диаграмма Ганта ...)  |  40+ практических формул (Расчет возраста на основе даты рождения ...)  |  19 инструментов вставки (Вставить QR-код, Вставить изображение по пути ...)  |  12 инструментов преобразования (Преобразовать в слова, Конвертация валюты ...)  |  7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки Excel ...)  |  ... и многое другое
Используйте Kutools на вашем предпочитаемом языке – поддерживает английский, испанский, немецкий, французский, китайский и более 40 других языков!

Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...


Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)

  • Один щелчок, чтобы переключаться между десятками открытых документов!
  • Сократите сотни кликов мышью каждый день, попрощайтесь с болью в руке от мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.