Завантажте зразок робочої книги
У цьому посібнику буде показано, як видалити цифри з тексту в комірці в Excel та Google Таблицях.
Ми обговоримо дві різні формули для видалення чисел з тексту в Excel.
ЗАМІНА Формула функції
Ми можемо використовувати формулу на основі функції ЗАМІНИ. Це довга формула, але це один з найпростіших способів видалення чисел з буквено -цифрового рядка.
У цій формулі ми вкладали функції SUBSTITUTE 10 разів, наприклад:
| 1 | = ЗАМІНКА (ЗАМІНКА (ЗАМІНА (ЗАМІНА (ЗАМІНА (ЗАМІНА) "), 6," "), 7," "), 8," "), 9," "), 0," ") |
Формула масиву TEXTJOIN
Щоб видалити числа з буквено -цифрових рядків, ми також можемо використовувати складну формулу масиву, яка складається з функцій TEXTJOIN, MID, ROW та INDIRECT.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Примітка: TEXTJOIN - це нова функція Excel, доступна в Excel 2022+ та Office 365.
Це складна формула, тому ми поділимо її на кроки, щоб краще зрозуміти її.
Крок 1
Функція MID використовується для вилучення буквено -цифрового рядка на основі аргументів start_num і num_chars.
Для аргументу start_num у функції MID ми будемо використовувати отриманий список масивів із функцій ROW та INDIRECT.
| 1 | = РЯДОК (НЕПРЯМИЙ ("1:" & LEN (B3))) |
А для аргументу num-chars ми поставимо 1. Після розміщення аргументів у функції MID він поверне масив.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)} |
Крок 2
Ми додамо нуль до кожного значення в результуючому масиві (який ми отримуємо з наведеної вище функції MID). В excel, якщо додати цифри до нечислових символів, ми отримаємо #VALUE! Помилка. Отже, після додавання 0 у наведеному вище масиві ми отримаємо масив чисел та #Value! Помилки.
| 1 | {= MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0} |
Крок 3
Після додавання 0 отриманий масив поміщається у функцію ISERR. Як ми знаємо, функція ISERR повертає TRUE для помилок і FALSE для значень, що не містять помилок.
Отже, він дасть масив TRUE та FALSE, TRUE для нечислових символів та FALSE для чисел.
| 1 | = ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0) |
Крок 4
Тепер ми додамо функцію IF.
Функція IF перевірить результат функції ISERR (крок 3). Якщо його значення TRUE, воно поверне масив усіх символів буквено -цифрового рядка. Для цього ми додали ще одну функцію MID без додавання нуля в кінці. Якщо значення функції IF - FALSE, вона поверне порожнє значення (“”).
Таким чином, у нас буде масив, який містить лише нечислові символи рядка.
| 1 | = IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) +0), MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1 ), "") |
Крок 5
Нарешті, вищевказаний масив поміщається у функцію TEXTJOIN. Функція TEXTJOIN об'єднає всі символи вищезазначеного масиву та ігнорує порожній рядок.
Розмежувач для цієї функції встановлюється порожнім рядком (“”), а значення аргументу ignore_empty вводиться як TRUE.
Це дасть нам бажаний результат, тобто лише нечислові символи буквено-цифрового рядка.
| 1 | {= TEXTJOIN ("", TRUE, IF (ISERR (MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1) +0), MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1), ""))} |
Примітка: Це формула масиву. Під час введення формул масиву в Excel 2022 або більш ранніх версіях потрібно використовувати CTRL + SHIFT + ENTER ввести формулу замість звичайної ENTER.
Ви дізнаєтесь, що правильно ввели формулу за фігурними дужками, що з’являються. НЕ вводите вручну фігурні дужки, формула не працюватиме.
За допомогою Office 365 (і, ймовірно, версій Excel після 2022 року) ви можете просто ввести формулу, як звичайно.
Функція TRIM
Коли числа видаляються з рядка, у нас може залишитися зайвий пробіл. Щоб видалити всі кінцеві та провідні пробіли та зайві пробіли між словами, ми можемо скористатися функцією TRIM перед основною формулою, наприклад:
| 1 | = ТРИМ (C3) |
Видалити цифри з тексту в Таблицях Google
Формула видалення чисел з тексту працює точно так само в Google Таблицях, як і в Excel:
