Как в эксель сделать текст под текстом

  • Закрыть ... [X]

    Хитрости »

    как в эксель сделать текст под текстом 1 Май 2011       Дмитрий       197445 просмотров

     

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

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

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

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

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

    Теперь коротко разберем формулу на примере фразы: Было доставлено кусков мыла 763шт.

    • в A1 сам текст, из которого необходимо извлечь числа: Было доставлено кусков мыла 763шт., а заказывали 780
    • блок: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(A1;СТРОКА(:);1));СТРОКА(:)))
      вычисляет позицию первой цифры в ячейке - 29
    • блок: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(A1;СТРОКА(:);1));СТРОКА(:))
      вычисляет позицию последней цифры в ячейке - 31
    • в результате получается: =ПСТР(A1;29;31-29+1)
      функция ПСТР извлекает из текста, указанного первым аргументом(A1) текст, начиная с указанной позиции(29) с количеством символов, указанным третьим аргументом(31-29+1)
    • И в итоге:
      =ПСТР(A1;29;31-29+1)
      => =ПСТР(A1;29;2+1)
      => =ПСТР(A1;29;3)
      => 763

    СПОСОБ 2: используем макросы
    Самый главный недостаток метода при помощи формулы, приведенной выше - из текста "Было доставлено кусков мыла 763шт., а заказывали 780" формула вернет не только числа, а и текст между первой и последней цифрой: 763шт., а заказывали 780.
    Решить же проблему извлечения цифр даже из такого текста при помощи VBA куда проще и гибче. Плюс можно не только цифры извлекать, но и наоборот - цифры удалить, а извлечь только текст. Ниже приведен код пользовательской функции, которая поможет извлечь из строки только числа либо только текст. Иными словами, результатом функции будет либо только текст, либо только числа.

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    27

    28

    29

    30

    31

    32

    33

    34

    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

    Данный код необходимо поместить в стандартный модуль книги. После этого в мастере функций в категории Определенные пользователем (User Defined) будет доступна функция Extract_Number_from_Text, которую можно будет применять как обычную функцию на листе.
    Для извлечения только чисел
    =Extract_Number_from_Text(A1; 0)
    или
    =Extract_Number_from_Text(A1)
    Для извлечения только текста
    =Extract_Number_from_Text(A1; 1)

    Подробнее про создание пользовательских функции и их применении можно почитать в статье Что такое функция пользователя(UDF)?

    Помимо функции пользователя решил выложить и вариант с использованием диалогового окна:

    Выбрать ячейку или диапазон с текстом(Лист1!$A:$A) - здесь указывается диапазон с исходными значениями, из которого необходимо оставить только числа или только текст.

    Выберите ячейку для вывода данных(Лист1!$A) - указывается одна ячейка, с которой начать вывод преобразованных значений. В качестве этой ячейки можно выбрать первую ячейку диапазона с текстом(исходного) если необходимо произвести изменения сразу в этих же ячейках(как на рисунке). Осторожнее с таким указанием, т.к. результат работы кода может быть не совсем таким, какой вы ожидали, а вернуть прежние данные уже не получится - если только не закрыть файл без сохранения изменений.

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

    Небольшое дополнение к использованию кода
    В коде есть строка:

    Данная строка отвечает за текстовые символы, которые могут встречаться внутри чисел и которые надо оставить(не удалять наравне с другими не числовыми символами). Следовательно, если какие-то из данных символов не нужны в конечном тексте - их надо просто удалить. Например, чтобы оставались исключительно числа(без запятых и пр.):

    если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):

    и т.д.
    Скачать пример:

      Tips_Macro_Number_From_Text.xls (80,0 KiB, 9 388 скачиваний)

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

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

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

    Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Печать Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки


    Поделись с друзьями



    Рекомендуем посмотреть ещё:



    Функция СЧЁТЕСЛИ : подсчет количества ячеек по определенному критерию Установка инжектора 2110 своими руками

    Как в эксель сделать текст под текстом Как в эксель сделать текст под текстом Как в эксель сделать текст под текстом Как в эксель сделать текст под текстом Как в эксель сделать текст под текстом Как в эксель сделать текст под текстом Как в эксель сделать текст под текстом

    ШОКИРУЮЩИЕ НОВОСТИ