Как в excel сделать разделение строки на подстроки?

Формула Excel: разделение текста и чисел

Обзор

Формула выглядит сложной, но на самом деле механика довольно проста.

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

В этом случае мы предполагаем, что числа и текст объединены, и что число появляется после текста. Из исходного текста, который отображается в одной ячейке, вы хотите разделить текст и числа на отдельные ячейки, например:

Оригинал	Текст	Номер
Яблоки 30	Яблоки	30
персики 24	персики	24
апельсины12	апельсина	12
персики 0	персики

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

 = МИН (НАЙТИ ({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789")) 

Когда у вас есть позиция, чтобы извлечь только текст, используйте:

И, чтобы извлечь только число, используйте:

В первой формуле выше мы используем функцию НАЙТИ, чтобы определить начальную позицию числа. Для find_text мы используем константу массива {0,1,2,3,4,5,6,7,8,9}, это заставляет функцию FIND выполнять отдельный поиск для каждого значения в константе массива. Поскольку константа массива содержит 10 чисел, результатом будет массив с 10 значениями. Например, если исходный текст — «яблоки30», результирующий массив будет:

 {8,10,11,7,13,14,15,16,17,18} 

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

Затем функция MIN возвращает наименьшее значение в списке, которое соответствует положению первого числа , которое появляется в исходном тексте

По сути, функция НАЙТИ получает все числовые позиции, а MIN дает нам первую числовую позицию: обратите внимание, что 7 — это наименьшее значение в массиве, которое соответствует позиции числа 3 в исходном тексте

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

Эта часть формулы объединяет все возможные числа 0–9 с исходным текстом в B5. К сожалению, FIND не возвращает ноль, если значение не найдено, поэтому это просто умный способ избежать ошибок, которые могут возникнуть, если число не найдено.

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

Если исходный текст не содержит чисел, будет возвращена «фиктивная» позиция, равная длине исходного текста + 1. С этой фиктивной позицией формула LEFT выше по-прежнему будет возвращать текст, а формула RIGHT вернет пустую строку («»).

Деление строки

Допустим, у нас есть такая табличка:

И, например, нам нужно разделить имя + фамилию и поместить их в разные столбцы.

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «с разделителями», так как мы будем делить строку по пробелу;

На втором этапе, собственно, указываем что пробел в нашем случае будет разделителем строки;

И на третьем этапе указываем куда поместить данные;

Подтверждаем.

Результат:

Примечание:

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

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

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; )

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

Пример использования:

Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

Текстовые функции в Эксель

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

1. БАТТЕКСТ(Значение). Функция, необходимая для превращения ячейки числового формата в текстовый. Ее полезно использовать, если формула требует текстового значения, в то время как в ячейке число представлено в виде цифрового. С помощью данной функции можно конвертировать данные из одного типа в другой.
2. ДЛСТР(Значение). Эта функция позволяет определить длину строки и то, сколько символов находится в ней. Возвращает число, соответствующее количеству знаков, которые записаны в этой строке.
3. ЗАМЕНИТЬ(Старый текст, Начальная позиция, число знаков, новый текст). С помощью этой функции можно заменить один текст на другой, в качестве ориентира используя определенное количество знаков, начиная с позиции, которая указана пользователем.
4. ЗНАЧЕН(Текст). Эта функция совершает противоположную первому оператору операцию – значение текстового формата превращает в числовой.
5. ЛЕВСИМВ(Строка, Количество знаков). С помощью этой функции можно получить заданное пользователем количество символов строки, указанной человеком. При этом в учет берутся те знаки, которые располагаются слева.
6. ПРАВСИМВ(Строка, Количество знаков). Принцип работы этой функции аналогичный, только с ее помощью можно вернуть определенное количество знаков справа. То есть, узнать, какой будет часть строки, начиная с самого последнего символа.
7. НАЙТИ(текст для поиска, текст, в котором ищем, начальная позиция). С помощью этой функции можно получить позицию, на которой находится текст, заданный пользователем. Этот оператор можно использовать, только если регистр для нас важен. Если же нет разницы, какие буквы использовать: большие или маленькие, то есть аналогичная функция – ПОИСК. Также следует отметить, что эта функция будет возвращать исключительно первое вхождение, все последующие уже не берутся в учет. Для этого существуют другие функции.
8. ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция). Это очень интересная функция. В чем-то она схожа на оператор ЗАМЕНИТЬ, но имеет более широкий функционал. Если пользователь не указал последний аргумент, то замена осуществляется всех вхождений в тексте. Таким образом, это позволяет автоматизировать опции Excel «Заменить все».
9. ПОДСТРОКА(Текст, разделитель, номер). С помощью этой функции можно получить строку, которая была разделена с помощью разделителя.
10. ПСТР (Текст, Начальная позиция, Количество знаков). Это одна из самых главных функций, которую мы сегодня будем разбирать очень подробно. Она в чем-то имеет схожий принцип на ЛЕВСИМВ, только дает возможность начать поиск подстроки не с самого начала, а с определенной позиции.
11. СЦЕПИТЬ (Текст1, Текст2…). Это функция, позволяющая объединить несколько строк. Является некой заменой оператору &. Максимальное количество строк, которые можно соединить между собой – 30.

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

Пример использования текстовых функций в Эксель

Давайте опишем несколько практических применений текстовых функций. Для наглядности, мы представим работу функции ПОДСТРОКА и задачу, которую нужно решить. Первая колонка этой таблицы – полная строка. Вторая – значение, которое нам нужно найти в первой колонке. В третьем столбце перечислены формулы, с помощью которых это можно сделать.

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

А в этом примере мы попробуем разбить номер телефона на несколько частей.

Недостаток функции ПОДСТРОКА заключается в том, что требуется наличие разделителя, поэтому можно только отделять слова друг от друга или цифры в номере телефона.

Если нужно отделить одно слово от другого, то можно использовать разделитель в виде пробела. В таком случае надо открыть кавычку, поставить пробел, а потом закрыть кавычку в соответствующем аргументе.

Как найти числа в текстовом формате

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

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

• Числа выровнены по левому краю
• Если выделена группа ячеек с такими “числами”, то в строке состояния не будет подсказок по сумме, среднему чисел в выделенном диапазоне
• Апостроф стоит первым символом в строке формул этой ячейки

На скриншоте ниже пример как Excel автоматически выравнивает по левому краю текстовые значения и по правому краю числовые значения.

Конвертация текста в числа через меню ошибки

Если в левом верхнему углу ячеек с числовыми значениями появился зеленый треугольник, то:

• Выделите диапазон таких ячеек
• Кликните по иконке с желтым ромбом и восклицательным знаком
• В выпадающем списке выберите пункт меню “Преобразовать в число”

Как конвертировать текст в число с помощью смены формата ячейки

Есть еще один способ изменить формат значений текстовой ячейки на числовой. Сделать это можно с помощью смены формата ячейки на числовой. Как это сделать:

• Выделите левой клавишей мыши диапазон ячеек
• На панели инструментов перейдите на вкладку “Главная”
• Перейдите в подгруппу “Число” и в выпадающем списке выберите формат “Числовой”

Пример функция ПСТР для разделения текста на части в Excel

​ лишние пробелы и​. Пробелы учитываются.​ популярна среди пользователей,​. Но данная формула​ них. Открывается список​ в прошлый раз,​Как альтернативное действие, можно​ или больше количеству​

Примеры использования функции ПСТР в Excel

​ указанную нами в​«ПСТР»​ знаками.​ исходной строки. Функция​ номера картотеки в​ Если аргумент задан​В результате расчетов получим:​1 – номер начальной​Формула в примере ниже​ различные непечатаемые символы.​Текстовые функции​ объясняется тем фактом,​ указывает на пробел,​ последних применяемых операторов.​ нам нужно извлечь​ после выделения нажать​ символов в самом​ первом шаге​

Как разделить текст на несколько ячеек по столбцам в Excel?

​. Как видим, в​Аргумент​ ПСТР требует заполнить​ базе данных кадрового​ числом, превышающим количество​Пример 3. В таблице​ позиции символа извлекаемой​ заменяет 4 символа,​ Чтобы удалить все​

​ЛЕВСИМВ​

​ что многие юзеры,​ а нам нужен​ Так как среди​ наименование моделей без​

​ комбинацию клавиш​

• ​ длинном наименовании в​Примера 1​ этом окне число​«Начальная позиция»​ 3 аргумента:​
• ​ отдела;​ символов в строке,​ содержатся данные о​ подстроки (первый символ​
• ​ расположенные, начиная с​ непечатаемые символы из​и​

​ используя Excel, большее​ следующий символ после​ них нет наименования​ обобщающего названия. Трудность​Ctrl+C​ данном списке. Устанавливаем​ячейку.​ полей соответствует количеству​представлен в виде​Текст – исходные данные​

​и похожие другие задачи…​

​ будет возвращена вся​

​ сотрудниках в столбцах​

​ в исходной строке);​ седьмой позиции, на​ текста, необходимо воспользоваться​ПРАВСИМВ​ внимание уделяют математическим​ пробела, с которого​«ПОИСК»​ состоит в том,​.​

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

​Урок:​ аргументов данной функции.​ числа, которое указывает,​ (текстовое либо числовое​Умение быстро решать подобного​ часть строки начиная​ ФИО и дата​2 – номер последней​ значение «2013». Применительно​ функцией​

​возвращают заданное количество​

​ функциям, а не​ и начинается наименование​

​, то кликаем по​

​ что если в​Далее, не снимая выделения,​«50»​Мастер функций в Эксель​В поле​ с какого знака​ значение).​ рода базовые задачи​

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

​ к нашему примеру,​

​ПЕЧСИМВ​ символов, начиная с​ текстовым. При использовании​ модели. Поэтому к​ пункту​ предыдущем примере обобщающее​ щелкаем по колонке​. Название ни одного​Но, естественно, в практических​«Текст»​ по счету, начиная​Начальная_позиция – порядковый номер​ в Excel пригодиться​ аргументом позиции. В​ в котором будет​ подстроки.​ формула заменит «2010»​.​

​ начала или с​

Как посчитать возраст по дате рождения в Excel?

​ данной формулы в​ существующим данным в​«Другие функции…»​ наименование для всех​ правой кнопкой мыши.​ из перечисленных смартфонов​ целях легче вручную​вводим координаты ячейки,​ слева, нужно производить​ символа от начала​

​ каждому офисному сотруднику.​

​ дробных числах, используемых​ отображаться фамилия сотрудника​Аналогичным способом выделим номера​ на «2013».​

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

​ и его возраст​ месяца и годы​Заменяет первые пять символов​ПОВТОР​

Особенности использования функции ПСТР в Excel

​ считается за символ.​ операторами функциональность её​

​«Начальная позиция»​

​Открывается окно​

​возвращает заданное количество​Excel предлагает большое количество​

​без кавычек.​

​«Текстовые»​

​ разное («компьютер», «монитор»,​«Значения»​После того, как данные​ группы данных использование​ в поле и​«2»​ взятых из середины​ ПСТР реализуются решения​ синтаксис:​

​Для возврата строки с​

1. ​ начинается с 4-го​Вот и все! Мы​ первый аргумент функции,​ символов, начиная с​ функций, с помощью​В поле​
2. ​выделяем наименование​ «колонки» и т.д.)​.​ введены, жмем на​ функции будет вполне​ кликаем левой кнопкой​и т.д. В​ текста в исходных​ выше описанных задач:​=ПСТРБ(текст;начальная_позиция;число_байтов)​
3. ​ фамилией и текущим​ символа в каждой​ познакомились с 15-ю​ а количество повторов​ указанной позиции. Пробел​ которых можно обрабатывать​
4. ​«Количество знаков»​«ПОИСК»​ с различным числом​После этого вместо формул​ кнопку​

exceltable.com>

Несколько особенностей использования ПСТР в Excel

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

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

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

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

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

Этот аргумент также обязательный. Его задача – задать стартовую точку отсчета. Являет собой обязательно целое число, которое относится к положительным числам. То бишь, минимальное значение – 1. Если оказывается, что в аргумент была передана дробная часть, она отсекается.
Число знаков. И этот аргумент является обязательным. Таким образом, все аргументы, используемые в этой функции, необходимо использовать. Здесь есть такой нюанс. Если оказывается, что было в него передано число, которое больше длины строки, то возвращается вся строка. 

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

Напоминаем, что синтаксис следующий:

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

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

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

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

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

Фамилия из ФИО в Excel: как ее извлечь

Пример возьмем следующий. Допустим, перед нами фамилия, имя и отчество некоего человека: Завьялова Анна Петровна. Никто конкретно не имеется в виду, просто случайно выбранная ФИО (фамилия, имя, отчество). Задача для программиста будет следующая: выделить с помощью программного кода из ФИО одну лишь фамилию.

Казалось бы, в чем проблема? Что, разве не видно, что фамилия указанного человека – Завьялова? Да, видно тем, кто умеет читать. Но компьютер не умеет читать. Он не может смотреть как мы и не может думать, как мы. Он умеет только считать. Как «сосчитать» из ФИО одну только фамилию? В этом-то и будет заключаться задача, которую мы ставим перед гипотетическим программистом.

Для решения задачи давайте использовать табличный редактор Microsoft Excel, у которого также есть разные аналоги, например, таблицы  Liber Office и др. Итак, допустим, мы – программисты. Сейчас мы попробуем с помощью программного кода извлечь фамилию из ФИО в Excel.

На новом чистом листе табличного редактора Excel в ячейку B2 вводим ФИО (Завьялова Анна Петровна) и выделяем ее желтым цветом для наглядности. Также размечаем таблицу наших последующих действий (рис. 1).

Рис. 1. Ввод ФИО в ячейку B1 и предварительная разметка листа табличного редактора Microsoft Excel.

В столбце с заголовком № п/п мы будем записывать цифры 1, 2, 3 и так далее – это предстоящие попытки извлечения фамилии из ФИО.

Далее в столбце Фамилия будут размещаться результаты наших попыток.

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

Как извлечь подстроку определенной длины

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

• Функция LEFT — извлечь подстроку слева.
• Функция ПРАВО — извлечь текст справа.
• Функция MID — для извлечения подстроки из середины текстовой строки, начиная с указанной вами точки.

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

Извлечь подстроку из начала строки (СЛЕВА)

Чтобы извлечь текст слева от строки, вы используете функцию Excel LEFT:

СЛЕВА(текст, )

Где текст адрес ячейки, содержащей исходную строку, и num_chars количество символов, которые вы хотите извлечь.

Например, чтобы получить первые 4 символа из начала текстовой строки, используйте следующую формулу:

=ЛЕВО(A2,4)

Получить подстроку с конца строки (ВПРАВО)

Чтобы получить подстроку из правой части текстовой строки, воспользуйтесь функцией Excel ПРАВИЛЬНО:

ПРАВИЛЬНО(текст, )

Например, чтобы получить последние 4 символа с конца строки, используйте следующую формулу:

=ВПРАВО(A2,4)

Извлечь текст из середины строки (MID)

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

По сравнению с двумя другими текстовыми функциями, MID имеет несколько иной синтаксис:

MID(текст, start_num, num_chars)

Помимо текст (исходная текстовая строка) и num_chars (количество символов для извлечения), вы также указываете start_num (отправная точка).

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

=СРЕДНЕЕ(A2,6,3)

Кончик. Вывод формул Right, Left и Mid всегда является текстом, даже если вы извлекаете число из текстовой строки. Если вы хотите оперировать результатом как числом, оберните формулу в функцию ЗНАЧЕНИЕ следующим образом:

= ЗНАЧЕНИЕ (СРЕДНЕЕ (A2,6,3))

Как найти подстроку в Excel

В ситуациях, когда вы не хотите извлекать подстроку, а хотите найти только ячейки, содержащие ее, вы используете функцию ПОИСК или НАЙТИ, как показано в приведенных выше примерах, но выполняете поиск внутри функции ЕСНИМ. Если ячейка содержит подстроку, функция поиска возвращает позицию первого символа, и если ISNUMBER получает любое число, она возвращает TRUE. Если подстрока не найдена, поиск приводит к ошибке, заставляя ISNUMBER возвращать FALSE.

IНОМЕР(ПОИСК(«подстрока«, клетка))

Предположим, у вас есть список британских почтовых индексов в столбце A, и вы хотите найти те, которые содержат подстроку «1ZZ». Чтобы это сделать, используйте эту формулу:

=ISNUMBER(ПОИСК(«1zz», A2))

Результаты будут выглядеть примерно так:

Если вы хотите вернуть собственное сообщение вместо логических значений ИСТИНА и ЛОЖЬ, вложите приведенную выше формулу в функцию ЕСЛИ:

=ЕСЛИ(IЧИСЛО(ПОИСК(«1zz», A2)), «Да», «»)

Если ячейка содержит подстроку, формула возвращает «Да», в противном случае — пустую строку («»):

Как вы помните, функция ПОИСК в Excel нечувствительна к регистру, поэтому вы используете ее, когда регистр символов не имеет значения. Чтобы ваша формула различала прописные и строчные буквы, выберите функцию НАЙТИ с учетом регистра.

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

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

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

Разобрать слитый текст на нужные составляющие может быть произвести при помощи:

1. Мастера разбора текстов;
2. Формулы;
3. Макросов VBA.

Как разделить текст в MS Excel

Многие знают, что для того, чтобы объединить текст в двух ячейках достаточно воспользоваться функцией СЦЕПИТЬ (CONCATENATE), однако, как быть, если необходимо не объединить, а наоборот разделить текст в ячейке? Если количество символов, которое необходимо отделить известно (не важно справа или слева), тогда можно воспользоваться функциями ЛЕВСИМВ (LEFT) или ПРАВСИМВ (RIGHT), в зависимости с какой стороны необходимо выделить определенное количество символов. Однако, как быть, если необходимо разделить ячейку в которой заведомо не известно количество символов, которые нужно отделить, а известно лишь сколько частей необходимо получить в результате операции

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

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

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

Первый способ — супер быстрый.

На самом деле в MS Excel существует встроенная возможность быстрого разделения текста в ячейке, если там присутствует или присутствуют разделитель/разделители (например, простой пробел или запятая). Причем таких разделителей может быть несколько, т.е. текст будет разделятся если в строке присутствует или пробел, или точка с запятой или запятая и т.д.

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

В англоязычной версии MS Excel данная команда звучит как «Text to Columns» вкладки «DATA».

После несложных подсказок мастера (на самом деле, в нашем примере после выбора разделителя — пробела можно смело жать «Готово»)

А вот и, собственно, результат.

Второй способ — с использованием формул.

В такой ситуации понадобится сочетание функций: ПОИСК (SEARCH) и ПСТР (MID). Для начала, с помощью первой находим пробел между словами (между фамилией и именем и именем и отчеством), а потом подключаем вторую для того, чтобы выделить необходимое количество символов. Грубо говоря, первой функцией определяем количество символов, а второй — уже разделяем.

Кроме того, поскольку разделительного знака в конце строки нет, то количество символов в последнем слове (нашем случае — отчестве) вычислить не удастся, но это не проблема, достаточно указать заведомо большее количество символов в качестве аргумента «число знаков» функции ПСТР, например, 100.

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

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

Поскольку информация о количестве необходимых символах получена, следующим этапом будет использование функции ПСТР (MID).

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

Теперь попробуем соединить промежуточные расчеты в одну формулу.

Если текст в ячейке необходимо разделить лишь на две части, то необходимо произвести поиск лишь одного пробела (либо другого разделителя, который находится между словами), а для разделения на 4 и больше частей формулу придется усложнить поиском 3го, 4го и т.д. разделителей.

Примеры использования функции ПСТР

А теперь давайте приведем один пример, как можно использовать функцию ПСТР на практике. Для начала нужно понять, что количество байтов, которые занимает один символ, зависит от языков. Если кодировка в языке однобайтовая, то и один символ занимает ровно один байт. В таком случае нет разницы, какую формулу использовать: ПСТР или ПСТРБ.

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

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

Сама таблица выглядит следующим образом:

5

В этом случае нужно применить формулу массива, а именно такую.

6

Какие аргументы использовались в этом случае?

1. А2:А10. Здесь перечисляется набор ячеек, представление дат в которых выполнено в текстовой форме. Из них и будет доставаться день. 
2. 1 —  это число, обозначающее первоначальную позицию, с которой будет осуществляться извлечение. 
3. 2 – это последняя позиция.

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

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

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

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

=ПСТР(A2:A10;4;2)

=ПСТР(A2:A10;7;4)

В результате, у нас получается следующая таблица. 

7

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

Таким образом, возможностей у функции ПСТР огромное количество. Ее можно использовать для обработки огромных массивов информации. А поскольку каждый год количество данных, которые нужно эффективно анализировать, постоянно увеличивается, необходимо искать качественные способы автоматизации. И хотя некоторые считают, что Эксель несколько устарел, в своей нише альтернатив этой программе нет. Хотя бы потому, что зная несколько формул, можно добиваться почти любого функционала электронных таблиц. Больших успехов вам и легкости в освоении этого ремесла.

Заключение

Деление – достаточно простая базовая операция Эксель, осуществляемая при помощи формул, в которых используется знак деления (““), а в качестве делимого и делителя могут выступать как цифры, так и адреса ячеек с числовыми данными. Также можно воспользоваться функцией ЧАСТНОЕ. Но в этом случае нужно помнить, что результат ее использования – это число без остатка, округляемое до меньшего по модулю ближайшего целого.

• В норвегии приняли закон о фотошопе

    

• Для сертификата которым подписано сообщение не настроены параметры подписи 1с диадок

    

• Как в excel сохранить в dbf формате

    

• Как заработать на adobe flash

    

• Меняется формат при сохранении в экселе