Как выделить слово в экселе
Как выделить произвольное слово в ячейке в Excel?
Узнаем как можно выделить произвольное слово в ячейке (первое, последнее или n-ое по порядку) в Excel в двух вариациях: в виде сложной формулы и пользовательской функции.
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Задача выделения конкретных (по порядку) слов из предложения имеет достаточно широкое применение при структурировании и анализе данных, например, выделение части артикула из кода товара, имени или фамилии из полной записи ФИО и т.д.
Если данные представлены в однородном виде (к примеру, нужно выделить первое/второе/…/последнее слово в каждой ячейке столбца, где все данные содержат одинаковое количество слов), то можно воспользоваться инструментом Текст по столбцам и получить разделенные слова в соседних столбцах.
Не будем подробно останавливаться на этом способе, так как применять его не всегда можно (данные в этом случае имеют специфичную форму) и удобно (при разделении по столбцам добавляются новые столбцы, что изменяет структуру документа).
Изучим более универсальный способ, который будет работать для любого типа данных.
Однако перед решением задачи сначала найдем ответ на достаточно простой и очевидный вопрос.
Что в предложениях отделяет одно слово от другого?
В большинстве современных языков для разделения слов используется символ пробела, поэтому в первую очередь алгоритм будет строиться на поиске пробела, а далее мы уже будем выделять нужное слово.
Выделение первого слова в ячейке в Excel
C помощью функции НАЙТИ найдем позицию первого пробела, а затем воспользуемся функцией ЛЕВСИМВ, которая возвращает указанное количество символов с начала текста (как раз до первого найденного пробела):
Вместо ЛЕВСИМВ можно воспользоваться функцией ПСТР, которая более универсальна и позволяет вернуть заданное количество символов начиная с указанной позиции, а не только с начала строки (в данном случае с 1, так как мы выделяем именно первое слово):
Недостаток такого способа в том, что если в ячейке содержится ровно 1 слово, то она вернет значение ошибки, так как пробелов в нем нет:
Поэтому дополнительно добавим проверку на ошибку — в случае если во фразе нет пробелов, то возвращаем исходный текст:
Выделение последнего слова в ячейке в Excel
С поиском последнего слова все несколько сложнее — мы не знаем сколько их во фразе, поэтому для начала определим количество пробелов в предложении. В этом нам помогут функции ПОДСТАВИТЬ с помощью которой мы сначала удалим все пробелы во фразе, и ДЛСТР, которая покажет символьную длину фразы.
Таким образом, разность длины исходной фразы и фразы без пробелов даст нам итоговое количество пробелов в ячейке.
Далее вновь воспользуемся функцией ПОДСТАВИТЬ и заменим последний пробел (его порядковый номер мы уже знаем) на специальный символ, который точно не встретится в предложении (обычно используются символы #, ^ и т.п.).
После чего функцией ПРАВСИМВ (возвращает указанное количество символов с конца текста) выделяем все символы от специального символа (в данном случае функцией НАЙТИ ищем позицию символа #) до конца предложения:
Как и в предыдущем варианте, если во фразе всего 1 слово, то формула выдаст ошибку, поэтому для корректной работы здесь также нужно будет добавить аналогичную проверку, что и в примере выше.
Теперь перейдем к самому сложному варианту — выделению произвольного по порядку слова.
Выделение n-го слова в ячейке в Excel
Воспользуемся следующим приемом — продублируем все пробелы между словами очень большое количество раз (заведомо больше длины фразы, например, 1 000 раз), после чего функцией ПСТР выделяем 1 000 символов умноженные на n (номер нужного слова), а затем функцией ПРАВСИМВ возвращаем только последнюю 1 000 символов (в котором как раз содержится искомое слово).
Далее удаляем все лишние пробелы и получаем:
Иначе говоря, алгоритм следующий — мы разбиваем предложение на блоки, состоящие из одного слова предложения и большого количества пробелов, а затем вытаскиваем нужный блок и удаляем оттуда лишние пробелы.
Если же мы хотим достать другое слово, то вместо *4 в примере выше, нужно просто поставить другой номер (1 для первого слова, 2 для второго и т.д.), а если указанный номер больше количества слов в предложении, то в качестве ответа будет получено последнее слово.
Также преимуществом данной формулы является ее универсальность, потому что ее можно использовать для любого по порядку слова — от первого до последнего, к тому же не нужно дополнительно делать проверку на длину фразы.
Пользовательская функция
Как видим, полученная формула получилась несколько громоздкой по размеру, поэтому для удобства дополнительно реализуем пользовательскую функцию с тем же функционалом.
Выделение содержимого ячеек в Excel
В Excel можно выделять содержимое ячеек, строк или столбцов.
Примечание: Если лист защищен, возможность выделения ячеек и их содержимого может быть недоступна.
Выделение ячеек
Чтобы выделить ячейку, щелкните ее. Для перехода к ячейке и ее выделения также можно использовать клавиатуру.
Чтобы выбрать диапазон, выделите ячейку, а затем, удерживая основную кнопку мыши, перетащите выделение на другие ячейки.
Для этого также можно использовать SHIFT+клавиши со стрелками.
Чтобы выделить несмежные ячейки и диапазоны ячеек, выберите их, удерживая нажатой клавишу CTRL.
Выделение строк и столбцов
Щелкните букву в верхней части столбца, чтобы выделить его целиком. Можно также щелкнуть любую ячейку в столбце и нажать клавиши CTRL+ПРОБЕЛ.
Выберите номер строки, чтобы выделить ее целиком. Можно также щелкнуть любую ячейку в строке и нажать клавиши SHIFT+ПРОБЕЛ.
Чтобы выделить несмежные строки или столбцы, выберите их номера, удерживая нажатой клавишу CTRL.
Выделение таблицы, списка или листа
Чтобы выделить список или таблицу, щелкните любую содержащуюся в них ячейку и нажмите клавиши CTRL+A.
Чтобы выделить весь лист, нажмите кнопку Выделить все в его левом верхнем углу.
Примечание: В некоторых случаях выделение одной ячейки может привести к выбору нескольких смежных ячеек. Советы о том, как устранить эту проблему, см. в публикации сообщества под названием Как предотвратить одновременное выделение нескольких ячеек в Excel?.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов
В этом руководстве мы обсудим синтаксис и особенности функции Excel ПСТР (в английской версии – MID), а затем вы узнаете несколько вариантов творческого её использования для выполнения сложных задач.
Cинтаксис.
Функция ПСТР возвращает указанное количество знаков, начиная с указанной вами позиции.
Функция Excel ПСТР имеет следующие аргументы:
ПСТР(текст; начальная_позиция; количество_знаков)
Все 3 аргумента обязательны.
Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:
Результат может выглядеть примерно так:
5 вещей, которые вы должны знать о функции Excel ПСТР
Как вы только что убедились, в использовании функции ПСТР в Excel нет ничего страшного. И помня следующие простые факты, вы избежите наиболее распространенных ошибок.
При решении реальных задач в Excel вам чаще всего потребуется использовать ПСТР в сочетании с другими функциями, как показано в следующих примерах.
Как извлечь имя и фамилию.
Если у вас была возможность прочитать наши недавние уроки, вы уже знаете, как вытащить имя с помощью функции ЛЕВСИМВ и получить фамилию с помощью ПРАВСИМВ. Но, как это часто бывает в Excel, одно и то же можно сделать разными способами.
Получаем имя.
Предполагая, что полное имя находится в ячейке A2, имя и фамилия разделены интервалом, вы можете извлечь имя, используя следующую формулу:
ПОИСК используется для сканирования исходного значения на предмет пробела (» «) и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов после имени. Затем вы используете ПСТР, чтобы вернуть подстроку, начинающуюся с первого знака и заканчивая предшествующим пробелу, таким образом извлекая первое имя.
Получаем фамилию.
Чтобы извлечь фамилию из A2, используйте эту формулу:
Как выделить подстроку между двумя разделителями.
Продолжим предыдущий пример. А если, помимо имени и фамилии, ячейка A2 также содержит отчество, то как его извлечь?
Технически задача сводится к определению позиций двух пробелов в исходном тексте, и вы можете сделать это следующим образом:
Чтобы узнать количество извлекаемых знаков, вычтите позицию первого пробела из положения второго. И затем еще вычтите 1 из результата, поскольку вам не нужны лишние интервалы в получившемся результате. Таким образом, у вас есть второй аргумент для формулы:
Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами:
На следующем скриншоте показан результат:
Аналогичным образом вы можете извлечь текст между любыми другими разделителями:
Например, чтобы извлечь отрезок, выделенный запятой с пробелом после неё, используйте следующее выражение:
На следующем рисунке эта формула используется для извлечения из адреса названия города, и она отлично справляется со своей задачей:
Как получить N-е слово из текста.
Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:
Общая формула выглядит следующим образом:
Например, чтобы вытащить второе слово из A2, используйте это выражение:
Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:
Как работает эта формула?
По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:
Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.
Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР($A$2))); (B2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2)))
Следующий рисунок демонстрирует улучшенный вариант (2) в действии:
Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР(СЖПРОБЕЛЫ ($A$2)))); (B2-1)*ДЛСТР(СЖПРОБЕЛЫ($A$2))+1; ДЛСТР(СЖПРОБЕЛЫ($A$2))))
Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты.
Извлекаем слово, содержащее определенный символ.
В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:
Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)); МАКС(1;НАЙТИ(«$»;ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)))-50);99))
Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.
Теперь разберём пошагово:
Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.
С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.
Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.
Как заставить ПСТР возвращать число?
Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст.
Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число.
Например, чтобы извлечь подстроку из 3 символов, начинающуюся с 7- го символа, и преобразовать ее в число, используйте:
На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещены в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A:
Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН:
Вот как можно использовать функцию ПСТР в Excel.
Как извлечь текст из ячейки с помощью Ultimate Suite
Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами.
Вы просто переходите на вкладку Ablebits Data > Текст, выбираете инструмент Split Text и в выпадающем списке нажимаете Извлечь (Extract) :
Теперь вы выбираете исходные ячейки, и какие бы сложные строки они ни содержали, извлечение из них подстроки сводится к этим двум простым действиям:
Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.
В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:
Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? 😉
Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные ячейки будут внесены какие-либо изменения. Когда в ваш набор данных добавляются новые записи, вы можете скопировать формулы в другие ячейки как обычно, без необходимости заново запускать инструмент «Извлечь текст».
Если вам интересно попробовать это, а также множество других полезных функций, включенных в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию. Если вам нравятся инструменты, вы можете получить лицензию по очень специальной цене, которая доступна только для наших русскоязычных пользователей.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Как быстро извлечь число из текста в Excel
В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь».
Проблема выделения числа из текста возникает достаточно часто, особенно когда вы работаете с данными, полученными из других программ. К примеру, нужно вытащить почтовый индекс из адреса, номенклатурный номер из строки с наименованием товара, номер счета из платежного документа. Нужное нам число может находиться в любом месте текста — в начале, в середине или в конце.
Вот что мы рассмотрим в этой статье:
Когда дело доходит до извлечения части текстового значения заданной длины, Эксель предоставляет три текстовых функции (ЛЕВСИМВ, ПРАВСИМВ и ПСТР) для быстрого выполнения этой задачи. А вот когда дело доходит до извлечения числа из буквенно-цифровой строки, Microsoft Excel … не предоставляет ничего.
Чтобы извлечь число из текста в Excel, требуется немного изобретательности, немного терпения и множество различных функций, вложенных друг в друга.
Или вы можете запустить инструмент «Извлечь (Extract)» из надстройки Ablebits Ultimate Suite и выполнить эту операцию одним щелчком мыши. Ниже вы найдете полную информацию обо всех этих методах.
Как извлечь число из конца текстовой строки.
Если у вас есть столбец буквенно-цифровых значений, в котором число всегда идет после текста, вы можете использовать одну из следующих формул, чтобы вытащить из них числа.
Важное замечание! В приведенных ниже формулах извлечение выполняется с помощью функций ПРАВСИМВ и ЛЕВСИМВ, которые относятся к категории текстовых функций. Эти функции всегда возвращают текст. В нашем случае результатом будет числовая подстрока, которая с точки зрения Excel также является текстом, а не числом. Если вам нужно, чтобы результат был числом (которое можно использовать в дальнейших вычислениях), оберните соответствующую формулу в функцию ЗНАЧЕН, или выполните с ней простейшую математическую операцию (например, двойное отрицание).
Чтобы извлечь число из строки «текстовое число», первое, что вам нужно знать, — это с какой позиции начать операцию. Итак, давайте определим положение первой цифры с помощью этой общего выражения:
О логике вычислений мы поговорим чуть позже. На данный момент просто замените слово «ячейка» ссылкой на позицию, содержащую исходный текст (в нашем случае A2), и запишите получившееся выражение в любую пустую клетку той же строки, скажем, в B2:
Хотя формула содержит константу массива, это обычное выражение, которое вводится обычным способом: нажатием клавиши Enter.
Как только позиция первой цифры определена, можно использовать функцию ПРАВСИМВ для извлечения числа. Чтобы узнать, сколько символов нужно извлечь, вы вычитаете позицию первой цифры из общей длины строки и добавляете единицу к результату, потому что первая цифра также должна быть включена:
На следующем скриншоте показаны результаты:
Чтобы исключить вспомогательный столбец, содержащий позицию первой цифры, вы можете встроить формулу МИН непосредственно в функцию ПРАВСИМВ следующим образом:
Чтобы формула возвращала именно число, а не числовую строку, вложите ее в функцию ЗНАЧЕН:
Или просто примените двойное отрицание, использовав два знака «минус»:
Другой способ извлечь число из конца строки — использовать вот такое выражение:
Используя исходный текст в A2, вы записываете приведенную ниже формулу в B2 или любую другую пустую ячейку в той же строке, а затем копируете её вниз по столбцу:
Примечание. Эти формулы предназначены для случая, когда числа находятся только в конце текстовой строки. Если некоторые цифры также находятся в середине или в начале, то ничего не будет работать.
Этих недостатков не имеет третья формула, которая извлекает только последнее число в тексте, игнорируя все предыдущие:
На скриншоте ниже вы видите результат ее работы.
Как видите, цифры в начале или в середине текста игнорируются. Также обратите внимание, что результатом, как и в предыдущих формулах, является число, записанное в виде текста. Как превратить его в нормальное число, мы уже рассмотрели выше в этой статье.
Как извлечь число из начала текстовой строки
Если вы работаете со строками, в которых текст находится после числа, решение для извлечения числа будет аналогично описанному выше. С той только разницей, что вы используете функцию ЛЕВСИМВ для извлечения из левой части текста:
Используя этот метод для A2, извлекаем число при помощи такого выражения:
Это решение работает для текстовых выражений, которые содержат числа только в начале. Если некоторые цифры также находятся в середине или в конце строки, формула не будет работать.
Если вы хотите извлечь только числа слева и игнорировать остальные, воспользуйтесь другой формулой:
Или чуть модифицируем, чтобы ускорить расчеты:
Примечание. Как и в случае с функцией ПРАВСИМВ, функция ЛЕВСИМВ также возвращает числовую подстроку, которая технически является текстом, а не числом.
Как получить число из любой позиции в тексте
Если ваша задача подразумевает извлечение числа из любого места строки, вы можете использовать следующую формулу:
=СУММПРОИЗВ(ПСТР(0&A2; НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2; СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 1)) * СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 0); СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))))+1; 1) * 10^СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))/10)
Для пояснения, как это работает, потребуется отдельная статья. Поэтому вы можете просто скопировать на свой рабочий лист, чтобы убедиться, что это действительно работает 🙂
Обратите внимание, что в этом случае в тексте могут находиться несколько чисел. Все они будут извлечены и объединены в единое целое.
Однако, изучив результаты, вы можете заметить один незначительный недостаток: если исходный текст в ячейке не содержит числа, формула возвращает ноль, как в строке 7 на скриншоте выше. Чтобы исправить это, вы можете заключить формулу в оператор ЕСЛИ, который проверит, содержит ли исходный текст какое-либо число. Если это так, формула извлекает это число, в противном случае возвращает пустую строку:
В отличие от всех предыдущих примеров, результатом этих формул является число. Чтобы убедиться в этом, просто обратите внимание на выровненные по правому краю значения в столбце B и усеченные ведущие нули (например, 88 вместо 088).
Если число, которое вы хотите извлечь, ограничено какими-то знаками-разделителями, то можно использовать функцию ПСТР. Рассмотрим пример, как получить номер счета из текста платежа.
Мы будем искать позицию знака «№» и позицию следующего за ним первого пробела. То, что находится между ними, как раз и будет номером счёта:
На скриншоте ниже вы видите, как это работает.
Вот еще один возможный вариант вынимания числа из текста. Необходимо извлечь первое встретившееся число из текста.
или заменяем список цифр функцией:
Как видите, получаем только первое число, независимо от его расположения:
И еще один пример. Давайте попробуем достать все числа из текста, разграничив их каким-то разделителем. Например, дефисом “-“.
В этом случае придется использовать формулу массива:
Мы нашли все числа в тексте, как вы видите на скриншоте ниже:
Откорректировав эту формулу, вы можете использовать любой другой разделитель.
Поскольку между ними есть разделители, то вы легко можете распределить эти числа в отдельные ячейки. Как это сделать — читайте в материале 8 способов разделить ячейку Excel на две или несколько.
Как выделить число из текста с помощью Ultimate Suite
Как вы только что видели, не существует простой и понятной формулы Excel для извлечения чисел из текстовой строки. Если у вас есть трудности с пониманием формул или их настройкой для ваших наборов данных, вам может понравиться этот простой способ получить число из текста в Excel.
С надстройкой Ultimate Suite, добавленной на вашу ленту Excel, вы можете быстро получить число из любой буквенно-цифровой строки:
Я советую активировать это поле, если вы хотите, чтобы извлеченные числа обновлялись автоматически, как только в исходные значения вносятся какие-либо изменения. Если нужно, чтобы результаты не зависели от будущих изменений (например, если вы планируете удалить исходные данные позже), не используйте эту опцию.
Как и в предыдущем примере, результаты извлечения являются числами. Это означает, что вы можете подсчитывать, суммировать, усреднять или выполнять любые другие вычисления с ними.
Если установлен флажок «Вставить как формулу», вы увидите выражение в строке формул. Любопытно узнать, какое именно? Просто скачайте пробную версию Ultimate Suite и убедитесь сами 🙂
Если вы хотите иметь это, а также еще более 60 полезных инструментов в Excel, воспользуйтесь этой надстройкой.
Я постарался дать вам максимально полные рекомендации, какими способами можно извлечь число из текста. Конечно, они не могут охватить все возможные случаи. Поэтому если встретилось что-то особенно заковыристое — не стесняйтесь писать в комментариях. Постараюсь помочь по мере сил.