Удалить числа из ячейки Excel
Обновлено: 05.07.2024
Представьте себе: вы получаете необработанные данные для анализа и обнаруживаете, что числа смешаны с текстом в одном столбце. В большинстве случаев, безусловно, будет удобнее разместить их в отдельных столбцах для более подробного изучения.
Если вы работаете с однородными данными, вы, вероятно, могли бы использовать функции LEFT, RIGHT и MID для извлечения одинакового количества символов из одной и той же позиции. Но это идеальный сценарий для лабораторных испытаний. В реальной жизни вы, скорее всего, будете иметь дело с непохожими данными, где числа стоят перед текстом, после текста или между текстом. В приведенных ниже примерах представлены решения именно для этого случая.
Как удалить текст и сохранить числа в ячейках Excel
Решение работает в Excel 365, Excel 2021 и Excel 2019
В Microsoft Excel 2019 появилось несколько новых функций, недоступных в более ранних версиях, и мы собираемся использовать одну из таких функций, а именно TEXTJOIN, для удаления текстовых символов из ячейки, содержащей числа.
Общая формула:
В Excel 365 и 2021 этот вариант также будет работать:
На первый взгляд формулы могут показаться немного пугающими, но они работают :)
Например, чтобы удалить текст из чисел в A2, введите одну из приведенных ниже формул в B2, а затем скопируйте ее в нужное количество ячеек.
В Excel 365 – 2019:
=TEXTJOIN("", ИСТИНА, ЕСЛИОШИБКА(СРЕДН(A2, СТРОКА(ДВССЫЛ( "1:"&ДЛСТР(A2))), 1) *1, ""))
В Excel 2019 его необходимо ввести как формулу массива с помощью Ctrl + Shift + Enter . В динамическом массиве Excel это работает как обычная формула, завершаемая клавишей Enter.
В Excel 365 и 2021:
=TEXTJOIN("", ИСТИНА, ЕСЛИОШИБКА(СРЕДН(A2, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(A2)), 1) *1, ""))
В результате из ячейки удаляются все текстовые символы, а числа сохраняются:
Как работает эта формула:
Чтобы лучше понять логику, давайте начнем исследовать формулу изнутри:
Вы используете ROW(INDIRECT("1:"&LEN(string))) или SEQUENCE(LEN(string)) для создания последовательности чисел, соответствующих общему количеству символов в исходной строке, а затем передаете эти порядковые номера в MID функционируют как начальные номера. В B2 эта часть формулы выглядит следующим образом:
Функция MID извлекает каждый символ из A2, начиная с самого первого, и возвращает их в виде массива:
Функция ЕСЛИОШИБКА обрабатывает эти ошибки и заменяет их пустыми строками:
Этот окончательный массив передается функции TEXTJOIN, которая объединяет непустые значения в массиве (аргумент ignore_empty имеет значение TRUE), используя пустую строку ("") в качестве разделителя:< /p>
Совет. Для Excel 2016–2007 решение тоже существует, но формула гораздо сложнее. Вы можете найти его в этом руководстве: Как извлечь числа в Excel.
Пользовательская функция для удаления текста из чисел
Решение работает для всех версий Excel
Если вы используете старую версию Excel или считаете приведенные выше формулы слишком сложными для запоминания, ничто не мешает вам создать собственную функцию с более простым синтаксисом и удобным для пользователя именем, например RemoveText. . Пользовательскую функцию (UDF) можно записать двумя способами:
Код VBA 1:
Здесь мы просматриваем каждый символ в исходной строке один за другим и проверяем, является ли он числовым или нет. Если это число, к результирующей строке добавляется символ.
Код 2 VBA:
Код создает объект для обработки регулярного выражения. Используя RegExp, мы удаляем из исходной строки все символы, кроме цифр 0–9.
На небольших листах оба кода работают одинаково хорошо. На больших листах, где функция вызывается сотни или тысячи раз, код 2, использующий VBScript.RegExp, будет работать быстрее.
Какой бы подход вы ни выбрали, с точки зрения конечного пользователя функция удаления текста и сохранения чисел проста:
Например, чтобы удалить нечисловые символы из ячейки A2, используйте формулу в ячейке B2:
Просто скопируйте его вниз по столбцу, и вы получите следующий результат:
Примечание. И собственные формулы, и пользовательская функция выводят числовую строку. Чтобы превратить его в число, умножьте результат на 1, или добавьте ноль, или заверните формулу в функцию ЗНАЧЕНИЕ. Например:
Как удалить числа из текстовой строки в Excel
Решение работает в Excel 365, Excel 2021 и Excel 2019
Формулы для удаления чисел из буквенно-цифровой строки очень похожи на те, что обсуждались в предыдущем примере.
Для Excel 365 – 2019:
TEXTJOIN("", TRUE, IF(ISERR(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) * 1), MID(ячейка, СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ячейка))), 1), ""))
В Excel 2019 не забудьте сделать формулу массива, нажав одновременно клавиши Ctrl + Shift + Enter.
Для Excel 365 и 2021:
TEXTJOIN("", TRUE, IF(ISERROR(MID(cell, SEQUENCE(LEN(cell 1) *1), MID(cell< /em>, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(ячейка)), 1), ""))
Например, чтобы удалить числа из строки в A2, используйте следующую формулу:
=TEXTJOIN("", ИСТИНА, ЕСЛИ(ЕОШИБКА(СРЕДН(A2, СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))), 1) *1), СРЕД(A2, СТРОКА(ДВССЫЛ("1 :"&ДЛСТР(A2))), 1), ""))
=TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "" ))
В результате из ячейки удаляются все числа, а текстовые символы сохраняются:
Как показано на снимке экрана выше, формула удаляет числовые символы из любой позиции в строке: в начале, в конце и в середине. Однако есть одно предостережение: если строка начинается с числа, за которым следует пробел, этот пробел сохраняется, что создает проблему начальных пробелов (как в B2).
Чтобы избавиться от лишних пробелов перед текстом, заключите формулу в функцию TRIM следующим образом:
=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))
Теперь ваши результаты просто идеальны!
Как работает эта формула:
По сути, формула работает так же, как описано в предыдущем примере. Отличие в том, что из итогового массива, переданного функции TEXTJOIN, вам нужно удалить числа, а не текст. Для этого мы используем комбинацию функций ЕСЛИ и ЕОШИБКА.
Функция ЕОШИБКА перехватывает ошибки и передает результирующий массив логических значений в ЕСЛИ:
Когда функция ЕСЛИ принимает значение ИСТИНА (ошибка), она вставляет соответствующий текстовый символ в обрабатываемый массив с помощью другой функции MID. Когда функция ЕСЛИ встречает ЛОЖЬ (число), она заменяет его пустой строкой:
Этот окончательный массив передается в TEXTJOIN, поэтому он объединяет текстовые символы и выводит результат.
Пользовательская функция для удаления чисел из текста
Решение работает для всех версий Excel
Помня о том, что надежная формула должна быть простой, я приведу код определяемой пользователем функции (UDF), чтобы убрать любые числовые символы.
Код VBA 1:
Код 2 VBA:
Как и в случае с функцией RemoveText, второй код лучше использовать на больших листах для оптимизации производительности.
После добавления кода в книгу вы можете удалить все числовые символы из ячейки с помощью этой пользовательской функции:
В нашем случае формула в ячейке B2 выглядит так:
Чтобы обрезать начальные пробелы, если они есть, вложите пользовательскую функцию в TRIM, как в нативную формулу:
Разделить числа и текст на отдельные столбцы
В ситуации, когда вы хотите разделить текст и числа на два столбца, было бы неплохо выполнить эту работу с помощью одной формулы, согласны? Для этого мы просто объединяем код функций RemoveText и RemoveNumbers в одну функцию с именем SplitTextNumbers или просто Split или как вам больше нравится :)
Код VBA 1:
Код 2 VBA:
Наша новая пользовательская функция требует два аргумента:
Где is_remove_text — логическое значение, указывающее, какие символы следует удалить:
- TRUE или 1 – удалить текст и оставить числа.
- FALSE или 0 — удалить числа и оставить текст
Для нашего примера набора данных формулы имеют следующий вид:
Чтобы удалить нечисловые символы:
Чтобы удалить числовые символы:
=SplitTextNumbers(A2, FALSE)
Совет. Чтобы избежать потенциальной проблемы с начальными пробелами, я рекомендую всегда заключать формулу, удаляющую числа, в функцию TRIM:
Специальный инструмент для удаления чисел или текста
Для тех, кто не любит излишне усложнять, я покажу наш собственный способ удаления текста или чисел в Excel.
Предполагая, что наш Ultimate Suite добавлен на вашу ленту Excel, вы делаете следующее:
Совет. Если результаты содержат начальные пробелы, инструмент «Обрезать пробелы» мгновенно их удалит.
Вот как удалить текст или числовые символы из строки в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Мы обсудим две разные формулы для удаления чисел из текста в Excel.
ЗАМЕНИТЬ формулу функции
Мы можем использовать формулу, основанную на функции ПОДСТАВИТЬ. Это длинная формула, но это один из самых простых способов удалить числа из буквенно-цифровой строки.
В этой формуле мы вложили функции ПОДСТАВИТЬ 10 раз, например:
= ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( ЗАМЕНИТЬ ( B3 , 1 , "" ) , 2 , "" ) , 3 , "" ) , 4 , "" ) , 5 , "") , 6 , "" ) , 7 , "" ) , 8 , "" ) , 9 , "" ) , 0 , "" )
Формула массива TEXTJOIN
Чтобы удалить числа из буквенно-цифровых строк, мы также можем использовать формулу сложного массива, состоящую из функций TEXTJOIN, MID, ROW и INDIRECT.
Примечание. TEXTJOIN – это новая функция Excel, доступная в Excel 2019+ и Office 365.
Это сложная формула, поэтому мы разделим ее на этапы, чтобы лучше понять.
Шаг 1
Функция MID используется для извлечения буквенно-цифровой строки на основе аргументов start_num и num_chars.
Для аргумента start_num в функции MID мы будем использовать результирующий список массивов из функций ROW и INDIRECT.
А для аргумента num-chars мы поместим 1. После помещения аргументов в функцию MID она вернет массив.
Шаг 2
Шаг 3
После добавления 0 результирующий массив помещается в функцию ISERR. Поскольку мы знаем, что функция ISERR возвращает TRUE для ошибок и FALSE для безошибочных значений.
Таким образом, он выдаст массив значений ИСТИНА и ЛОЖЬ, ИСТИНА для нечисловых символов и ЛОЖЬ для чисел.
Шаг 4
Теперь мы добавим функцию ЕСЛИ.
Функция ЕСЛИ проверит результат функции ЕОШИБКА (шаг 3). Если его значение TRUE, он вернет массив всех символов буквенно-цифровой строки. Для этого мы добавили еще одну функцию MID без добавления нуля в конце. Если значение функции ЕСЛИ равно ЛОЖЬ, она вернет пустое значение ("").
Таким образом, у нас будет массив, содержащий только нечисловые символы строки.
= ЕСЛИ (ЕОШИБКА (СРЕДНЯЯ (B3, СТРОКА (ДВССЫЛ ("1:" &ДЛСТР (B3))), 1) + 0), СРЕДНЯ (B3, СТРОКА (ДВССЫЛ ("1:" &ДЛСТР (B3)) ) , 1 ), "" )
Шаг 5
Наконец, приведенный выше массив помещается в функцию TEXTJOIN. Функция TEXTJOIN объединит все символы указанного выше массива и проигнорирует пустую строку.
В качестве разделителя для этой функции задается пустая строка (""), а значение аргумента ignore_empty вводится как ИСТИНА.
Это даст нам желаемый результат, т.е.только нечисловые символы буквенно-цифровой строки.
Примечание. Это формула массива. При вводе формул массива в Excel 2019 или более ранней версии необходимо использовать клавиши CTRL + SHIFT + ВВОД для ввода формулы вместо обычного ВВОДА.
Вы узнаете, что правильно ввели формулу, по появившимся фигурным скобкам. НЕ вводите фигурные скобки вручную, формула не будет работать.
В Office 365 (и, предположительно, версиях Excel после 2019 г.) вы можете просто ввести формулу, как обычно.
Функция ОБРЕЗКИ
Когда числа удалены из строки, у нас могут остаться лишние пробелы. Чтобы удалить все конечные и начальные пробелы, а также лишние пробелы между словами, мы можем использовать функцию TRIM перед основной формулой, например:
Чтобы удалить числовые символы из текстовой строки, вы можете использовать формулу, основанную на функции TEXTJOIN. В показанном примере формула в C5 выглядит так:
Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter, за исключением Excel 365.
Excel не позволяет преобразовать буквы текстовой строки в массив непосредственно в формуле. В качестве обходного пути в этой формуле используется функция СРЕДН с помощью функций СТРОКА и ДВССЫЛ для достижения того же результата. Формула в C5, скопированная вниз, выглядит так:
Это выглядит довольно сложно, но суть в том, что мы создаем массив всех символов в B5 и проверяем каждый символ, чтобы определить, является ли он числом. Если это так, мы отбрасываем значение и заменяем его пустой строкой (""). Если нет, мы добавляем нечисловой символ в «обрабатываемый» массив. Наконец, мы используем функцию TEXTJOIN (новая в Excel 2019), чтобы объединить все символы вместе, игнорируя пустые значения.
который раскручивает массив, содержащий 100 чисел, например:
Примечание. 100 означает максимальное количество символов для обработки. Измените в соответствии со своими данными или используйте функцию ДЛСТР, как описано ниже.
Этот массив входит в функцию MID в качестве аргумента start_num. Для num_chars мы используем 1.
Функция MID возвращает такой массив:
Примечание. Дополнительные элементы в массиве удалены для удобства чтения.
Если не возникает ошибка, мы знаем, что у нас есть число, поэтому мы вставляем в массив пустую строку ("") вместо числа.
Конечный результат массива передается функции TEXTJOIN в качестве аргумента text1. Для разделителя мы используем пустую строку (""), а для ignore_empty мы указываем TRUE. Затем TEXTJOIN объединяет все непустые значения в массиве и возвращает результат.
Точная длина массива
Вместо жесткого кодирования числа, например 100, в ДВССЫЛ, вы можете использовать функцию ДЛСТР для построения массива с фактическим количеством символов в ячейке следующим образом:
LEN возвращает количество символов в ячейке в виде числа, которое используется вместо 100. Это позволяет автоматически масштабировать формулу до любого количества символов.
Удаление лишнего пробела
Когда вы удаляете числовые символы, у вас могут остаться лишние пробелы. Чтобы удалить начальные и конечные пробелы и нормализовать пробелы между словами, вы можете включить формулу, показанную на этой странице, в функцию TRIM:
С ПОСЛЕДОВАТЕЛЬНОСТЬЮ
В Excel 365 новая функция ПОСЛЕДОВАТЕЛЬНОСТЬ может заменить приведенный выше код СТРОКА + ДВССЫЛ:
Здесь мы используем SEQUENCE + LEN для построения массива правильной длины за один шаг.
С ПУСТЬ
Мы можем упростить эту формулу с помощью функции LET. Поскольку массив создается дважды выше с помощью SEQUENCE и LEN, мы можем определить массив как переменную и создать его только один раз:
Здесь значение массива задается только один раз, а затем используется дважды внутри функции MID.
В этой статье демонстрируется формула массива, которая извлекает все символы, кроме чисел, из ячейки, ячейка C3 содержит формулу и извлекает все, кроме чисел из ячейки B3, как показано на изображении выше.
Содержание
1. Как удалить числа из значения ячейки (Excel 2016)
Следующая формула содержит функцию ОБЪЕДИНЕНИЕ ТЕКСТ и работает только в Excel 2016.
Он позволяет фильтровать значения длиной до 1000 символов, и вы можете легко изменить это ограничение, изменив ссылку на эту ячейку: $A$1:$A$1000 в приведенной выше формуле.
Объяснение формулы массива Excel 2016 в ячейке C3
Шаг 1. Подсчет символов в ячейке B3
Функция ДЛСТР возвращает количество символов в значении ячейки.
Шаг 2. Создайте ссылку
Функция ИНДЕКС возвращает значение на основе номера строки и столбца (необязательно), однако ее также можно использовать для создания динамической ссылки на ячейку.
Шаг 3. Создайте ссылку на диапазон ячеек
Шаг 4. Создание номеров строк на основе ссылки на ячейку
Функция СТРОКА возвращает число, представляющее номер строки данной ссылки на ячейку.
Шаг 5. Разделение символов в ячейке B3
Функция MID возвращает подстроку из строки на основе начальной позиции и количества символов, которые вы хотите извлечь.
СРЕДНЯЯ(B3, СТРОКА($A$1:ИНДЕКС($A$1:$A$1000, ДЛСТР(B3))), 1)
Шаг 6. Удаление чисел из массива
Функция ТЕКСТ позволяет применить форматирование к заданному значению.
ТЕКСТ(СРЕДНЯЯ(B3, СТРОКА($A$1:ИНДЕКС($A$1:$A$1000, ДЛСТР(B3))), 1), "")
Шаг 7. Соедините оставшихся персонажей
Функция TEXTJOIN объединяет значения в диапазоне ячеек или массиве.
TEXTJOIN("", TRUE, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), 1), ""))
и возвращает "AABB".
2. Как удалить числа из значения ячейки (Excel 365)
Обновление новой формулы динамического массива от 12.01.2021 в ячейке C3:
Эта формула работает только с Excel 365, она содержит новую функцию ПОСЛЕДОВАТЕЛЬНОСТЬ, которая создает массив, содержащий числа от 1 до n.
2.1 Объяснение формулы динамического массива Excel 365 в ячейке C3
Шаг 1. Подсчет символов в ячейке B3
Функция ДЛСТР возвращает количество символов в значении ячейки.
Шаг 2. Создайте массив чисел от 1 до n
Функция SEQUENCE создает список порядковых номеров в диапазоне ячеек или массиве. Он находится в категории "Математика и тригонометрия" и доступен только подписчикам Excel 365.
Шаг 3. Разбить значение на символы
Функция MID возвращает подстроку из строки на основе начальной позиции и количества символов, которые вы хотите извлечь.
СРЕДНЯЯ(B3, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(B3)), 1)
Шаг 4. Удаление чисел из массива
Функция ТЕКСТ позволяет применить форматирование к заданному значению.
ТЕКСТ(СРЕДНИЙ(B3, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(B3)), 1), "")
Шаг 5. Соедините оставшихся персонажей
Функция TEXTJOIN объединяет значения в диапазоне ячеек или массиве.
TEXTJOIN("", TRUE, TEXT(MID(B3, SEQUENCE(LEN(B3)), 1), ""))
и возвращает "AABB".
Получить файл Excel *.xlsx
Еженедельный блог EMAIL
[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.
Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.
Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.
Статьи по теме
Ячейка B3 содержит несколько нечетных символов, а формула в C3 показывает ANSI-эквивалент каждого символа в […]
В этой статье объясняется, как заменить часть формулы во всех ячейках листа. Это проще, чем […]
Оставить ответ
Как комментировать
Как добавить формулу в комментарий
Вставьте сюда формулу.
Преобразование знаков меньше и больше
Используйте символы HTML вместо знаков меньше и больше.
становится >
Как добавить код VBA в свой комментарий
[vb 1="vbnet" language=","]
Поместите здесь код VBA.
[/vb]
Читайте также: