Vba excel удалить из строки все цифры
Как удалить числа из текстовых строк в Excel?
Например, в текстовой строке смешано много чисел, и вы хотите удалить эти числа из текстовой строки. Как это сделать? В этой статье будут представлены несколько хитрых методов для простого удаления чисел из ячеек текстовой строки в Excel.
Удаление чисел из текстовых строк с помощью функции, определяемой пользователем
Этот метод представит определяемую пользователем функцию для простого удаления чисел из текстовых строк в Excel. Пожалуйста, сделайте следующее:
1, нажмите другой + F11 одновременно нажмите клавиши, чтобы открыть окно Microsoft Basic для приложений.
2. Нажмите Вставить > Модули, а затем вставьте следующий код VBA в открывающееся окно модуля.
VBA: удаление чисел из текстовых строк в Excel
3. Сохраните Использовать определенную функцию. Выберите пустую ячейку, в которую вы вернете текстовую строку без чисел, введите формулу = RemoveNumbers (A2) (A2 - это ячейка, из которой вы удалите числа), а затем перетащите маркер заполнения вниз до нужного диапазона.
Удалите числа из текстовых строк с помощью Kutools for Excel
Этот метод представит Kutools for Excel's Удалить символы утилита для быстрого удаления всех чисел из ячеек текстовой строки в Excel. Пожалуйста, сделайте следующее:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выделите ячейки текстовой строки, из которых нужно удалить числа, и нажмите Кутулс > Текст > Удалить символы.
2. В открывшемся диалоговом окне «Удалить символы» установите флажок Числовой и нажмите Ok кнопку.
Затем вы увидите, что все числа сразу удаляются из выбранных ячеек текстовой строки. См. Снимок экрана:
Внимание: Иногда вам может потребоваться удалить числа и другие специальные знаки из ячеек текстовой строки и оставить только буквы. Вы можете сделать это, проверив Не альфа и нажмите Ok в диалоговом окне "Удалить символы". Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: удаление чисел из текстовых строк в Excel
Быстро извлекайте числа из текстовых строк с помощью функции Kutools в Excel
Kutools for Excel's Kutools Functions Утилита предоставляет ВЫДЕРЖКИ функция, которая поможет вам быстро и легко извлекать числа из смешанных текстовых строк. Полнофункциональная бесплатная 30-дневная пробная версия!
Добрый день! Помогите упростить мне жизнь!
Есть данные в ячейки, содержат: цифры, буквы, точку..нужно убрать цифры чтобы остались только буквы и точка.
Добрый день! Помогите упростить мне жизнь!
Есть данные в ячейки, содержат: цифры, буквы, точку..нужно убрать цифры чтобы остались только буквы и точка. Taliya
Это не в ячейке содержится. Это формат ячейки такой 0,000" м.";[Красный]-0,000" м.". А в ячейке просто число, без букв.
Это не в ячейке содержится. Это формат ячейки такой 0,000" м.";[Красный]-0,000" м.". А в ячейке просто число, без букв. _Boroda_
Function buk(adr As Range)
f_ = adr.NumberFormat
t_ = Mid(f_, WorksheetFunction.Search("""", f_) + 2, WorksheetFunction.Search(""";", f_) - WorksheetFunction.Search("""", f_) - 2)
End Function
Function buk(adr As Range)
f_ = adr.NumberFormat
t_ = Mid(f_, WorksheetFunction.Search("""", f_) + 2, WorksheetFunction.Search(""";", f_) - WorksheetFunction.Search("""", f_) - 2)
End Function
Function buk(adr As Range)
f_ = adr.NumberFormat
t_ = Mid(f_, WorksheetFunction.Search("""", f_) + 2, WorksheetFunction.Search(""";", f_) - WorksheetFunction.Search("""", f_) - 2)
End Function
[/vba]
Смотрите в файле.
Макросы должны быть разрешены Автор - _Boroda_
Дата добавления - 18.07.2014 в 12:53
Не пойму что не так делаю..все как Вы написали
ПыСы а в графе выходи или "ЗНАЧ" или "0"
Не пойму что не так делаю..все как Вы написали
ПыСы а в графе выходи или "ЗНАЧ" или "0" Taliya
ПыСы а в графе выходи или "ЗНАЧ" или "0" Автор - Taliya
Дата добавления - 18.07.2014 в 14:00
СПАСИБО! Теперь разобралась как мне проще делать..с функцией так и не подружилась..но если графу с форматом ячеек "0,000" м.";[Красный]-0,000" м." скопировать в ворд а потом обратно в ексель то формат ячеек меняется на общий и появляются уже шт. м. и т.д. А потом уже вставляю Вашу формулу!
Большое спасибо..сэкономили час жизни еженедельно.
СПАСИБО! Теперь разобралась как мне проще делать..с функцией так и не подружилась..но если графу с форматом ячеек "0,000" м.";[Красный]-0,000" м." скопировать в ворд а потом обратно в ексель то формат ячеек меняется на общий и появляются уже шт. м. и т.д. А потом уже вставляю Вашу формулу!
Большое спасибо..сэкономили час жизни еженедельно. Taliya
Добрый день. Возник вопрос, как можно удалить все текстовые значение (рус и анг) из ячейки, и оставить только цифры.
в ASAP Util есть такая функция, но работает только с английским языком.
Добрый день. Возник вопрос, как можно удалить все текстовые значение (рус и анг) из ячейки, и оставить только цифры.
в ASAP Util есть такая функция, но работает только с английским языком.
в ASAP Util есть такая функция, но работает только с английским языком.
Подскажите пожалуйста. Автор - qpp
Дата добавления - 04.07.2012 в 12:06
Excel 2003 EN, 2013 EN
И ещё Автор - Формуляр
Дата добавления - 04.07.2012 в 12:17
[/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
Интересное решение, попробую разобраться на досуге.
Но меня все таки интересует момент, как можно убрать из ячейки все буквы.
тк для этого
МАТЕРИАЛ ПЕРЕВЯЗОЧНЫЙ АДГЕЗИВНЫЙ, ИМЕЮЩИЙ ЛИПКУЮ ПОВЕРХНОСТЬ, ДЛЯ ИСПОЛЬЗОВАНИЯ В МЕДИЦИНЕ, В КАРТ. КОРОБКАХ, КОД ОКП 939370:ГИПОАЛЛЕРГЕННЫЙ ФИКСИРУЮЩИЙ ПЛАСТЫРЬ НА ПЛАСТ. КАТУШКАХ: "OMNIPOR" РАЗМ.5СМХ9.2М, АРТ. - 9005829 - 80 УПАК. (ПО 2 ШТ) В 2 КАРТ.КОР. САМОКЛЕЯЩАЯСЯ ПОВЯЗКА ДЛЯ УХОДА ПОСЛЕОПЕРАЦИОННЫМИ РАНАМИ СО СПЕЦИАЛЬНОЙ НЕ ПРИКЛЕИВАЮЩЕЙСЯ К РАНЕ : "COSMOPOR ADVANCE|", РАЗМ.15X8CM, АРТ. - 9010141 - 12 УПАК. (ПО 25 ШТ), В 1 КАРТ.КОР.
формула не работает.
Интересное решение, попробую разобраться на досуге.
Но меня все таки интересует момент, как можно убрать из ячейки все буквы.
тк для этого
МАТЕРИАЛ ПЕРЕВЯЗОЧНЫЙ АДГЕЗИВНЫЙ, ИМЕЮЩИЙ ЛИПКУЮ ПОВЕРХНОСТЬ, ДЛЯ ИСПОЛЬЗОВАНИЯ В МЕДИЦИНЕ, В КАРТ. КОРОБКАХ, КОД ОКП 939370:ГИПОАЛЛЕРГЕННЫЙ ФИКСИРУЮЩИЙ ПЛАСТЫРЬ НА ПЛАСТ. КАТУШКАХ: "OMNIPOR" РАЗМ.5СМХ9.2М, АРТ. - 9005829 - 80 УПАК. (ПО 2 ШТ) В 2 КАРТ.КОР. САМОКЛЕЯЩАЯСЯ ПОВЯЗКА ДЛЯ УХОДА ПОСЛЕОПЕРАЦИОННЫМИ РАНАМИ СО СПЕЦИАЛЬНОЙ НЕ ПРИКЛЕИВАЮЩЕЙСЯ К РАНЕ : "COSMOPOR ADVANCE|", РАЗМ.15X8CM, АРТ. - 9010141 - 12 УПАК. (ПО 25 ШТ), В 1 КАРТ.КОР.
формула не работает.
Но меня все таки интересует момент, как можно убрать из ячейки все буквы.
тк для этого
МАТЕРИАЛ ПЕРЕВЯЗОЧНЫЙ АДГЕЗИВНЫЙ, ИМЕЮЩИЙ ЛИПКУЮ ПОВЕРХНОСТЬ, ДЛЯ ИСПОЛЬЗОВАНИЯ В МЕДИЦИНЕ, В КАРТ. КОРОБКАХ, КОД ОКП 939370:ГИПОАЛЛЕРГЕННЫЙ ФИКСИРУЮЩИЙ ПЛАСТЫРЬ НА ПЛАСТ. КАТУШКАХ: "OMNIPOR" РАЗМ.5СМХ9.2М, АРТ. - 9005829 - 80 УПАК. (ПО 2 ШТ) В 2 КАРТ.КОР. САМОКЛЕЯЩАЯСЯ ПОВЯЗКА ДЛЯ УХОДА ПОСЛЕОПЕРАЦИОННЫМИ РАНАМИ СО СПЕЦИАЛЬНОЙ НЕ ПРИКЛЕИВАЮЩЕЙСЯ К РАНЕ : "COSMOPOR ADVANCE|", РАЗМ.15X8CM, АРТ. - 9010141 - 12 УПАК. (ПО 25 ШТ), В 1 КАРТ.КОР.
формула не работает.
Function ИЗВЛЕЧ_ЦИФР(ЯЧЕЙКА As Range) As String
'---------------------------------------------------------------------------------------
' Purpose : Извлекает цифры из ячейки
'---------------------------------------------------------------------------------------
Dim LenStr As Long
For LenStr = 1 To Len(ЯЧЕЙКА)
Select Case Asc(Mid(ЯЧЕЙКА, LenStr, 1))
Case 48 To 57
ИЗВЛЕЧ_ЦИФР = ИЗВЛЕЧ_ЦИФР & Mid(ЯЧЕЙКА, LenStr, 1)
End Select
Next
End Function
93937059290058298022158901014112251 - солить будем?
Function ИЗВЛЕЧ_ЦИФР(ЯЧЕЙКА As Range) As String
'---------------------------------------------------------------------------------------
' Purpose : Извлекает цифры из ячейки
'---------------------------------------------------------------------------------------
Dim LenStr As Long
For LenStr = 1 To Len(ЯЧЕЙКА)
Select Case Asc(Mid(ЯЧЕЙКА, LenStr, 1))
Case 48 To 57
ИЗВЛЕЧ_ЦИФР = ИЗВЛЕЧ_ЦИФР & Mid(ЯЧЕЙКА, LenStr, 1)
End Select
Next
End Function
93937059290058298022158901014112251 - солить будем? RAN
Быть или не быть, вот в чем загвоздка!
Function ИЗВЛЕЧ_ЦИФР(ЯЧЕЙКА As Range) As String
'---------------------------------------------------------------------------------------
' Purpose : Извлекает цифры из ячейки
'---------------------------------------------------------------------------------------
Dim LenStr As Long
For LenStr = 1 To Len(ЯЧЕЙКА)
Select Case Asc(Mid(ЯЧЕЙКА, LenStr, 1))
Case 48 To 57
ИЗВЛЕЧ_ЦИФР = ИЗВЛЕЧ_ЦИФР & Mid(ЯЧЕЙКА, LenStr, 1)
End Select
Next
End Function
93937059290058298022158901014112251 - солить будем? Автор - RAN
Дата добавления - 04.07.2012 в 12:55
прошу прощения, принял к сведению и буду выкладывать файл впредь для примера.
в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,
я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками.
прошу прощения, принял к сведению и буду выкладывать файл впредь для примера.
в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,
я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками. qpp
в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,
я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками. Автор - qpp
Дата добавления - 04.07.2012 в 13:00
Зачастую текст, который достается нам для работы в ячейках листа 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 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
В этой статье говорится об удалении всех чисел из ячеек, содержащих текст и числа, и о сохранении только текста в этих ячейках, как показано на скриншоте ниже.
Quickly remove all numbers from cells which containing texts and numbers:
You can quickly remove all numbers from specified cells which containing texts and numbers with the Remove Characters utility of Kutools for Excel as below screenshot shown.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Удалите числа из ячеек, содержащих текст и числа, с помощью пользовательской функции
Следующая определяемая пользователем функция может помочь вам удалить все числа из ячеек и оставить только текст. Пожалуйста, сделайте следующее.
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. Выберите ячейки, в которых нужно удалить числа и оставить только текст, затем нажмите Кутулс > Текст > Удалить символы. Смотрите скриншот:
2. в Удалить символы диалоговое окно, только отметьте Числовой коробка в Удалить символы раздел, а затем щелкните OK кнопку.
Затем все числа удаляются из выбранного диапазона, и остаются только тексты.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: