Как вытащить слово в отдельную колонку

9

Как вытащить число или часть текста из текстовой строки в Excel

Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.

Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.

Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.

И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.

Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).

Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.

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

Вся трудность, в том чтобы извлечь код из текстовой строки.

Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.

В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.

Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.

Сложнее если нужные нам символы находятся в середине текста.

Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.

=ПСТР(текст; начальная_позиция; количество_знаков)

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.

Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

В нашем случае формула будет иметь вид:

Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.

=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])

Искомый_текст – обязательный аргумент. Текст, который требуется найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.

Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:

Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.

В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.

Как расцепить текст в Excel

Часто перед пользователями табличного редактора встает задача расцепления текста. Существует несколько способов, позволяющих реализовать эту несложную процедуру. В статье мы детально рассмотрим каждый из методов.

Первый метод: применение автоматического инструмента

В табличном редакторе существует специальный инструмент автоматического вида, позволяющий реализовать эту процедуру. Подробная инструкция выглядит так:

1. Зажимаем левую клавишу мышки и производим выделение всех ячеек, которые мы планируем расцепить на колонки.

1. Передвигаемся в подраздел «Данные» и жмем ЛКМ по элементу «Текст по столбцам».

1. Отобразилось окошко, имеющее наименование «Мастер распределения текста по столбцам». Ставим пунктик рядом с надписью «с разделителями». Обычно в качестве разделителя выступает пробел, но на дальнейшем этапе можно отредактировать вид разделения.

1. Выставляем отметку около того символа, который мы планируем использовать в качестве разделителя. Внизу окошка находится поле «Образец разбора данных». Здесь можно предварительно посмотреть полученный результат.

1. На следующем этапе задаем новый формат колонок, а также ту локацию, в которую будет перемещена новая информация. После проведения всех процедур щелкаем левой клавишей мышки на «Готово», чтобы сохранить внесенные изменения.

1. Возвращаемся к исходной табличке и убеждаемся, что процесс расцепления произошел корректно.

Стоит отметить! Данный метод подходит только в тех случаях, когда необходимо произвести только одно расцепление, указав для каждого слова новую колонку. Этот метод не подходит для работы, в которой необходимо постоянно проводить процедуру расцепления, так как будет тратиться много времени. Рассмотрим следующие методы.

Второй метод: применение формулы расцепления текста

Такая формула позволяет подсчитать позиции слов, отыскать пробелы, а затем расцепить все слова на отдельные колонки. Этот способ подразумевает большое количество действий, поэтому нужно внимательно следовать этапам подробной инструкции. Для примера мы возьмем поле, в котором располагается три слова, которые разделены между собой пробелами.

Этап первый: расцепление 1-го слова

Подробная инструкция выглядит так:

1. Создаем 3 новых колонки с заголовками.

1. Производим выбор той ячейки, в которой мы планируем расположить 1-е слово. Вписываем сюда формулу: =ЛЕВСИМВ(.

1. Щелкаем левой клавишей мышки на «Аргументы функции».

1. В поле «Текст» при помощи левой клавиши мышки указываем координаты ячейки. В нашем случае это А1.

1. В поле «Количество_знаков» вбиваем формулу ПОИСК(), позволяющую автоматически подсчитать число символов до разделителя.

1. Формула отобразилась в строчке для ввода формул. Жмем на нее ЛКМ, чтобы попасть в окошко «Аргументы функции».

1. В строчку «Искомый_текст» вбиваем необходимый разделитель. Стандартно – это пробел. В строчку «Текст_для_поиска» вбиваем координату А1.

1. Жмем ЛКМ на 1-ю функцию, чтобы снова попасть в окошко ее аргументов. Дописываем правее 2-го аргумента значение -1. Это позволит формуле учесть не сам пробел, а знак до него.

1. Закрываем окошко аргументов, чтобы убедиться в правильном выводе информации.

1. Растягиваем новую ячейку вниз при помощи зажатия ЛКМ. Формула автоматическим образом подставилась в другие ячейки.

Этап второй: расцепление 2-го слова

Расцепление 2-го слова – это самый сложный этап, так как оно с 2-х сторон окружено разделителями. Подробная инструкция выглядит так:

1. Для работы нам понадобится формула =ПСТР(. Записываем ее в ячейку, а затем передвигаемся в окошко для ввода аргументов.

1. Эта формула позволяет отыскать необходимую строчку в текстовой информации. В строчку «Текст» вводим координату А1.

1. В поле «Начальная_позиция» вбиваем формулу ПОИСК(), позволяющую автоматически подсчитать число символов до разделителя.

1. Формула отобразилась в строчке для ввода формул. Жмем на нее ЛКМ, чтобы попасть в окошко «Аргументы функции».

1. В строчку «Искомый_текст» вбиваем необходимый разделитель. Стандартно – это пробел. В строчку «Текст_для_поиска» вбиваем координату А1.

1. Вернувшись в изначальное окошко, добавляем во второй аргумент значение +1 в конец. Это позволит осуществить начало счета со следующего знака после найденного разделителя.

1. В строчку «Количество_знаков» снова вводим формулу ПОИСК().

1. Аргументы оператора заполняем таким же образом, как и выше.

1. Дополнительно здесь же в поле «Нач_позиция» необходимо еще раз прописать ПОИСК(), чтобы отыскать 2-й разделитель.

1. Окошко аргументов для оператора ПОИСК() заполняем всем тем же образом, как и ранее.

1. Передвигаемся к первому ПОИСКУ и добавляем значение +1 в аргумент «Нач_позиция».

1. Жмем ЛКМ на элемент =ПСТР, находящийся в строчке формул. Наводим указатель в конец строчки «Количество_знаков».
2. Добавляем в конце значение -ПОИСК(» «;A1)-1), чтобы закончить расчет разделителей.

1. Закрываем окошко аргументов, чтобы убедиться в правильном выводе информации.
2. Растягиваем новую ячейку вниз при помощи зажатия ЛКМ. Формула автоматическим образом подставилась в другие ячейки.

Третий этап: расцепление 3-го слова

Этот этап схож с первым, но имеет небольшие отличия. Подробная инструкция выглядит так:

1. Вбиваем в необходимую ячейку формулу =ПРАВСИМВ( и перемещаемся в окошко аргументов.

1. В строчку «Текст» вбиваем координату А1.

1. В строчку «Число_знаков» вбиваем формулу ДЛСТР(А1). Она позволяет выявить число символов в данных.

1. Добавляем в конец этой строчки знакомое нам значение -ПОИСК(). Передвигаемся в окошко аргументов данной формулы.

1. Вбиваем уже знакомые нам данные для поиска пробела в строчке.

1. В строчку «Нач_позиция» вбиваем дополнительный ПОИСК().

1. Указываем здесь такие же данные.

1. Возвращаемся к прошлому «ПОИСКУ».

1. В конец строчки «Нач_позиция» добавляем значение +1.

1. Подтверждаем все внесенные изменения.

1. Готово! Мы реализовали процедуру расцепления 3-х слов. Каждое слово находится в отдельной колонке.

Заключение

Мы рассмотрели два способа расцепления текста в табличном редакторе. Второй способ является наиболее эффективным, но для его реализации необходимо выполнить большое количество действий. Понимание работы процедуры расцепления значительно облегчит пользователям работу в табличном редакторе.

Excel как извлечь часть текста из ячейки

Извлечение части текста – самая распространенная задача при работе с текстом в Excel. Часто к этой задаче и сводится вся работа над текстом. Чтобы получить часть символов из строки, нужно владеть функциями поиска, удаления лишних символов, определения длины строки и др.

Для получения части текста, в Эксель есть 3 функции:

1. ЛЕВСИМВ(Строка; Количество_символов) – выводит заданное количество символов с левого края. Например, =ЛЕВСИМВ(А1;10) выведет 10 первых символов строки в ячейке А1 . Функция имеет 2 обязательных аргумента – Строка-источник и количество выводимых символов;

Функция ЛЕВСИМВ в Эксель

1. ПРАВСИМВ(Строка; Количество_символов) – функция схожа с предыдущей, она выводит заданное количество символов справа. То есть, =ПРАВСИМВ(А1;10) в результате выдаст 10 последних символов из строки А1 .

Функция ПРАВСИМВ в Excel

1. ПСТР(Строка; Начальный_символ; Количество символов) – выбирает из текста нужное количество знаков, начиная с заданного. Например, =ПСТР(А1;5;3) выведет 3 символа начиная с 5-го (5-7 символы строки).

Функция ПСТР в Эксель

Все эти функции в подсчёте количества символов учитывают лишние пробелы, непечатаемые символы, поэтому рекомендую сначала очистить текст от лишних знаков.

Функции ЛЕВИСМВ, ПРАВСИМВ, ПСТР – это простой и мощный инструмент, если используется в комбинации с другими текстовыми функциями. Вы увидите это в уроке-практикуме по строчным функциям.

А следующий пост мы посвятим поиску нужного текста в строке. Заходите и читайте. Только хорошее владение функциями позволит вам эффективно выполнять задачи в Microsoft Excel!

Если вы еще не прочли посты о написании формул и применении функций – обязательно это сделайте, без них изучение функций Excel будет сложнее и дольше!

Как в Эксель (Excel) вытащить часть текста из ячейки в другую ячейку?

Как в Excel извлечь часть текста из ячейки?

Например, в ячейке написана категория товара и информация о товаре.

Как выташить в отдельную ячейку только название категории («Перчатки хозяйственные», «Молоток слесарный» и т.п.)?

Если у Вас данные (которые нужно обработать, все эти «молотки» и «перчатки») всегда отделены от остальной части текста запятой и первая ячейка с данными это B2, то формула такая

напишите ее в любую свободную ячейку (например правее) в той же строке, а потом растяните вниз и все ваши тысячи строк будут обработаны.

Очевидно, что вытащить часть текста в другую ячейку можно с помощью специальных функций для работы со строками.

В Excel их довольно много, и в первую очередь можно выделить такие функции, как:

ЛЕВСИМВ и ПРАВСИМВ — излекают определённое число символов слева и справа соответственно.

ДЛСТР — длина строки.

НАЙТИ — возвращает позицию, с которой подстрока или символ входит в строку.

ПОДСТРОКА — извлекает подстроку из текста, которая отделена определённым символом-разделителе­ м.

ПСТР — извлекает указанное число знаков из строки (начиная с указанной позиции).

КОНЕЦСТРОКИ и НАЧАЛОСТРОКИ — возвращает строку после / до указанной подстроки.

Но здесь всё зависит от того, как именно эти данные расположены в исходной строке — одно дело в самом конце / начале, а другое — в середине.

В любом случае нужно постараться найти какой-то признак — слово или символ, до или после которого в ячейке находятся нужные данные, после чего использовать его в качестве аргумента в функциях, про которые я написал выше.

Пример 1

Исходные данные такие:

Предположим, нужно извлечь в отдельную ячейку цену товара (3500 рублей, 4200 рублей).

Можно увидеть, что в этих ячейках цене предшествует текст «размеры, » — то есть можно воспользоваться функцией КОНЕЦСТРОКИ и вытащить всё, что находится после этого текста.

Итак, ставим курсор в ячейку, куда нужно извлечь цену, и на вкладке «Формулы» выбираем «Текстовые» -> «КОНЕЦСТРОКИ».

Указываем аргументы функции (обязательные):

ТЕКСТ — указываем ячейку, из которой нужно извлечь подстроку (B2 или B3).

НАЙТИ — указываем подстроку, после которой должно начаться извлечение текста («размеры, «).

Нажимаем на кнопку «OK» и получаем то, что было нужно:

Формула получилась такая:

А если требуется, чтобы было только число (без рублей), то можно, например, использовать функцию НАЧАЛОСТРОКИ.

В этом случае в качестве 1 аргумента (исходной строки) вводим формулу, созданную выше, а в качестве 2 аргумента — » «.

Пример 2

Нужно извлечь в отдельную ячейку название цвета (красный, коричневый и т.п.).

Здесь всё проще, так как название цвета находится в самом конце строки — и можно, например, использовать функцию ПРАВСИМВ.

У этой функции 2 аргумента:

Текст — указываем ячейку, из которой нужно извлечь подстроку.

Число_знаков — это разность между длиной исходной строки (функция ДЛСТР) и позицией запятой в этой строке (функция НАЙТИ), также дополнительно нужно отнять единицу, так как после запятой стоит пробел.

Формула и результат:

Но мне всё же больше нравится вариант с упомянутой выше функцией КОНЕЦСТРОКИ.

Она менее громоздкая и не содержит вложенных функций.

Эксель многие любят за то, что можно быстро обрабатывать и менять таблицы, так как надо.

Вот и в этом случаи, для того, чтобы вытащить из ячейки текст, нужно в пустой рядом столбик ввести формулу. Но тут не так всё просто. В зависимости от того, с какой стороны нужен текст, вводим формулу Левсимв и Правсимв. Одна из этих функций выведет нужный текст справа, другая слева. При этом формула будет выглядеть примерно так:=ЛЕВСИМВ(В1;10). В данном случаи 10 число символов. Но если число символов не одинаковое, то метод не совсем подойдёт.

Тогда можно будет попробовать функцию текстовые, конец строки. Если перед нужной вам фразой стоит одно и тоже слово в каждой строке. Появится окошко, и в строке найти добавить это слова. Нужный текст после этого слова переместится.

Если в таблице одна или 2 строки, тогда можно воспользоваться функцией нажатия клавиш Ctrl+C скопировать и Ctrl+V вставить, а если в таблице нужно поменять цену для большого количества параметров, переходите в шапку инструментов, и действуйте по алгоритму, который находится под кнопой формулы — текстовые и в выпадающем меню находите среди абракадабры из сокращений «конецстроки»

далее следуя указаниям меняете в открытых окнах параметры. Подставляете какие заданы, а машина сама все посчитает. За это эксель и любят бухгалтеры, по мне так самая кривая программа после ворда. имхо для бв.

Что касается абракадабры в выпадающем меню, на это есть подсказки, например

ЛЕВСИМВ — левые символы

ПРАВСИМВ — правые символы

ДЛСТР — длина строки

НАЙТИ — возвращает позицию, с которой подстрока или символ входит в строку.

КОНЕЦСТРОКИ возврат строки до конца

НАЧАЛОСТРОКИ — возврат строки в начало

Открывайте ячейку из которой надо вытащить часть текста, клацаете по тексту что бы курсор в тексте начал моргать, выделяете эту часть текста которую хотите утащить в другое место, щелкаете по выделенке ПКМ (правой кнопкой мышки) выбираете «копировать»

Переходите в окно куда нужно вставить, щелкаете в нем ЛКМ (левой кнопкой мышки) что бы активировать работу ввода данных в этой ячейки, следом щелкаете ПКМ, выбираете «вставить» и все.

Довольно сложный вопрос, но в Ексель можно сделать и такое, в этом редакторе есть подобные функции работы со строками.

Эти функции мы ищем в верхнем меню во вкладке «Формулы» — «Текстовые»:

Желательно, чтобы записи в ячейках были бы хоть как-то структурированы, например, если в ячейках сначала записано наименование товара, потом через запятую, в конце записи, цена товара, с такими ячейками будет работать несложно. Поработаем вот с этими ячейками, попробуем цену товара перенести в отдельные ячейки:

Текст у нас написан для этого отлично, цена товара стоит в конце строки, после слова «размеры» и запятой, поэтому мы воспользуемся функцией КОНЕЦСТРОКИ из вкладки «Текстовые» (см. выше). Открывается вот такое окошечко, в поле ТЕКСТ указываем столбец, в котором находятся наши ячейки, в поле НАЙТИ — слова, после которых текст надо переносить в отдельную ячейку.

Нажимаем ОК, получаем то, что хотели:

Теперь можно, используя тот же алгоритм, поработать с новыми ячейками с помощью функции НАЧАЛОСТРОКИ и получить число без рублей:

Как вытащить число или часть текста из текстовой строки в Excel

Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.

Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.

Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.

И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.

Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).

Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.

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

Вся трудность, в том чтобы извлечь код из текстовой строки.

Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.

В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.

Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.

Сложнее если нужные нам символы находятся в середине текста.

Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.

=ПСТР(текст; начальная_позиция; количество_знаков)

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.

Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

В нашем случае формула будет иметь вид:

Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.

=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])

Искомый_текст – обязательный аргумент. Текст, который требуется найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.

Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:

Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.

В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.

Как вытащить часть текста из ячейки excel?

Действительно можно столкнуться с ситуацией, когда необходимо из какой-то одной или несколько ячеек в программе эксель необходимо вытащить часть текста.

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

1. ПРАВСИМВ и ЛЕВСИМВ – данные функции наиболее часто используют для решения поставленного вопроса. Функция ПРАВСИМВ возвращает указанное количество символов справа, ЛЕВСИМВ слева.

2. ДЛСТР – это функция позволяет узнать количество символов строки.

3. НАЙТИ – находит в тексте положение определенного элемента или слова.

Именно с помощью применения данных четырех функций можно легко решить стоящую перед вами задачу. А теперь рассмотрим несколько простых примеров.

Пример №1.

Перед нами таблица, в которой указаны ссылка на разные статьи, нам необходимо убрать из каждой ссылки буквы: .html

Чтобы решить данную задачу, мы будем использовать две функции ДЛСТР и ЛЕВСИМВ. С помощью функции ДЛСТР будем определять длину строки, так как строки имеют у нас разную длину. А функцией ЛЕВСИМВ, будем оставлять нужное количество символов. Также заметим, что строчка .html состоит из 5 символов, т.е. в независимости какая длина строки, всегда нужно убирать пять элементов. И так в ячейке «С2» напишем формулу: =ЛЕВСИМВ(B2;ДЛСТР(B2)-5). После её скопируем в оставшиеся ячейки.

Пример № 2. Перед нами другая в таблица, в которой указана номенклатура товара, наша задача убрать из текста весовые параметры продукта, которые находятся после запятой.

В этом примере в отличие от первого, мы заранее не знаем сколько нужно отрезать символов, поэтому просто посчитать их количество, нам ничего не даст. Но мы знаем, что они все отделены запятой, поэтому будем использовать функцию «НАЙТИ», чтобы понимать, где запятая. Поэтому в ячейке «С2» пишем формулу: =ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)-1) И копируем её на оставшиеся ячейки.

Таким образом, каждая задача нетривиальная и вам придется решать какой набор функций использовать, чтобы получить нужный результат.

Примеры использования функции ЛЕВСИМВ в Excel работа с текстом

Функция ЛЕВСИМВ в Excel используется для получения части текста и возвращает заданное количество символов текстовой строки, при этом отсчет символов начинается слева.

Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?

Пример 1. В таблице Excel содержатся имена переменных вместе с текстом, определяющим их тип данных. Необходимо выделить только названия переменных и записать их в отдельный столбец.

Исходная таблица данных:

Для выделения первого символа, которым является искомое название переменной, используем формулу:

Второй равен 1 по умолчанию, поэтому функция возвращает первый символ. Аналогично выделим названия переменных из других строк. В результате получим:

Как видно на рисунке с помощью функции ЛЕВСИМВ получилось вытащить левую часть текста из каждой ячейки по одному (первому) символу.

Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?

Пример 2. В таблицу Excel подтягиваются данные из другого программного продукта. Одно из полей данных получило ошибку. Для обработки ошибки необходимо выделить ее код и описание из строки.

Выделим код ошибки с помощью следующей формулы:

• ЛЕВСИМВ(B5;НАЙТИ(«3»;B5)+1) – функция, выделяющая часть текстовой строки слева, количество символов в которой соответствуют порядковому номеру символа «3» (функция НАЙТИ выполняет поиск порядкового номера этого символа и возвращает соответствующее значение). Единица (+1) добавлена для выделения всего кода ошибки.
• 2 – количество символов, которые вернет функция ПРАВСИМВ, работающая аналогично рассматриваемой ЛЕВСИМВ (отличие – возвращает символы справа, а не слева строки).

В результате получим:

Выделим часть текста ошибки:

В данном случае удобнее использовать ПРАВСИМВ, которая является, по сути, зеркальным отражением ЛЕВСИМВ. Для определения второго аргумента используется запись ДЛСТР(B5)-НАЙТИ(«:»;B5)-1, определяющая позицию символа «u» в слове «unknown» (единица вычтена для удаления пробела).

