Как удалить цифры от алфавитно-цифровой строки в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: excel-text-formulas

Мы узнали, как связать: / excel-text-formulas-split-numbers-and-text-from-string-in-excel [удалить числовые значения из ячейки в Excel 2016 и старше]. Формулы, которые мы использовали, были немного сложными, но теперь Excel 2019 и 365 входят в игру с новыми игрушками, я имею в виду функции. В Excel 2019 и 365 представлены некоторые новые функции (TEXTJOIN и SEQUENCE)

это может упростить задачу удаления числовых символов и получения только нечисловых значений в новой ячейке. Мы будем использовать формулы, которые помогут нам сделать это более удобно. Generic Formula

=TEXTJOIN(«»,TRUE,IF(ISERROR(MID(jumbled_text,SEQUENCE(NumChars),1)+0),MID(jumbled_text,SEQUENCE*(NumChars),1),»»)))

Jumbled_text: это исходный текст, из которого вы хотите извлечь все числовые значения.

NumChars: это общее количество символов, которые вы хотите обработать.

В jumbled_text не должно быть больше символов, чем это число (вместе символов и чисел).

Давайте посмотрим на примере, чтобы прояснить ситуацию.

Пример: удалить числовые символы и извлечь все алфавиты и нечисловые символы.

Итак, у нас есть буквенно-цифровой текст. Этот текст содержит некоторые числа и некоторые нечисловые символы. Мне нужно избавиться от числовых символов и получить только алфавиты и другие символьные значения в столбце D.

Я не ожидаю, что общее количество символов в беспорядочном тексте будет больше 100. Итак, значение NumChars здесь равно 100. При необходимости вы можете увеличить или уменьшить это число.

Примените приведенную выше общую формулу, чтобы вырезать числовые символы.

=TEXTJOIN(«»,TRUE,IF(ISERROR(MID(B3,SEQUENCE(100),1)+0),MID(B3,SEQUENCE*(100),1),»»)))

Когда вы нажимаете кнопку ввода, вы удаляете все числовые символы, и остаются только буквенные значения. Перетащите эту формулу вниз, чтобы удалить числа из строки из всех ячеек в столбце B.

Как это работает?

Сначала давайте посмотрим, как эта формула решается шаг за шагом.

1→

TEXTJOIN(«»,TRUE,IF(ISERROR(MID(B3,SEQUENCE(100),1)+0),MID(B3,SEQUENCE*(100),1),»»)))

2→

TEXTJOIN(«»,TRUE,IF(ISERROR(MID(«This1

is…site»,\{1,2,3,…100},1)+0),MID(B3,SEQUENCE(100),1),»»)))

3→

TEXTJOIN(«»,TRUE,IF(ISERROR(\{«T»;»h»;»i»;»s»;»

«,»1″…»»;»»}+0),MID(B3,SEQUENCE(100),1),»»)))

4→

