Загрузите образец книги
В этом руководстве будет показано, как удалить числа из текста в ячейке в Excel и Google Таблицах.
Мы обсудим две разные формулы удаления чисел из текста в Excel.
ЗАМЕНА Функциональная формула
Мы можем использовать формулу, основанную на функции ЗАМЕНА. Это длинная формула, но это один из самых простых способов удалить числа из буквенно-цифровой строки.
В этой формуле мы вложили функции ЗАМЕЩЕНИЯ 10 раз, например:
| 1 | = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (B3,1, "")), 2, ""), 3, ""), 5, "") "), 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:" & ДЛИН (B3))) |
А для аргумента num-chars мы поставим 1. После помещения аргументов в функцию MID она вернет массив.
| 1 | {= MID (B3; ROW (INDIRECT ("1:" & LEN (B3))), 1)} |
Шаг 2
Мы добавим ноль к каждому значению в результирующем массиве (который мы получаем из приведенной выше функции MID). В Excel, если добавить числа к нечисловым символам, мы получим # ЗНАЧ! Ошибка. Итак, добавив 0 в приведенный выше массив, мы получим массив чисел и #Value! Ошибки.
| 1 | {= MID (B3; ROW (INDIRECT ("1:" & LEN (B3))), 1) +0} |
Шаг 3
После добавления 0 полученный массив помещается в функцию ISERR. Как мы знаем, функция ISERR возвращает TRUE для ошибок и FALSE для значений, не связанных с ошибками.
Таким образом, он даст массив ИСТИНА и ЛОЖЬ, ИСТИНА для нечисловых символов и ЛОЖЬ для чисел.
| 1 | = ISERR (MID (B3; ROW (INDIRECT ("1:" & LEN (B3))); 1) +0) |
Шаг 4
Теперь мы добавим функцию ЕСЛИ.
Функция ЕСЛИ проверяет результат функции ISERR (шаг 3). Если его значение - ИСТИНА, он вернет массив всех символов буквенно-цифровой строки. Для этого мы добавили еще одну функцию MID без добавления нуля в конце. Если значение функции ЕСЛИ ЛОЖЬ, она вернет пустое поле («»).
Таким образом, у нас будет массив, содержащий только нечисловые символы строки.
| 1 | = ЕСЛИ (ISERR (MID (B3; ROW (INDIRECT ("1:" & LEN (B3)))); 1) +0); MID (B3; ROW (INDIRECT ("1:" & LEN (B3))), 1 ), "") |
Шаг 5
Наконец, указанный выше массив помещается в функцию TEXTJOIN. Функция TEXTJOIN объединит все символы указанного выше массива и проигнорирует пустую строку.
В качестве разделителя для этой функции устанавливается пустая строка («»), а значение аргумента ignore_empty вводится как ИСТИНА.
Это даст нам желаемый результат, то есть только нечисловые символы буквенно-цифровой строки.
| 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 + ВВОД ввести формулу вместо обычного ВХОДИТЬ.
Вы узнаете, что правильно ввели формулу, по появившимся фигурным скобкам. НЕ вводите фигурные скобки вручную, формула не будет работать.
В Office 365 (и, предположительно, в версиях Excel после 2022 года) вы можете просто ввести формулу, как обычно.
Функция обрезки
Когда числа удаляются из строки, у нас могут остаться лишние пробелы. Чтобы удалить все конечные и ведущие пробелы, а также лишние пробелы между словами, мы можем использовать функцию TRIM перед основной формулой, например:
| 1 | = ОБРЕЗАТЬ (C3) |
Удалить числа из текста в Google Таблицах
Формула удаления чисел из текста в Google Таблицах работает точно так же, как и в Excel:
