Как оставить в ячейке только цифры или только текст?
Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 763шт.". Вам нужно из этого только 763 - чтобы можно было провести с этим некие математические действия. Если это только одна ячейка - проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?
- Было доставлено кусков мыла 763шт.
- Всего пришло 34
- Тюбики - 54 доставлено
- и т.д.
Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.
можно применить формулу массива, вроде такой:
Три важных момента:
- Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
- в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить
СТРОКА( на$1:$99 )СТРОКА( . Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго$1:$200 ) - формула не обработает корректно текст "
Было доставлено кусков мыла 763шт., а заказывали 780 " и ему подобный, где числа раскиданы по тексту.
Теперь коротко разберем формулу на примере фразы:
- в
A1 сам текст, из которого необходимо извлечь числа:Было доставлено кусков мыла 763шт., а заказывали 780 - блок:
МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА($1:$99 );1));СТРОКА($1:$99 )))
вычисляет позицию первой цифры в ячейке - 29 - блок:
ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА($1:$99 );1));СТРОКА($1:$99 ))
вычисляет позицию последней цифры в ячейке - 31 - в результате получается: =ПСТР(
A1 ;29;31-29+1)
функция ПСТР извлекает из текста, указанного первым аргументом( ) текст, начиная с указанной позиции(29) с количеством символов, указанным третьим аргументом(31-29+1)A1 - И в итоге:
=ПСТР( A1 ;29;31-29+1)
=> =ПСТР(A1 ;29;2+1)
=> =ПСТР(A1 ;29;3)
=> 763
Может быть задача проще - необходимо извлечь односоставной текст, убрав цифры вначале и в конце строки, учитывая, что сам текст всегда следует после разделителя(например, тире):
12.08-АГСВ1
01.03-ОВ2
12.03-КЖ6.1
Из этих данных надо получить только текст после тире(-) и отсечь цифры на конце:
АГСВ
ОВ
КЖ
Формула будет работать почти по тому же принципу, что и формула выше, но она проще:
В данном случае мы при помощи
Самый главный недостаток метода при помощи формулы, приведенной выше - из текста "
Решить же проблему извлечения цифр даже из такого текста при помощи VBA куда проще и гибче. Плюс можно не только цифры извлекать, но и наоборот - цифры удалить, а извлечь только текст. Ниже приведен код пользовательской функции, которая поможет извлечь из строки только числа либо только текст. Иными словами, результатом функции будет либо только текст, либо только числа.
Function Extract_Number_from_Text(sWord As String, Optional Metod As Integer) 'sWord = ссылка на ячейку или непосредственно текст 'Metod = 0 – числа 'Metod = 1 – текст Dim sSymbol As String, sInsertWord As String Dim i As Integer If sWord = "" Then Extract_Number_from_Text = "Нет данных!": Exit Function sInsertWord = "" sSymbol = "" For i = 1 To Len(sWord) sSymbol = Mid(sWord, i, 1) If Metod = 1 Then If Not LCase(sSymbol) Like "*[0-9]*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i - 1, 1) Like "*[0-9]*" And Mid(sWord, i + 1, 1) Like "*[0-9]*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If Else If LCase(sSymbol) Like "*[0-9.,;:-]*" Then If LCase(sSymbol) Like "*[.,]*" And i > 1 Then If Not Mid(sWord, i - 1, 1) Like "*[0-9]*" Or Not Mid(sWord, i + 1, 1) Like "*[0-9]*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Extract_Number_from_Text = sInsertWord End Function |
Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(
или
Подробнее про создание пользовательских функции и их применении можно почитать в статье Что такое функция пользователя(UDF)?
Помимо функции пользователя решил выложить и вариант с использованием диалогового окна:
Выбрать ячейку или диапазон с текстом(Лист1!$A$2:$A$10) - здесь указывается диапазон с исходными значениями, из которого необходимо оставить только числа или только текст.
Выберите ячейку для вывода данных(Лист1!$A$2) - указывается одна ячейка, с которой начать вывод преобразованных значений. В качестве этой ячейки можно выбрать первую ячейку диапазона с текстом(исходного) если необходимо произвести изменения сразу в этих же ячейках(как на рисунке). Осторожнее с таким указанием, т.к. результат работы кода может быть не совсем таким, какой вы ожидали, а вернуть прежние данные уже не получится - если только не закрыть файл без сохранения изменений.
Оставить только цифры, Оставить только текст- думаю не надо пояснять. Здесь выбираем, что оставить в качестве результата.
В коде есть строка:
If LCase(sSymbol) Like "*[0-9.,;:-]*" Then |
Данная строка отвечает за текстовые символы, которые могут встречаться внутри чисел и которые надо оставить(не удалять наравне с другими не числовыми символами). Следовательно, если какие-то из данных символов не нужны в конечном тексте - их надо просто удалить. Например, чтобы оставались исключительно числа(без запятых и пр.):
If LCase(sSymbol) Like "*[0-9]*" Then |
если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):
If LCase(sSymbol) Like "*[0-9.]*" Then |
и т.д.
Число из текста и наоборот.xls (99,0 KiB, 18 241 скачиваний)
Также см.:
Извлечение числа из текста
Что такое функция пользователя(UDF)?
Как получить адрес гиперссылки из ячейки
Оставить цифры или текст при помощи PowerQuery
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Вставил код, не работает выдает ошибку 1004
Заменил FormulaR1C1 на FormulaLocal и все заработало.
Огромное вам спасибо добрый человек.
Спасибо огромное!!!!!
Спасибо огромное!!! Супер Супер!!!
Спасибо за отличный код, сам только начал учить вба и нужно было написать нечто подобное, погуглил, как лучше сделать - а ведь уже почти все написано!)
Правда возникла одна маленькая проблема - эксель ругается, что используется циклический код, и обнуляет значение ячейки(что в моем документе с небольшими изменениями кода, что в вашем, где ничего не трогал)
Судя по комментариям проблема только у меня, но мало ли... Вы же создатель, вдруг есть предположения, отчего такое может быть)
Х, Excel ругается потому что Вы наверняка прописываете функцию Extract_Number_from_Text, ссылаясь в ней на ячейку, в которой в свою очередь тоже формула, ссылающаяся на ячейку с функцией Extract_Number_from_Text. Т.е. получается, что значение функции Extract_Number_from_Text напрямую зависит от значения в ячейке с текстом(что логично), а та в свою очередь зависит от ячейки с Extract_Number_from_Text. Т.е. замкнутый круг. Вот Вам и цикличность. Либо замените формулы на значения, либо создайте доп.столбец, в который пропишите значения ячеек, из которых надо извлечь текст или цифры.
Ахаха)) Нельзя нубам давать ноутбук)))
Огромное спасибо, не только указали на такую глупую ошибку, но еще и расписали))
Забыл про "рекурсию экселя"))
Еще раз большое спасибо))
Кстати, не могу не отметить то, насколько оперативно вы отвечаете всем на комментарии, это огромный плюс)
Примите мою признательность и уважение)
Три дня сидел мучился. С 1-С вгрузил карточку счета. Мне нужны были только содержания проводок, а там в одной ячейке помимо содержания еще и пишутся номера проводок. Че тока не перепробовал комбинируя ТИП(), ЛЕВСИМВ(), ПОИСК() и т.д.. В натуре заманался, думал уже в ручную удалить номера.
СПОСИБО ОГРОМНОЕ.
И надо же, прям с первого раза на нужный сайт попасть.
Этот макрос не убирает двоеточие!!!
Сергей, что за крики? Да, не убирает. И точку-с-запятой не убирает, и запятую и тире. И это входит в функционал. Советую внимательно всмотреться в код функции. Есть там такой кусочек: "*[0-9.,;:-]*"
Удалите оттуда все символы, которые Вам не нужны.
Спасибо, огромное!!! Просто супер!!! Сколько времени, сил зря тратилось!!!