Excel - как сохранить в ячейке только цифры, а не буквы.
Смотреть видео
- Проверьте столбец, чтобы увидеть, есть ли в коде какие-либо цифры
- Билл Метод 1:
- Flash Fill
- Майк Метод:
- Используйте функцию ЗАМЕНА с константой массива.
- Вам не придется использовать Ctrl + Shift + Enter, потому что это константа массива
- Это удалит по одной цифре за раз
- Используйте функцию И, чтобы убедиться, что каждый элемент в результирующем массиве равен исходному элементу
- Билл Метод 3:
- Используйте функцию VBA для проверки цифр
Стенограмма видео
Билл: Привет. С возвращением. Пришло время для еще одного подкаста Dueling Excel. Я Билл Джелен из. Ко мне присоединится Майк Гирвин из ExcelIsFun. Это наша 186 серия: да, если в ячейке есть цифры. Сегодняшний вопрос прислала Джен, в нем 13 000 строк данных, необходимо просмотреть ячейку, если какой-либо символ является цифрой, отметьте его как «Да», в противном случае - «Нет». Хорошо. Что ж, Майк, я надеюсь, что у тебя есть отличный способ сделать это, потому что у меня нет.
Я собираюсь использовать флэш-заливку, а во флэш-заливке я просто дам ему узор с несколькими буквами и цифрами. Я хочу убедиться, что я включил все возможные числа, чтобы он понял, что я делаю, и 0 в этом роде. Итак, есть исходные данные, и я собираюсь исправить это для меня, и в фиксированной версии мы собираемся избавиться от всех цифр. Итак, я посмотрю, есть ли цифра. Если есть, избавьтесь от него вот так, а затем нажмите CONTROL + E, чтобы залить заливку, и теперь у нас должны быть только буквы, только буквы.
И тогда вопрос, изменилось ли это? Итак = ЕСЛИ это = то, что мы ищем, то это означает, что не было никаких изменений, никаких цифр, тогда мы говорим Нет, в противном случае Да, вот так, и дважды щелкните, скопируйте это вниз, хорошо, и то все, что имеет «Нет», не имеет цифр. Мы избавляемся от нашей исходной строки, и как только мы ее копируем, CONTROL + C, ALT + E, S, V, и мы можем избавиться от флэш-заливки. Хорошо, Майк. Посмотрим, что у вас есть. (= ЕСЛИ (A2 = B2, «Нет», «Да»))
Майк: Вау. Это должно быть самое удивительное творческое использование флэш-заполнения, которое я когда-либо видел. Вы придумали текст, вытащили реальные числа, CONTROL + E, и он мгновенно извлек все числа и доставил новую текстовую строку без чисел, а затем вы выполнили свой IF. Абсолютно красивый.
Хорошо. Я перейду к этому листу прямо здесь и воспользуюсь функцией ЗАМЕНА. Теперь, SUBSTITUTE, я скажу ему посмотреть на этот текст прямо здесь, и OLD_TEXT, который я хочу найти и удалить, ну, это все цифры. Итак, я собираюсь создать константу массива (1, 2, 3, 4, 5 все цифры и). Теперь это константа массива, и она находится в OLD_TEXT, потому что я не помещаю туда ни одного элемента, а вместо этого помещаю кучу элементов. Это операция с массивом аргументов функции. Здесь есть 10 различных элементов, которые заставят SUBSTITUTE правильно доставить 10 отдельных элементов, и если он найдет один из этих элементов, что мне нужно? «». Это скажет функции ничего не помещать). (= ПОДСТАВИТЬ (A2, (1,2,3,4,5,6,7,8,9,0), «»))
Теперь мой курсор в конце. Когда я нажимаю клавишу F9, конечно, потому что там только 0, все они в точности равны, за исключением последнего. В последнем случае ЗАМЕНА нашла 0 и ничего не заменила. Теперь, CONTROL-Z, CONTROL-ENTER, и я скопирую это прямо сюда, F2 и F9. Итак, если мы перейдем к 6, есть версия 5, она удалила 5 там, удалила 6 там, и удалила 8 там, так что будут 1, 2, 3 разных элемента. Только когда все элементы будут в точности равны оригиналу, он сообщит нам, что нет цифр. ПОБЕГ.
Я собираюсь вернуться наверх. Похоже, F2, это проверка логического И. И функция. Я хочу проверить, соответствует ли каждый из этих элементов в результирующем массиве исходному элементу. Когда все они верны, он скажет мне, что в этом текстовом кольце нет цифр. ), CONTROL + ENTER, я получаю ЛОЖЬ, потому что у одного из них внутренне отсутствует этот 0. Я скопирую это здесь. Этот, конечно же, получит ИСТИНУ - то же самое с ними - потому что все внутренне сгенерированные элементы, если я F2 прямо здесь, все они, F9, в точности равны оригиналу. ПОБЕГ. Теперь я подхожу к вершине. Кстати, мне не нужно было использовать CONTROL + SHIFT + ENTER, потому что, когда вы используете эту константу массива в своей формуле массива, вам не нужно использовать CONTROL + SHIFT + ENTER. (= И (ПОДСТАВИТЬ (A2; (1,2,3,4,5,6,7,8,9,0), «») = A2))
Хорошо. Я собираюсь подойти к началу. Это мой логический тест. Если все это оказывается правдой,, значение, если правда, в «НЕТ», в противном случае укажите ДА »). КОНТРЛ + ВВОД. Дважды щелкните по нему. Мне нужно будет дважды щелкнуть по нему и отправить его. Хорошо. Это было немного забавно с ПОДСТАВКОЙ, константой массива, логическим тестом И и ЕСЛИ, но, скажу вам вот что, я до сих пор не могу поверить в то, как вы использовали его, по сути, для извлечения всех числа от этого. Хорошо, я заброшу его тебе, мистер Эксель. (= ЕСЛИ (И (ПОДСТАВИТЬ (A2, (1,2,3,4,5,6,7,8,9,0), «») = A2), «Нет», «Да»))
Билл: Ну, эта формула с ПОДСТАВКОЙ, константой массива и И, чертовски потрясающая. Мне пришлось использовать флэш-заливку, потому что я не мог понять этого. Это великолепно. Теперь у меня есть третий путь. Давайте посмотрим на это.
Вот способ, которым я бы действительно решил это, просто немного VBA. Итак, я нажимаю ALT + F11, чтобы переключиться на VBA, INSERT, MODULE, а затем набираю этот код. Мы собираемся создать новую функцию под названием HASNUMBERS, и мы собираемся передать ее в значение ячейки, и мы собираемся начать со слова ALPHA. Мы смотрим на каждый символ, и если этот код, если (код ASC - 06:35) этого символа находится в диапазоне от 48 до 57, то мы говорим, что это НОМЕРА, ФУНКЦИЯ ВЫХОДА, и просто продолжаем. Итак, он ищет, пока не найдет цифру. Когда это произойдет, он вернет HASNUMBERS. Итак, здесь мы собираемся сказать = HASNUMBERS, указать на эту ячейку и дважды щелкнуть, чтобы скопировать ее. Каждый раз, когда он видит там цифру, он получает НОМЕРА, АЛЬФА, их легко разобрать. (= HasNumbers (A2))
Хорошо, краткое описание эпизода. Цель: проверить столбец, чтобы увидеть, есть ли какие-нибудь цифры в коде символа, коде в ячейке. Я использовал флэш-заливку, чтобы удалить цифры, затем функцию длины, чтобы увидеть, изменилось она или нет. У Майка была блестящая формула - функция ЗАМЕНА с константой массива. Вам не нужны CONTROL + SHIFT + ENTER. Вы должны удалять по одной цифре за раз, а затем использовать функцию И, чтобы просмотреть все 10 результатов, чтобы убедиться, что каждый из них равен исходному элементу. Блестящий способ, а затем, мой запасной вариант, используйте функцию VBA для проверки цифр.
Ну привет. Я хочу поблагодарить всех, что заглянули. Увидимся в следующий раз на другом сетевом трансляции от ExcelIsFun.
Скачать файл
Загрузите образец файла здесь: Duel186.xlsm
