Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Как оставить в ячейке только цифры или только текст?

Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 763шт.". Вам нужно из этого только 763 - чтобы можно было провести с этим некие математические действия. Если это только одна ячейка - проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?

  • Было доставлено кусков мыла 763шт.
  • Всего пришло 34
  • Тюбики - 54 доставлено
  • и т.д.

Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.


СПОСОБ 1: не используем макросы
можно применить формулу массива, вроде такой:
=ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99)));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99)))+1)
Три важных момента:

  1. Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
  2. в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА($1:$99) на СТРОКА($1:$200). Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
  3. формула не обработает корректно текст "Было доставлено кусков мыла 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

Может быть задача проще - необходимо извлечь односоставной текст, убрав цифры вначале и в конце строки, учитывая, что сам текст всегда следует после разделителя(например, тире):
12.08-АГСВ2
12.08-АГСВ1
01.03-ОВ2
12.03-КЖ6.1

Из этих данных надо получить только текст после тире(-) и отсечь цифры на конце:
АГСВ
АГСВ
ОВ
КЖ

Формула будет работать почти по тому же принципу, что и формула выше, но она проще:
=ПСТР(A1;ПОИСК("-";A1)+1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(--ПСТР(ПСТР(A1;ПОИСК("-";A1)+1;999);СТРОКА($1:$99);1));0)-1)
В данном случае мы при помощи ПОИСК("-";A1) ищем сначала позицию тире, далее при помощи ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(--ПСТР(ПСТР(A1;ПОИСК("-";A1)+1;999);СТРОКА($1:$99);1));0) находим именно в отсеченном тексте позицию первой цифры. Передаем эти значения в ПСТР, которая отбирает из этого текста все от первого тире(+1) до первого числа, идущего после текста.


СПОСОБ 2: используем макросы
Самый главный недостаток метода при помощи формулы, приведенной выше - из текста "Было доставлено кусков мыла 763шт., а заказывали 780" формула вернет не только числа, а и текст между первой и последней цифрой: 763шт., а заказывали 780.
Решить же проблему извлечения цифр даже из такого текста при помощи 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(Alt+F11) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Shift+F3), отыскав её в категории Определенные пользователем (User Defined Functions) и применять как обычную функцию на листе.
Для извлечения только чисел
=Extract_Number_from_Text(A1; 0)
или
=Extract_Number_from_Text(A1)
Для извлечения только текста
=Extract_Number_from_Text(A1; 1)

Подробнее про создание пользовательских функции и их применении можно почитать в статье Что такое функция пользователя(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 210 скачиваний)

Также см.:
Извлечение числа из текста
Что такое функция пользователя(UDF)?
Как получить адрес гиперссылки из ячейки
Оставить цифры или текст при помощи PowerQuery


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 83 комментария
  1. Сергей:

    подскажите пожалуйста,в столбце числа через дробь например: 0,65/0,58
    0,85/1,58 мне нужно чтобы было два столбца например:
    0,65 0,58
    0,85 1,58
    как это сделать??

    • Сергей, вообще с личными проблемами, не относящимися к статье, лучше в форум обращаться. Об этом честно написано.
      А так: Данные-Текст по столбцам.

  2. Эдуард:

    Код классный. Но у меня проблема:
    В ячейке есть информация: 25шт.+30бат. - функция выдает число 2530, а мне нужно, чтоб эти числа суммировались - т.е. должно быть 55;

    Помогите пожалуйста. Спасибо.

    • Вообще это не так просто. На вскидку функция должна выглядеть так:

      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 vTmpRes
          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
          vTmpRes = Evaluate(sInsertWord)
          If IsError(vTmpRes) Then
              Extract_Number_from_Text = sInsertWord
          Else
              Extract_Number_from_Text = vTmpRes
          End If
      End Function
      • Эдуард:

        Так тоже не получается - если в ячейке 25,5б.+25,5шт., то функция выдает 25,5+25,5, а надо 51. (

        • Эдуард:

          раньше пользовался вот такой функцией:

          Function specsum(s$) As Double
          Dim i&
          For i = 1 To Len(s)
          If Not IsNumeric(Mid(s, i, 1)) Then Mid(s, i, 1) = "+"
          Next
          specsum = Evaluate("=" & s & "+0")
          End Function

          но она не работает с дробными числами (н-д, 253,5 и т.д.)

          • Функция изначально не предназначена для подобных извратов. Суммирование внутри строки имеет достаточно много нюансов. Последняя попытка помочь Вам - дальше сами, т.к. переделывать все под персональные пожелания времени нет:

            sInsertWord = Replace(sInsertWord, ",", ".")

            эту строку добавляете после блока:

                        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
  3. Эдуард:

    Все работает! Спасибо большое. Извините за доставленные неудобства и потраченное время.

  4. Виктор:

    Ты гений очень помог)))!!!

    • Васек:

      Так то все круто, но оставляет "-" в обоих случаях
      Как это можно исправить

      • Ну вот ведь лень-то Ваша :-) Я же даже на Планете написал - прочитайте комментарии к статье. Вы решили не утруждать себя чтением, а написать свой. А на первой странице комментариев есть ответ, между тем.
        Но раз все же лень(и лень будет не только Вам, я полагаю) - добавил в конец статьи небольшое пояснение. Советую ознакомиться и попробовать применить к своей ситуации. Если уж и с пояснением не получится - тогда пишите еще один комментарий, приведу нужную строку. Но советую сначала вдуматься и помучиться лично - так лучше поймете принцип и при необходимости сможете уже без чужой помощи применить к другим ситуациям.

  5. Васек:

    Все разобрался, спасибо за наводку!!!

  6. Павел:

    Подскажите пожалуйста как исключить из удаления помимо цифр и символов (.,;:-) пробел?

  7. Иван:

    Добрый день, Дмитрий!
    Только начинаю изучать VBA, да и программирование в целом. Очень многое удается благодаря Вашей работе, за что Вам огромная благодарность!

    В ходе разбора предложенной пользовательской функции у меня появилась пара вопросов, может быть Вы сможете найти время и дать мне пояснения:
    1. Функции передается параметр method, который отвечает за то, что именно будет извлечено из строки: текст или число. Проверка этого параметра происходит в строке 13, которая находится в теле цикла FOR. Таким образом при выполнении кода при каждой итерации цикла будет происходить проверка параметра method. Если вынести проверку параметра method за пределы цикла, тогда в коде придется делать 2 цикла. Один если параметр 1 и другой если 0 или отсутствует. Код получается менее "привлекательным" для чтения, но чисто теоретически должен работать быстрее. Какой вариант Вы порекомендуете использовать? Вопрос относится не столько к данной конкретной задаче, а в принципе, когда встает вопрос о проверке параметров и циклах.
    2. В 25 строке Вы для проверки используете конструкцию:
    Not Mid(sWord, i - 1, 1) Like "*[0-9]*" Or Not Mid(sWord, i + 1, 1) Like "*[0-9]*"
    Если абстрагироваться от самих условий, то её можно преобразовать к конструкцию вида:
    Not A Or Not B, где А и B - переменные, принимающие значения "истина" и "ложь".
    Данная логическая конструкция равнозначна конструкции вида:
    Not (A and B), где А и B - переменные, принимающие значения "истина" и "ложь".
    Какой из двух вариантов по Вашему мнению предпочтительнее использовать в коде при разработке на VBA: "Not A Or Not B" или "Not (A and B)"? Или это личные предпочтения каждого разработчика :)

    Заранее благодарен за уделенное время!

  8. Данил:

    Спасибо за код, но у меня не появляется категория User defined, хотя я включил макросы и сохранил в .xlsm (у меня Excel 2007). Почему это может быть?

  9. Roman:

    Золотая голова у вас )) спасибо помогло

  10. Игорь:

    Здравствуйте!
    Как добавить эту формулу на постоянной основе, а не только в открытую книгу?
    При закрытии книги VBA не сохраняется

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти