Note: The other languages of the website are Google-translated. Back to English

Видаліть або видаліть нечислові символи з текстових рядків

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


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

В 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 клавіші разом, щоб отримати перший результат, див. скріншот:

3. Потім виділіть комірку формули, а потім перетягніть маркер заповнення вниз до комірок, до яких ви хочете застосувати цю формулу, витягнуто лише цифри, а всі інші нечислові символи видалено, див. Знімок екрана:


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

РЯД (НЕПРЯМИЙ ("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"; # ЗНАЧЕННЯ!; # ЗНАЧЕННЯ!; # ЗНАЧЕННЯ!; # ЗНАЧЕННЯ!; # ЗНАЧЕННЯ!; # ЗНАЧЕННЯ! !; # ЦІННІСТЬ! !; # ЦІННІСТЬ! ...}

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

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): Нарешті, ця функція TEXTJION поєднає всі непорожні значення в масиві, які повертає функція IFFERROR, і повертає результат.


примітки:

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)


Видаліть або видаліть усі нечислові символи з текстових рядків за допомогою простої функції

Можливо, це занадто довго, щоб згадувати наведені вище формули, ось, я вступлю до Kutools для Excel для вас, зі своїм Видалити символи Ви можете видалити числові, алфавітні, недруковані або буквено-цифрові символи з текстових рядків лише за кілька кліків. Натисніть, щоб завантажити Kutools для Excel для безкоштовної пробної версії!


Використовувані відносні функції:

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

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


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (4)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Дякую за це. Гарна формула. Як я можу змінити її так, щоб, якщо клітинка містить лише літери, формула вводить 0 в клітинку результатів (а не просто порожню, як вона є на даний момент)? Я подумав, що можу зробити це, загорнувши формулу в ще один оператор IF, але я не заходжу дуже далеко.
Цей коментар був мінімізований модератором на сайті
Привіт, Гленн! Щоб відобразити результати у вигляді пробілів, а не нулів, застосуйте таку формулу:=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, 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),"")

Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Вау, це займе деякий час 😋 Дякуємо, що знайшли час, щоб відповісти 👍
Цей коментар був мінімізований модератором на сайті
Що це за формула для французьких налаштувань?
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL