Отнять текст из текста excel
Формулы в данной нам статье полезны для извлечения слов из текста, содержащегося в ячейке. К примеру, вы сможете сделать формулу для извлечения первого слова в предложении.
Извлечение первого слова из строчки
Чтоб извлечь 1-ое слово из строчки, формула обязана отыскать позицию первого знака пробела, а потом применять эту информацию в качестве аргумента для функции ЛЕВСИМВ. Последующая формула делает это: =ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1) .
Эта формула возвращает весь текст до первого пробела в ячейке A1. Но у нее есть маленький недочет: она возвращает ошибку, если текст в ячейке А1 не содержит пробелов, поэтому что состоит из 1-го слова. Несколько наиболее непростая формула решает делему при помощи новейшей функции ЕСЛИОШИБКА, отображая все содержимое ячейки, если произошла ошибка:
=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1);A1) .
Если для вас необходимо, чтоб формула была совместима с наиболее ранешними версиями Excel, вы не сможете применять ЕСЛИОШИБКА. В таком случае придется обойтись функцией ЕСЛИ и функцией ЕОШ для проверки на ошибку:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1))
Извлечение крайнего слова строчки
Извлечение крайнего слова строчки — наиболее непростая задачка, так как функция НАЙТИ работает лишь слева вправо. Таковым образом, неувязка состоит в поиске крайнего знака пробела. Последующая формула, но, решает эту делему. Она возвращает крайнее слово строчки (весь текст, последующий за крайним эмблемой пробела):
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"";"")))))
Но у данной нам формулы есть таковой же недочет, как и у первой формулы из предшествующего раздела: она возвратит ошибку, если строчка не содержит по последней мере один пробел. Решение заключается в использовании функции ЕСЛИОШИБКА и возврате всего содержимого ячейки А1, если возникает ошибка:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))));A1)
Последующая формула совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;"";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))))
Извлечение всего, не считая первого слова строчки
Последующая формула возвращает содержимое ячейки А1, кроме первого слова:
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ":A1;1)) .
Если ячейка А1 содержит текст 2008 Operating Budget, то формула возвратит Operating Budget.
Формула возвращает ошибку, если ячейка содержит лишь одно слово. Последующая версия формулы употребляет функцию ЕСЛИОШИБКА, чтоб можно было избежать ошибки; формула возвращает пустую строчку, если ячейка не содержит наиболее 1-го слова:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1));"")
А эта версия совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));"";ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1)))
Извлечение части текста – самая всераспространенная задачка при работе с текстом в Excel. Нередко к данной нам задачке и сводится вся работа над текстом. Чтоб получить часть знаков из строчки, необходимо обладать функциями поиска, удаления излишних знаков, определения длины строчки и др.
Для получения части текста, в Эксель есть 3 функции:
- ЛЕВСИМВ(Строчка; Количество_символов) – выводит данное количество знаков с левого края. К примеру, =ЛЕВСИМВ(А1;10) выведет 10 первых знаков строчки в ячейке А1 . Функция имеет 2 неотклонимых аргумента – Строчка-источник и количество выводимых знаков;
Функция ЛЕВСИМВ в Эксель
- ПРАВСИМВ(Строчка; Количество_символов) – функция идентична с предшествующей, она выводит данное количество знаков справа. Другими словами, =ПРАВСИМВ(А1;10) в итоге выдаст 10 крайних знаков из строчки А1 .
Функция ПРАВСИМВ в Excel
- ПСТР(Строчка; Начальный_символ; Количество знаков) – выбирает из текста необходимое количество символов, начиная с данного. К примеру, =ПСТР(А1;5;3) выведет 3 знака начиная с 5-го (5-7 знаки строчки).
Функция ПСТР в Эксель
Все эти функции в подсчёте количества знаков учитывают излишние пробелы, непечатаемые знаки, потому рекомендую поначалу очистить текст от излишних символов.
Функции ЛЕВИСМВ, ПРАВСИМВ, ПСТР – это обычной и мощнейший инструмент, если употребляется в композиции с иными текстовыми функциями. Вы увидите это в уроке-практикуме по строчным функциям.
А последующий пост мы посвятим поиску подходящего текста в строке. Входите и читайте. Лишь не плохое владение функциями дозволит для вас отлично делать задачки в Microsoft Excel!
Если вы еще не прочитали посты о написании формул и применении функций – непременно это сделайте, без их исследование функций Excel будет труднее и подольше!
Примечание: Мы стараемся как можно оперативнее обеспечивать вас животрепещущими справочными материалами на вашем языке. Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Просим вас уделить пару секунд и сказать, посодействовала ли она для вас, при помощи клавиш понизу странички. Для удобства также приводим ссылку на оригинал (на британском языке).
Принципиально: Вычисляемые результаты формул и некие функции листа Excel могут несколько различаться на компах под управлением Windows с архитектурой x86 либо x86-64 и компах под управлением Windows RT с архитектурой ARM. Подробнее о этих различиях.
Допустим, для вас необходимо выяснить, сколько товарных продуктов труднодоступно (отнять прибыльные продукты из итогового счета). Либо, может быть, для вас необходимо знать, сколько служащих приближается к возрасту выбытия (отнять количество служащих в 55 от полного количества служащих).
В данной нам статье
Вычитать числа можно несколькими методами, в том числе:
Вычитание чисел в ячейке
Для обычного вычитания используйте арифметический оператор – (символ "минус").
К примеру, если ввести формулу = 10-5 в ячейку, в ячейке будет отображено значение 5.
Вычитание чисел в спектре
Добавление отрицательного числа идентично для вычитания 1-го числа из другого. Функция сумм употребляется для сложения отрицательных чисел в спектре.
Примечание: В Excel не существует функции ВЫЧЕСТЬ. Используйте функцию СУММ, преобразуя все числа, которые нужно отнять, в их отрицательные значения. К примеру, функция СУММ(100,-32,15,-6) возвращает итог 77.
Пример
Чтоб отнять числа разными методами, сделайте обозначенные ниже деяния.
Изберите все строчки в таблице ниже, а потом нажмите клавиши CTRL + C на клавиатуре.
Вычитает 9000 из 15000 (что равно 6000).
Суммирует все числа в перечне, включая отрицательные числа (итог — 16000).
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Чтоб переключиться меж просмотром результатов и просмотром формул, нажмите клавиши CTRL + ‘ (символ ударения) на клавиатуре. Не считая того, можно надавить клавишу " Показать формулы " на вкладке " формулы ".
Внедрение функции сумм
Функция сумм добавляет все числа, обозначенные в качестве аргументов. Любой аргумент быть может спектр, ссылка на ячейку, массив, константа либо формулалибо результатом выполнения иной функции. К примеру, функция сумм (a1: A5) суммирует все числа в спектре ячеек от a1 до A5. Очередной пример — Sum (a1; a3; A5) , при всем этом добавляются числа, находящиеся в ячейках a1, A3 и A5 ( аргументыa1, A3 и A5).
Как выделить случайное слово в ячейке в Excel?
Приветствую всех, почетаемые читатели блога TutorExcel.Ru.
Задачка выделения определенных (по порядку) слов из предложения имеет довольно обширное применение при структурировании и анализе данных, к примеру, выделение части артикула из кода продукта, имени либо фамилии из полной записи ФИО и т.д.
Если данные представлены в однородном виде (например, необходимо выделить 1-ое/2-ое/…/крайнее слово в каждой ячейке столбца, где все данные содержат однообразное количество слов), то можно пользоваться инвентарем Текст по столбцам и получить разбитые слова в примыкающих столбцах.
Не будем тщательно останавливаться на этом методе, потому что использовать его не постоянно можно (данные в этом случае имеют специфичную форму) и комфортно (при разделении по столбцам добавляются новейшие столбцы, что изменяет структуру документа).
Изучим наиболее всепригодный метод, который будет работать для хоть какого типа данных.
Но перед решением задачки поначалу найдем ответ на довольно обычной и тривиальный вопросец.
Что в предложениях отделяет одно слово от другого?
В большинстве современных языков для разделения слов употребляется знак пробела, потому сначала метод будет строиться на поиске пробела, а дальше мы уже будем выделять необходимое слово.
Пойдем по порядку.
Выделение первого слова в ячейке в Excel
C помощью функции НАЙТИ найдем позицию первого пробела, а потом воспользуемся функцией ЛЕВСИМВ, которая возвращает обозначенное количество знаков с начала текста (как раз до первого отысканного пробела):
Заместо ЛЕВСИМВ можно пользоваться функцией ПСТР, которая наиболее всепригодна и дозволяет возвратить данное количество знаков начиная с обозначенной позиции, а не только лишь с начала строчки (в этом случае с 1, потому что мы выделяем конкретно 1-ое слово):
Недочет такового метода в том, что если в ячейке содержится ровно 1 слово, то она возвратит значение ошибки, потому что пробелов в нем нет:
Потому добавочно добавим проверку на ошибку — в случае если во фразе нет пробелов, то возвращаем начальный текст:
Выделение крайнего слова в ячейке в Excel
С поиском крайнего слова все несколько труднее — мы не знаем сколько их во фразе, потому для начала определим количество пробелов в предложении. В этом нам посодействуют функции ПОДСТАВИТЬ при помощи которой мы поначалу удалим все пробелы во фразе, и ДЛСТР, которая покажет символьную длину фразы.
Таковым образом, разность длины начальной фразы и фразы без пробелов даст нам итоговое количество пробелов в ячейке.
Дальше вновь воспользуемся функцией ПОДСТАВИТЬ и заменим крайний пробел (его порядковый номер мы уже знаем) на особый знак, который буквально не повстречается в предложении (обычно употребляются знаки #, ^ и т.п.).
Опосля чего же функцией ПРАВСИМВ (возвращает обозначенное количество знаков с конца текста) выделяем все знаки от специального знака (в этом случае функцией НАЙТИ отыскиваем позицию знака #) до конца предложения:
Как и в прошлом варианте, если во фразе всего 1 слово, то формула выдаст ошибку, потому для корректной работы тут также необходимо будет добавить аналогичную проверку, что и в примере выше.
Сейчас перейдем к самому сложному варианту — выделению случайного по порядку слова.
Выделение n-го слова в ячейке в Excel
Воспользуемся последующим приемом — продублируем все пробелы меж словами весьма огромное количество раз (заранее больше длины фразы, к примеру, 1 000 раз), опосля чего же функцией ПСТР выделяем 1 000 знаков умноженные на n (номер подходящего слова), а потом функцией ПРАВСИМВ возвращаем лишь последнюю 1 000 знаков (в каком как раз содержится разыскиваемое слово).
Дальше удаляем все излишние пробелы и получаем:
По другому говоря, метод последующий — мы разбиваем предложение на блоки, состоящие из 1-го слова предложения и огромного количества пробелов, а потом вытаскиваем подходящий блок и удаляем оттуда излишние пробелы.
Если же мы желаем достать другое слово, то заместо *4 в примере выше, необходимо просто поставить иной номер (1 для первого слова, 2 для второго и т.д.), а если обозначенный номер больше количества слов в предложении, то в качестве ответа будет получено крайнее слово.
Также преимуществом данной формулы является ее универсальность, поэтому что ее можно применять для хоть какого по порядку слова — от первого до крайнего, к тому же не надо добавочно созодать проверку на длину фразы.
Пользовательская функция
Как лицезреем, приобретенная формула вышла несколько массивной по размеру, потому для удобства добавочно реализуем пользовательскую функцию с этим же функционалом.
Перейдем в редактор Visual Basic (резвый переход композицией кнопок Alt + F11), создаём новейший модуль (Insert -> Module) и вставляем туда код функции:
Как в Excel извлечь из ячейки крайние два слова
В этом учебном материале по Excel мы разглядим примеры того, как извлечь из ячейки крайние два слова.
Основная формула
Описание
Чтоб извлечь крайние два слова из ячейки, вы сможете применять формулу, построенную при помощи нескольких функций Excel, включая ПСТР, НАЙТИ, ПОДСТАВИТЬ и ДЛСТР. В показанном примере формула C5 имеет последующий вид:
Пояснение
На самом деле, эта формула употребляет функцию ПСТР для извлечения знаков, начиная с предпоследнего пробела. Функция ПСТР воспринимает 3 аргумента: текст, с которым необходимо работать, исходную позицию и количество извлекаемых знаков.
Текст берется из столбца B, и количество знаков быть может хоть каким огромным числом, которое обеспечит извлечение крайних 2-ух слов. Задачка заключается в том, чтоб найти исходную позицию, которая находится сходу опосля предпоследнего пробела. Умная работа производится сначала с функцией ПОДСТАВИТЬ, у которой есть необязательный аргумент, именуемый номером экземпляра. Эта функция употребляется для подмены предпоследнего пробела в тексте эмблемой «@», который потом находится с функцией НАЙТИ.
В приведенном ниже фрагменте кода показано, сколько пробелов в общем тексте, из которого вычитается 1.
В показанном примере в тексте 5 пробелов, потому приведенный выше код возвращает 4. Это число передается во внешнюю функцию ПОДСТАВИТЬ как номер экземпляра:
Это принуждает ПОДСТАВИТЬ подменять 4-ый пробел на «@». Выбор знака @ случайный. Вы сможете применять хоть какой знак, которого нет в начальном тексте.
Потом функция НАЙТИ находит в тексте знак «@»:
Итог функции НАЙТИ — 14, к которому добавляется 1, чтоб получить 15. Это исходная позиция, которая перебегает в функцию ПСТР в качестве второго аргумента. Для простоты количество извлекаемых знаков агрессивно задано как 100. Это число произвольно и быть может изменено зависимо от ситуации.
Извлечь из ячейки крайние N слов
Эту формулу можно обобщить для извлечения из ячейки крайних N слов, заменив агрессивно запрограммированную 1 в примере на (N-1). Не считая того, если вы извлекаете много слов, вы сможете поменять агрессивно запрограммированный аргумент в ПСТР, 100, на большее число. Чтоб гарантировать, что число довольно велико, вы сможете просто применять функцию ДЛСТР последующим образом:
Как извлечь номер либо текст из Excel при помощи данной нам функции
— но если вы используете оба в одной ячейке, вы сможете столкнуться с некими трудностями. К счастью, вы сможете извлечь числа либо текст из ячеек для наиболее действенной работы с вашими данными. Мы демонстрируем несколько вариантов, зависимо от формата, в каком находятся ваши данные.
Числа, хранящиеся как текст
Это рядовая ситуация, и, к счастью, с ней весьма просто совладать. Время от времени ячейки, содержащие лишь числа, некорректно помечаются как текстовые, что не дозволяет Microsoft Excel применять их в операциях. На изображении ниже вы сможете созидать, что ячейки в столбце A хранятся в виде текста, о чем свидетельствует зеленоватый флаг на каждой ячейке, также поле формата, оба из которых выделены ниже.
Конвертировать в число
Чтоб решить эту делему, просто щелкните в ячейке, нажмите на поле с предупреждением и изберите «Конвертировать в число». Вот и все! Это можно создать для нескольких ячеек, выбрав их все и щелкнув по полю с предупреждением, но это, возможно, не так отлично, как в последующем способе.
Текст в столбцы
Если у вас много ячеек, которые нужно поправить, внедрение этого способа может занять грандиозное количество времени. Чтоб убыстрить процесс, вы сможете применять Microsoft Excel Text to Columns
функция. Изберите числа, которые вы желаете преобразовать, перейдите на Данные> Текст в столбцы, и используйте мастер, чтоб убедиться, что числа выходят верно (по большей части, для вас просто необходимо надавить последующий также Конец не возиться ни с одной из опций).
Текст будет преобразован в числа, и вы готовы к работе. Направьте внимание, что это работает лишь для 1-го столбца за раз, потому, если у вас много столбцов, вы сможете применять этот крайний способ.
Особая паста
Чтоб этот способ работал, для вас необходимо ввести число 1 в ячейку (принципиально, чтоб оно было в числовом формате). Изберите эту ячейку и скопируйте ее. Сейчас изберите все числа, которые вы желаете конвертировать в числовой формат, и нажмите Edit> Paste Special. Изберите «Помножить» в разделе «Операция» и нажмите «ОК».
Все избранные вами номера будут преобразованы в общий формат, который распознает номера. Любые текстовые ячейки также будут преобразованы в общий формат, что не обязано вызывать заморочек.
Извлечение чисел либо текста из ячеек смешанного формата
Сейчас мы перебегаем к самой сложной части: извлечению чисел из ячеек, которые содержат несколько форматов ввода. Если у вас есть число и единица измерения (к примеру, «7 лопат», как у нас ниже), вы столкнетесь с данной нам неувязкой. Чтоб решить эту делему, мы разглядим несколько разных методов разделения ячеек на числа и текст, что дозволит для вас работать с каждой из их в отдельности. 1-ый способ мало громоздок, но весьма отлично работает с маленькими наборами данных.
ЛЕВО ПРАВО ПОИСК
Основная функция, которую мы будем тут применять, это LEFT, которая возвращает самые левые знаки из ячейки. Как вы сможете созидать в нашем наборе данных выше, у нас есть ячейки с одно-, двух- и трехсимвольными числами, потому нам необходимо будет возвратить самый левый один, два либо три знака из ячеек. Сочетая ВЛЕВО с функцией ПОИСК
, мы можем возвратить все слева от места. Вот функция:
Это возвратит все слева от места. Внедрение ручки наполнения
чтоб применить формулу к остальным ячейкам, вот что мы получаем (вы сможете узреть формулу в панели функций в высшей части изображения):
Видите ли, сейчас у нас все есть изолированные числа, потому ими можно манипулировать. Желаете также выделить текст? Мы можем применять функцию RIGHT таковым же образом:
Это возвращает x знаков справа от ячейки, где x — общая длина ячейки минус количество знаков слева от пробела.
Сейчас текстом можно манипулировать. Желаете соединить их опять? Просто используйте функцию CONCATENATE со всеми ячейками в качестве входных данных:
Разумеется, что этот способ работает идеальнее всего, если у вас есть лишь числа и единицы, и ничего больше. Если у вас есть остальные форматы ячеек, для вас, может быть, придется проявить творческий подход к формулам, чтоб все работало верно. Если у вас есть огромный набор данных, это будет стоить времени, которое пригодится для выяснения формулы!
Текст в столбцы
Функция «Текст в столбцы» полезна для столбцов, содержащих лишь числа, но она также может упростить жизнь, если у вас есть ячейки смешанного формата. Изберите столбец, с которым желаете работать, и нажмите Данные> Текст в столбцы. Потом вы сможете применять мастер, чтоб выбрать разделитель (пробел, обычно, наилучший) и поделить столбец так, как вы желаете.
Если у вас есть лишь одно- и двузначные числа, функция «Фиксированная ширина» также быть может полезна, так как она будет делить лишь 1-ые два либо три знака ячейки (вы сможете сделать несколько разбиений, если желаете, но я сохраню полное разъяснение разбиения по фиксированной ширине для иной статьи).
Если ваш набор данных содержит много столбцов, и вы не желаете применять текст в столбцах для всякого из их, вы сможете просто получить этот же эффект, используя резвый экспорт и импорт. Поначалу экспортируйте вашу электрическую таблицу в виде файла значений, разбитых запятыми (CSV). Нажмите Файл> Сохранить как… и сохраните ваш файл в формате CSV.
Сейчас откройте новейшую электрическую таблицу и нажмите Файл> Импорт… Изберите файл CSV и используйте мастер импорта, чтоб разбить данные на два столбца (вы будете применять те же шаги, что и в мастере «Текст в столбцы»). Выбрав «Пробел» в разделе «Разделители», вы скажете Microsoft Excel поделить данные, где бы они ни находились, чтоб изолировать числа и текст.
Когда закончите, нажмите «Готово», и вы получите новейшую электрическую таблицу, в какой столбцы разбиты на две части. Естественно, если у вас в ячейке наиболее 1-го пробела, вы получите наиболее 2-ух столбцов, как вы сможете созидать тут:
К огорчению, нет неплохого решения для этого с внедрением этого способа; для вас просто необходимо соединить ячейки вкупе.
Наиболее сложные ситуации
Используя приведенные выше стратегии, вы можете извлечь числа либо текст из большинства ячеек смешанного формата, которые доставляют для вас проблемы. Даже если они этого не делают, вы сможете соединить их с некими массивными текстовыми функциями, включенными в Microsoft Excel, чтоб получить знаки, которые вы ищете. Но, есть еще наиболее сложные ситуации, которые требуют наиболее сложных решений.
К примеру, я отыскал сообщение на форуме, где кто-то желал извлечь числа из строчки, к примеру, «45t * 65 /», чтоб в итоге он получил «4565». Иной создатель отдал последующую формулу в качестве 1-го из методов создать это:
Если честно, я понятия не имею, как это работает. Но, согласно сообщению на форуме, он будет извлекать числа из сложной цепочки цифр и остальных знаков. Дело в том, что при достаточном времени, терпении и усилиях вы сможете извлечь числа и текст фактически из всего! Для вас просто необходимо отыскать нужные ресурсы
Какую стратегию вы используете?
Сейчас, когда у вас есть наилучшее представление о том, как извлечь числа и буковкы из ячеек смешанного формата, мы желаем знать, какие остальные стратегии вы употребляли для такого же. Опубликуйте свои безумные формулы Excel, процессы либо чего-нибудть еще, что быть может полезным в комментах, чтоб мы все могли обучаться на их!