TEXTJOIN(«»,TRUE,IF(ISERROR(\{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1…;#VALUE!}),MID(B3,SEQUENCE*(100),1),»»)))

5→

TEXTJOIN(«»,TRUE,IF(\{TRUE;TRUE;TRUE;TRUE;FALSE…;TRUE},MID(B3,SEQUENCE*(100),1),»»)))

6→

TEXTJOIN(«»,TRUE,\{«T»;»h»;»i»;»s»;»»….;»»})

7→ «This is number one website»

Для удобства чтения я написал не весь массив. Я использовал точки (…) для обозначения длинных массивов .

Как видите, решение формулы начинается изнутри. Сначала решается ссылка: / excel-365-functions-the-sequence-function-in-excel [SEQUENCE function]. Поскольку мы передали 100 как количество символов. Он возвращает массив чисел от 1 до 100.

Этот массив передается функции MID в качестве начального номера. Функция mid переходит к каждому индексу в строке и разбивает каждый символ в массиве. Вы можете увидеть это на шаге 3. Я не показал весь массив, так как он займет слишком много места. \ {«T»; «h»; «i»; «s»; «», «1» … «»; «»} * Затем мы добавляем 0 к каждому символу. В Excel, если вы попытаетесь добавить число к нечисловым символам, это приведет к `link: / tips-value-error-and-how-to-fix-it-in-excel [#VALUE!

Ошибка] `. Получаем массив чисел и #VALUE! Ошибки.

\ {# VALUE!; # VALUE!; # VALUE!; # VALUE!; 1…; #VALUE!}

Этот массив передается в link: / excel-formula-and-function-iserror-function [ISERROR]

функция. Как вы знаете, функция ISERROR возвращает TRUE для ошибок и FALSE для значений, не являющихся ошибками. Следовательно, мы получаем массив ИСТИНА и ЛОЖЬ.

ИСТИНА для нечисловых символов и ЛОЖЬ для чисел.

\ {ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ…; ИСТИНА}.

То же самое происходит с ИСТИННЫМИ операторами раздела IF (link: / excel-text-formulas-the-mid-function-in-excel [MID] (B3, `link: / excel-365-functions-the -sequence-function-in-excel [SEQUENCE] ` (100), 1) ).

Он возвращает массив всех символов буквенно-цифровой строки в B3.

Теперь для каждого оператора ИСТИНА функция ЕСЛИ возвращает соответствующий символ из массива истинного раздела. Для FALSE IF возвращает пустое значение («»). Теперь у вас будет массив, не содержащий числовых символов. \{«Эта»;»»….;»»}. Наконец, этот массив обслуживается link: / excel-365-functions-the-excel-textjoin-function [TEXTJOIN *]

функция. Эта функция объединяет заданные тексты друг с другом, игнорируя пустые значения, с заданным разделителем. Таким образом, мы получаем строку, не содержащую числовых символов. Это сайт номер один *.

Улучшение формулы

В приведенной выше формуле используется жестко закодированное число для обработки количества символов (мы взяли 100). Но вы можете захотеть, чтобы он был динамичным. В этом случае, как вы угадали, вы можете использовать функцию LEN. Для обработки потребуется точное количество символов. Так что формула будет.

=TEXTJOIN(«»,TRUE,IF(ISERROR(MID(jumbled_text,SEQUENCE(LEN(jumbled_text),1)+0),MID(jumbled_text,SEQUENCE(LEN(jumbled_text)*,1),»»)))

Здесь функция LEN автоматически определит точное количество символов в буквенно-цифровой строке. Это избавит от бремени определения максимального количества символов.

Альтернатива функции ПОСЛЕДОВАТЕЛЬНОСТЬ

Если вы не хотите использовать функцию ПОСЛЕДОВАТЕЛЬНОСТЬ, вы можете использовать комбинацию функций СТРОКА и ДВССЫЛ для `link: / information-formulas-how-to-get-serial-row-number-in-excel [генерация последовательных чисел] `.

=TEXTJOIN(«»,TRUE,IF(ISERROR(MID(jumbled_text,ROW(INDIRECT(«1:»&`LEN(NumChars))),1)+0),`MID(jumbled_text,ROW(INDIRECT(«1:»&`LEN`(NumChars)))*,1),»»)))

ДВССЫЛ преобразует текст («1: 100») в фактический диапазон, а затем функция СТРОКА перечислит все номера строк от 1 до 100. (100 — это просто пример. Это может быть любое число).

Так что да, ребята, вот как вы можете скопировать нечисловые символы из буквенно-цифровой строки в Excel. Надеюсь, я достаточно объяснил, и эта статья вам помогла. Если у вас есть какие-либо вопросы по этой теме или любой другой теме excel / VBA. А пока продолжайте Excelling.

Статьи по теме:

link: / excel-text-formulas-how-to-remove-non-numeric-characters-from-cells-in-excel [Как удалить нечисловые символы из ячеек в Excel 2019:] Чтобы удалить нечисловые символы из буквенно-цифровую строку в Excel, мы используем новую функцию TEXTJOIN. Удаление нечисловых символов из строки может помочь нам очистить наши данные для лучшего анализа данных. Вот как это сделать link: / excel-text-formulas-split-numbers-and-text-from-string-in-excel [Разделить числа и текст из строки в Excel 2016 и более ранних версиях]: Когда мы этого не сделали ‘ У нас есть функция TEXTJOIN, мы использовали функции LEFT и RIGHT с некоторыми другими функциями для разделения числовых и нечисловых символов из строки. link: / excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [Извлечь текст из строки в Excel с помощью функции Excel ВЛЕВО и ВПРАВО]: Чтобы удалить текст в excel из строки, мы можем использовать функцию Excel LEFT и RIGHT. Эти функции помогают нам динамически нарезать строки. \ Link: / excel-text-formulas-remove-lead-and-trailing-space-from-text-in-excel [Удалить начальные и конечные пробелы из текста в Excel]: * Начальные и конечные пробелы трудно распознать визуально, и они могут испортить ваши данные. Удаление этих символов из строки — основная и самая важная задача очистки данных. Вот как это легко сделать в Excel.

link: / excel-text-formulas-remove-characters-from-right [Удалить символы справа]: * Чтобы удалить символы справа от строки в Excel, мы используем функцию LEFT. Да, функция ВЛЕВО. Функция LEFT сохраняет заданное количество символов от LEFT и удаляет все справа.

link: / excel-text-formulas-remove-unwanted-characters-in-excel [Удалить нежелательные символы в Excel]: * Чтобы удалить нежелательные символы из строки в Excel, мы используем функцию ЗАМЕНА. Функция SUBSTITUTE заменяет заданные символы другим заданным символом и создает новую измененную строку.

link: / excel-text-formulas-how-to-remove-text-in-excel-start-from-a-position [Как удалить текст в Excel, начиная с позиции в Excel]: * Чтобы удалить текст из начальная позиция в строке, мы используем функцию ЗАМЕНИТЬ в Excel. Эта функция помогает нам определить начальную позицию и количество символов для удаления.