Перейти до основного матеріалу

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

Іноді вам може знадобитися видалити всі нецифрові символи з текстових рядків і зберігати лише цифри, як показано нижче. У цій статті будуть представлені деякі формули вирішення цього завдання в 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 - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...


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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What is this formula for french settings?
This comment was minimized by the moderator on the site
Thanks for this. Nice formula.How would I alter it so that if the cell contains only letters the formula enters a 0 the results cell (rather than just blank as it is at the moment)?Thought I might be able to do it by wrapping the formula in another IF statement but I’m not getting very far.
This comment was minimized by the moderator on the site
Hello, Glenn,To display the results as blanks rather than zeros, please apply the following formula:=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),"")

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Wow that’ll take some digesting 😋 Thanks for taking the time to reply 👍
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations