Vba excel удалить из строки все цифры

Обновлено: 07.07.2024

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

в ASAP Util есть такая функция, но работает только с английским языком.

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

в ASAP Util есть такая функция, но работает только с английским языком.

в ASAP Util есть такая функция, но работает только с английским языком.

Подскажите пожалуйста. Автор - qpp
Дата добавления - 04.07.2012 в 12:06

Excel 2003 EN, 2013 EN

И ещё Автор - Формуляр
Дата добавления - 04.07.2012 в 12:17

Для А1
Формула массива (вводить одновременным нажатием Контрл, Шифт, Ентер)
[vba] [/vba]
Можно блок ДВССЫЛ("A1:A"&ДЛСТР(A1)) заменить на, допустим, D$1:D$1000 или вообще $1:$1000 Для А1
Формула массива (вводить одновременным нажатием Контрл, Шифт, Ентер)
[vba] [/vba]
Можно блок ДВССЫЛ("A1:A"&ДЛСТР(A1)) заменить на, допустим, D$1:D$1000 или вообще $1:$1000 _Boroda_ [/vba]
Можно блок ДВССЫЛ("A1:A"&ДЛСТР(A1)) заменить на, допустим, D$1:D$1000 или вообще $1:$1000 Автор - _Boroda_
Дата добавления - 04.07.2012 в 12:30

Интересное решение, попробую разобраться на досуге.

Но меня все таки интересует момент, как можно убрать из ячейки все буквы.

формула не работает.

Интересное решение, попробую разобраться на досуге.

Но меня все таки интересует момент, как можно убрать из ячейки все буквы.

формула не работает.

Но меня все таки интересует момент, как можно убрать из ячейки все буквы.

формула не работает.

Автор - Гость
Дата добавления - 04.07.2012 в 12:43 прошу прощение, оставил запись как гость. не до конца еще освоил форум. прошу прощение, оставил запись как гость. не до конца еще освоил форум. qpp

93937059290058298022158901014112251 - солить будем?

93937059290058298022158901014112251 - солить будем? RAN

Быть или не быть, вот в чем загвоздка!

93937059290058298022158901014112251 - солить будем? Автор - RAN
Дата добавления - 04.07.2012 в 12:55

прошу прощения, принял к сведению и буду выкладывать файл впредь для примера.

в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,

я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками.

прошу прощения, принял к сведению и буду выкладывать файл впредь для примера.

в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,

я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками. qpp

в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,

я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками. Автор - qpp
Дата добавления - 04.07.2012 в 13:00

Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.

Способ первый:
Использовать встроенное средство Excel - фильтр. Сначала его необходимо "установить" на листе:

