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

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

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

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

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

• text: текстовая строка или значение ячейки, из которой нужно удалить все нечисловые символы.

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

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

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

Пояснение формулы:

СТРОКА(INDIRECT("1:100")): Число 1: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: Прибавление нуля к этому массиву принудительно преобразует текст в число: текстовые значения, представляющие цифры, станут числами, а нечисловые — превратятся в ошибку #ЗНАЧ!, например:
{"5";"0";"0";#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!…}

ЕСЛИОШИБКА(MID(A2,ROW(INDIRECT("1:100")),1)+0): Эта функция ЕСЛИОШИБКА заменяет все значения ошибок пустыми строками следующим образом:
{"5"; "0";"0";«»;«»; «»;«»;«»;«»;«»;«»; … }

TEXTJOIN(«»;ИСТИНА;IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,«»)): Наконец, функция TEXTJOIN объединяет все непустые значения из массива, возвращённого функцией ЕСЛИОШИБКА, и выводит итоговый результат.

Примечания:

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

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

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

Используемые связанные функции:

Дополнительные статьи: