Как оставить в ячейке только цифры или только текст?
Вот бывает так: есть у Вас в ячейке некий текст. Допустим «Было доставлено кусков мыла 763шт.». Вам нужно из этого только 763 — чтобы можно было провести с этим некие математические действия. Если это только одна ячейка — проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?
Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.
СПОСОБ 1: не используем макросы
можно применить формулу массива, вроде такой:
=ПСТР( A1 ;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))+1)
Три важных момента:
- Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
- в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА( $1:$99 ) на СТРОКА( $1:$200 ) . Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
- формула не обработает корректно текст » Было доставлено кусков мыла 763шт., а заказывали 780 » и ему подобный, где числа раскиданы по тексту.
Теперь коротко разберем формулу на примере фразы: Было доставлено кусков мыла 763шт.
- в A1 сам текст, из которого необходимо извлечь числа: Было доставлено кусков мыла 763шт., а заказывали 780
- блок: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))
вычисляет позицию первой цифры в ячейке — 29 - блок: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))
вычисляет позицию последней цифры в ячейке — 31 - в результате получается: =ПСТР( A1 ;29;31—29+1)
функция ПСТР извлекает из текста, указанного первым аргументом( A1 ) текст, начиная с указанной позиции(29) с количеством символов, указанным третьим аргументом(31—29+1) - И в итоге:
=ПСТР( A1 ;29;31—29+1)
=> =ПСТР( A1 ;29;2+1)
=> =ПСТР( A1 ;29;3)
=> 763
Подробнее про создание пользовательских функции и их применении можно почитать в статье Что такое функция пользователя(UDF)?
Помимо функции пользователя решил выложить и вариант с использованием диалогового окна:
Выбрать ячейку или диапазон с текстом(Лист1! $A$2:$A$10 ) — здесь указывается диапазон с исходными значениями, из которого необходимо оставить только числа или только текст.
Оставить только цифры, Оставить только текст— думаю не надо пояснять. Здесь выбираем, что оставить в качестве результата.
Небольшое дополнение к использованию кода
В коде есть строка:
Данная строка отвечает за текстовые символы, которые могут встречаться внутри чисел и которые надо оставить(не удалять наравне с другими не числовыми символами). Следовательно, если какие-то из данных символов не нужны в конечном тексте — их надо просто удалить. Например, чтобы оставались исключительно числа(без запятых и пр.):
если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):
Как убрать повторяющиеся значения в Excel? | Mister-Office
Удаление дубликатов в Excel — обычная операция. Однако в каждом конкретном случае может быть ряд особенностей. В то время как инструмент Quick Dedupe фокусируется на скорости, Duplicate Remover предлагает ряд дополнительных опций для работы с дубликатами и уникальными значениями.
Удаление вручную.
Минусы: никаких дополнительных возможностей.
Итак, воспользуемся помощью стандартной функции «Найти и заменить»:
- Выделите все позиции, которые вы хотите обработать.
- Нажмите Ctrl + H , чтобы открыть диалоговое окно «Найти и заменить».
- В поле » Найти» введите Ctrl + J . Оно будет выглядеть пустым, но вы увидите крошечную точку.
- В поле «Заменить на» введите любое значение для замены. Обычно это пробел, чтобы избежать случайного «слипания» двух слов. Если вам нужно просто удалить разрывы строк, оставьте поле пустым.
- Нажимаем кнопку «Заменить все» и наслаждаемся результатом!
Удаление раскрывающегося списка в Microsoft Excel
- Добавьте вспомогательный столбец в конец ваших данных. Вы можете назвать его «В одну строку».
- В первой его позиции (C2) введите формулу для удаления или замены переносов строк. Здесь мы представим вам несколько полезных формул для разных случаев:
В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013 и более ранними версиями.
Вариант 2: Меню «Проверка вводимых значений»
Если ранее вы самостоятельно создавали раскрывающийся список, то уже знаете, через какое именно меню это делается. Его можно использовать и для очистки данных, удалив все значения кроме выбранного. Это отличный вариант в тех случаях, когда нужно сохранить текущее число или текст в ячейке, но удалить все остальные варианты списка.
Выделите список левой кнопкой мыши, перейдите на вкладку «Данные» и откройте окно «Проверка данных».
Слева внизу вы увидите кнопку «Очистить все», которая и удаляет все выставленные условия проверки, то есть в данном случае – выпадающий список.
Теперь вы увидите, что тип данных изменился на «Любое значение» и пропала строка со значениями списка.
Вернитесь к таблице и проверьте результативность выполненных действий.
Как убрать ошибки в ячейках Excel
Вот как вы можно выделить дубликаты в Excel с помощью надстройки Duplicate Remover. Если вам интересно попробовать этот инструмент на своих рабочих таблицах, вы можете загрузить полнофункциональную пробную версию Ultimate Suite, которая включает в себя более 60 инструментов для экономии времени при работе в Excel. Будем очень благодарны за ваши отзывы в комментариях!
Как показать третий, четвертый и все последующие дубликаты?
Чтобы просмотреть повторяющиеся значения, начинающиеся с N-го вхождения, действуйте как в предыдущем примере. С той лишь разницей, что вы заменяете > 1 в конце формулы на требуемое число. Например:
Чтобы раскрасить третий и все последующие повторы, примените это:
Чтобы указать на 4-е и все последующие повторяющиеся записи, используйте выражение:
Чтобы выделить только определенные вхождения, используйте оператор равенства (=).
Например, чтобы изменить фон только 2-х экземпляров, вы должны использовать следующую формулу:
Как убрать нули в ячейках в Excel?.
- Выберите данные, которые вы хотите проверить. Это может быть колонка, строка или диапазон ячеек.
- На вкладке « Главная » выберите « Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».
Здесь мы показали выделение последовательных повторяющихся текстов, но это также будет работать для последовательных повторяющихся чисел и дат.
Если на вашем листе Excel могут быть пустые строки и вы не хотите, чтобы следующие друг за другом пустые ячейки выделялись цветом, внесите следующие улучшения в формулы:
Как убрать дубликаты строк с помощью формул.
Еще один способ удалить неуникальные данные — идентифицировать их с помощью формулы, затем отфильтровать, а затем после этого удалить лишнее.
Преимущество этого подхода заключается в универсальности: он позволяет вам:
Недостатком является то, что вам нужно будет запомнить несколько формул.
В зависимости от вашей задачи используйте одну из следующих формул для обнаружения повторов.
Формулы для поиска повторяющихся значений в одном столбце
Добавляем еще одну колонку, в которой запишем формулу.
Повторы наименований товаров, без учета первого вхождения:
Как видите, когда значение встречается впервые (к примеру, в B4), оно рассматривается как вполне обычное. А вот второе его появление (в B7) уже считается повтором.
Где A2 — первая, а A10 — последняя ячейка диапазона, в котором нужно найти совпадения.
Ну а теперь, чтобы убрать ненужное, устанавливаем фильтр и в столбце H и оставляем только «Дубликат». После чего строки, оставшиеся на экране, просто удаляем.
- Выберите любую ячейку и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «Данные».
- Отфильтруйте повторяющиеся строки, щелкнув стрелку в заголовке нужного столбца.
- И, наконец, удалите повторы. Для этого выберите отфильтрованные строки, перетаскивая указатель мыши по их номерам, щелкните правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. Причина, по которой вам нужно сделать это вместо простого нажатия кнопки «Удалить» на клавиатуре, заключается в том, что это действие будет удалять целые строки, а не только содержимое ячейки.
Формулы для поиска повторяющихся строк.
В случае, если нам нужно найти и удалить повторяющиеся строки (либо часть их), действуем таким же образом, как для отдельных ячеек. Только формулу немного меняем.
Отмечаем при помощи формулы неуникальные строчки, кроме 1- го вхождения:
Теперь самый простой вариант действий – устанавливаем фильтр по столбцу H и слову «Дубликат». После этого просто удаляем сразу все отфильтрованные строки.
Если нам нужно исключить все повторяющиеся строки вместе с их первым появлением:
Далее вновь устанавливаем фильтр и действуем аналогично описанному выше.
Больше возможностей для поиска дубликатов при помощи Duplicate Remover.
- Выберите любую ячейку в таблице, c которой вы хотите работать, и нажмите Quick Dedupe на вкладке Ablebits Data.
- Откроется диалоговое окно, и все столбцы будут выбраны по умолчанию. Выберите те, которые вам нужны, а также в выпадающем списке в правом нижнем углу укажите желаемое действие.
Для этого используйте функцию СЧЁТЕСЛИМН, которая позволяет сравнивать по нескольким критериям. Например, чтобы выделить строки с одинаковыми значениями в B и C, то есть найти заказы одного и того же товара одним заказчиком, используйте одну из следующих формул:
Ошибка #ПУСТО! в Excel
В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.
Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!
Создание и применение фильтра
В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013 и более ранними версиями.
В сводных таблицах
Для начала пусть у нас имеется простая и небольшая таблица с данными (при этом в таблице не все ячейки будут заполнены), на основе которой мы построим сводную таблицу:
Задача удаления нулей из сводной таблицы можно условно разделить на 2 направления:
- Работа с данными как с ячейками листа;
В этом случае мы не делаем разницы между ячейками сводной таблицы и ячейками листа, т.е. можем воспользоваться вышеописанными способами, чтобы скрыть 0. - Работа с данными в сводной таблице.
В этом случае мы работаем не с ячейками листа, а непосредственно со сводной таблицей.
Так как первый вариант мы уже детально разбирали выше, то давайте поподробнее остановимся на втором, в котором также выделим 2 способа, чтобы убрать нули из таблицы:
Создание и применение фильтра
Давайте в сводной таблице добавим в качестве фильтра поля, по которым мы хотим скрыть нули (в данном примере это Количество, но в принципе тут может стоят все что угодно).
Теперь щелкаем по значку фильтра для поля Количество и в выпадающем списке снимаем галочку напротив значений 0 или (пусто), тем самым исключив эти значения из отображения:
Как мы видим строки, где находились нулевые значения, скрылись, что нам и требовалось.
Настройка параметров сводной таблицы
С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:
В разделе Формат нас интересует пункт Для пустых ячеек отображать, чтобы убрать нули в сводной таблице, нужно поставить галочку в соответствующем поле и оставить поле справа пустым.
В этом случае получается, что пустые ячейки из исходной таблицы подтянулись в сводную, а уже затем мы задали конкретный формат (пустое поле) для их отображения. В итоге получаем:
Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.
Вариант 2: Меню «Проверка вводимых значений»
- A — запись когда число положительное;
- B — запись когда число отрицательное;
- C — запись когда число равно нулю;
- D — запись если в ячейке не число, а текст (обычно для чисел не используется).
Если нам нужно убрать нули в каждой без исключения ячейке листа, то перейдем в панели вкладок Файл -> Параметры -> Дополнительно (так как эти настройки относятся в целом к работе со всей книгой):