• Выделяем таблицу с данными, включая заголовки. Если их нет - то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
• устанавливаем фильтр:
  • для Excel 2003 : Данные-Фильтр-Автофильтр
  • для Excel 2007-2010 : вкладка Данные (Data) -Фильтр (Filter)(или вкладка Главная (Home) -Сортировка и фильтр (Sort&Filter) -Фильтр (Filter) )

  Теперь выбираем условие для фильтра:

  • в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие("равно", "содержит", "начинается с" и т.д.), а напротив значение в соответствии с условием.
  • Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме

  После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк - все?! Как избежать.

  
  Способ второй:
  применить код VBA, который потребует только указания значения, которое необходимо найти в строке и номер столбца, в котором искать значение.

  Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
  Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
  If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
  вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
  Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:

  Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.

  УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
  Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
  Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.

  Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано "Привет, как дела?", а в списке есть слово "привет" - надо удалить, т.к. есть слово "привет"), то надо строку:

  If CStr(arr(li, 1)) = sSubStr Then

  заменить на такую:

  If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

  УДАЛЕНИЕ ИЗ ЛИСТА СТРОК, КОТОРЫХ НЕТ В СПИСКЕ ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
  Т.к. в последнее время стало поступать все больше и больше вопросов как не удалять значения по списку, а наоборот - оставить в таблице только те значения, которые перечислены в списке - решил дополнить статью и таким кодом.
  Значения, которые необходимо оставить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - после работы кода на листе будут оставлены только те строки, в которых присутствует хоть одно из перечисленных в списке значений. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
  В отличие от приведенных выше кодов, данный код ориентирован на то, что значения в списке указаны не полностью. Т.е. если необходимо оставить только те ячейки, в которых встречается слово "активы", то в списке надо указать только это слово. В этом случае если в ячейке будет записана фраза "Нематериальные активы" или "Активы сторонние" - эти ячейки не будут удалены, т.к. в них встречается слово "активы". Регистр букв при этом неважен.

  Чтобы код выше сравнивал значения таблицы со значениями списка по точному совпадению слов, а не по частичному, то надо строку:

  If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

  заменить на такую:

  If CStr(arr(li, 1)) = sSubStr Then

  Для всех приведенных кодов можно строки не удалять, а скрывать. Для этого надо строку:

  If Not rr Is Nothing Then rr.EntireRow.Delete

  заменить на такую:

  If Not rr Is Nothing Then rr.EntireRow.Hidden = True

  По умолчанию все коды начинают просмотр строк с первой по последнюю заполненную на листе. И если необходимо удалять строки не с первой или не по последнюю, то надо внести корректировки в эту строку:

  For li = 1 To lLastRow 'цикл с первой строки до конца

  1 - это первая строка; lLastRow - определяется автоматически кодом и равна номеру последней заполненной строки на листе. Если надо начать удалять строки только с 7-ой строки(например, в первых 6-ти шапка), то код будет выглядеть так:

  Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:

  • лишние пробелы перед, после или между словами (для красоты!)
  • ненужные символы ("г." перед названием города)
  • невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или "кривой" выгрузки из 1С, переносы строк, табуляция)
  • апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)

  Давайте рассмотрим способы избавления от такого "мусора".

  Замена

  "Старый, но не устаревший" трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.

  Изначально это окно было задумано для оптовой замены одного текста на другой по принципу "найди Маша – замени на Петя", но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим "г." (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы "г." перед названиями городов:

  
  

  Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!

  Удаление пробелов

  Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

  Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:

  
  

  Удаление непечатаемых символов

  В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их "зачистить".

  Вариантов решения два:

  • Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
  • Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

  Функция ПОДСТАВИТЬ

  Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:

  • Текст в котором производим замену
  • Старый текст – тот, который заменяем
  • Новый текст – тот, на который заменяем

  С ее помощью можно легко избавиться от ошибок (замена "а" на "о"), лишних пробелов (замена их на пустую строку ""), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):

  
  

  Удаление апострофов в начале ячеек

  Апостроф (') в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:

  Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

  Английские буквы вместо русских

  Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские ("це" вместо русской "эс", "игрек" вместо русской "у" и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.

  Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:

  Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
  

  В этой статье говорится об удалении всех чисел из ячеек, содержащих текст и числа, и о сохранении только текста в этих ячейках, как показано на скриншоте ниже.

  
  

  Удалите числа из ячеек, содержащих текст и числа, с помощью пользовательской функции

  Следующая определяемая пользователем функция может помочь вам удалить все числа из ячеек и оставить только текст. Пожалуйста, сделайте следующее.

  1. нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.

  2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль. Затем скопируйте и вставьте приведенный ниже код VBA в окно кода. Смотрите скриншот:

  Код VBA: удалить числа из ячеек, содержащих тексты и числа

  
  

  3. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

  4. Выберите пустую ячейку, введите формулу. = OnlyRemoveNumbers (A2) в панель формул, а затем нажмите Enter ключ для получения результата. Продолжая выбирать ячейку результата, перетащите ее маркер заполнения вниз, чтобы получить все результаты. Смотрите скриншот:

  
  

  Быстро удаляйте числа из ячеек, содержащих тексты и числа, с помощью Kutools for Excel

  Вы можете использовать Удалить символы полезности Kutools for Excel чтобы легко удалить все числа из выбранных ячеек и оставить только текст.

  Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

  1. Выберите ячейки, в которых нужно удалить числа и оставить только текст, затем нажмите Kutools > Текст > Удалить символы. Смотрите скриншот:

  
  

  2. в Удалить символы диалоговое окно, только отметьте Числовой коробка в Удалить символы раздел, а затем щелкните OK кнопку.

  
  

  Затем все числа удаляются из выбранного диапазона, и остаются только тексты.

  Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

  Читайте также:

    
  • Как переносить текст в ячейке excel на ipad
    
  • Visual studio version selector что это
    
  • Что значит обходить vpn в поисковых системах по умолчанию в опере
    
  • Программа для сканирования для canon lide 220
    
  • Winamp что это за программа