В результате получим:

Округление чисел текстовыми функциями в текстовой строке

Пример 3. Создать функцию для округления дробных чисел до ближайших целых значений с использованием формул для работы с текстом.

Форма для округления числовых значений имеет следующий вид:

В ячейке B3 введена следующая сложная формула:

=5,ЛЕВСИМВ(A2+1,НАЙТИ(«.»,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(«.»,A2)-1)),»Число не введено»)’ >

1. Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
2. Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
3. Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(«,»;B2)-1), то есть целой части числа +1 по правилам округления.
4. Если

Как вытащить слово в отдельную колонку

Как вытащить число или часть текста из текстовой строки в Excel

Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.

Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.

Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.

И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.

Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).

Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.

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

Вся трудность, в том чтобы извлечь код из текстовой строки.

Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.

В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.

Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.

Сложнее если нужные нам символы находятся в середине текста.

Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.

=ПСТР(текст; начальная_позиция; количество_знаков)

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.

Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

В нашем случае формула будет иметь вид:

Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.

=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])

Искомый_текст – обязательный аргумент. Текст, который требуется найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.

Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:

Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.

В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.

Делим слипшийся текст на части

Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:

• ФИО в одном столбце (а надо — в трех отдельных, чтобы удобнее было сортировать и фильтровать)
• полное описание товара в одном столбце (а надо — отдельный столбец под фирму-изготовителя, отдельный — под модель для построения, например, сводной таблицы)
• весь адрес в одном столбце (а надо — отдельно индекс, отдельно — город, отдельно — улица и дом)
• и т.д.

Способ 1. Текст по столбцам

Выделите ячейки, которые будем делить и выберите в меню Данные — Текст по столбцам (Data — Text to columns) . Появится окно Мастера разбора текстов:

На первом шаге Мастера выбираем формат нашего текста. Или это текст, в котором какой-либо символ отделяет друг от друга содержимое наших будущих отдельных столбцов (с разделителями) или в тексте с помощью пробелов имитируются столбцы одинаковой ширины (фиксированная ширина).

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) — необходимо указать какой именно символ является разделителем:

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании «Иванов, Манн и Фарбер») не делился по запятой
внутри названия.

И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:

• общий — оставит данные как есть — подходит в большинстве случаев
• дата — необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
• текстовый — этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:

Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

• в Excel 2003 и старше — меню Сервис — Макрос — Редактор Visual Basic(Tools — Macro — Visual Basic Editor)
• в Excel 2007 и новее — вкладка Разработчик — Редактор Visual Basic (Developer — Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert — Module) и копируем туда текст вот этой пользовательской функции:

Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

• Txt — адрес ячейки с текстом, который делим
• Delimeter — символ-разделитель (пробел, запятая и т.д.)
• n — порядковый номер извлекаемого фрагмента

Способ 3. Разделение слипшегося текста без пробелов

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО «ИвановИванИванович»), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:

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