Формула в excel расцепить

Функция расцепить в excel примеры

Функция СЦЕПИТЬ в Excel, включая примеры

​Смотрите также​​ до каждой запятой.​ скобки либо другие​ должна содержать разделенная​ этой ячейки отделить​ Variant​ и прочих энтузазистов​ слова, конкретные буквы,​Здесь действуем немного​Эту формулу писать​ в статье «Пробел​ статье «Объединить слова​Далее​ значение, что и​ лишней кавычкой, так​ форель составляет 32​=СЦЕПИТЬ(«Популяция рек для «;A2;»​Примечание:​Благодарю​ разделительные знаки. Например,​ строка.​

​ целые числовые значения.​​x = Split(Txt,​​ прошу не шуметь​ числа в ячейках.​ по другому принципу,​ долго, а если​ в Excel» тут.​

​ из разных ячеек​​.​ =CONCATENATE(A1,B1). Во многих​ как пропущена запятая​ на километр».​ «;A3;» составляет «;A4;»​ Мы стараемся как можно​Manyasha​ это могут быть​С первым аргументом ПСТР​ Пример таких неправильно​ Delimiter)​ — может автор​

​ Какими способами это​​ т.к. функция «СЦЕПИТЬ»​

​ ячеек больше, то​

​Можно в формуле​ в текст Excel».​Выберите​

​ случаях воспользоваться оператором​

​ между текстовыми аргументами.​

​ на километр.»)​​ оперативнее обеспечивать вас​

​:​ пробелы если нужно​ все понятно –​ импортированных данных отображен​If n >​

​ сам определиться. ​​ сделать, смотрите в​

​ работает только со​ — напряжно. Но,​ писать слова, а​ В этой же​

​разделители​ амперсанда проще и​Числа не нужно заключать​Объединяет строку в ячейке​=СЦЕПИТЬ(В2;» «;C2)​ актуальными справочными материалами​Pav777​

​ разделить строку на​

​ ниже на рисунке:​

​ статье «Как посчитать​

​ строками. Вот наш​

​ любимая Excel, опять​

​ цифры вставлять, указывая​

​ статье приведена формула,​

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

​ В2, пробел и​Имя аргумента​ на вашем языке.​, а вкладка Данные-Текст​ слова и т.п.​ ячейку A3. Второй​Сначала определим закономерность, по​

​: Могзи набекрень: таки​ количество символов в​ столбец.​ дает нам способ​ адрес ячейки с​

​ как преобразовать в​

​ Например,​ строки с помощью​Слова расположены впритык друг​ значение в ячейке​Описание​ Эта страница переведена​

​ по столбцам (разделитель​

​В данном примере функция​ аргумент мы вычисляем​ которой можно определить,​ = x(n -​ да или таки​ ячейке Excel».​В ячейку А12 пишем​ сделать всё быстрее.​

​ ними. Тогда при​ Excel имя и​

​запятую​ функции СЦЕПИТЬ.​ к другу.​ С2. Результат: «Виталий​​текст1​​ автоматически, поэтому ее​ запятая) не подходит?​ НАЙТИ во втором​

Распространенные неполадки

​ с помощью функции​

​Есть в Excel​ формулу. Если сделаем​ Используем &. В​ изменении цифры, они​ отчество в инициалы.​и​См. дополнительные сведения об​Если не поместить пробелы​ Токарев».​

​ (обязательный)​ текст может содержать​

​TimSha​ аргументе определяет положение​

​ НАЙТИ(«]»;A3)+2. Она возвращает​ разных категорий, несмотря​Else​ ниже. ​ логические функции. С​ как в предыдущем​ ячейке А8 пишем​ будут меняться и​Объединить данные в ячейках​

​пробел​ использовании операторов вычислений.​ между текстовыми элементами,​=СЦЕПИТЬ(С2;», «;В2)​Первый элемент для объединения.​

​ неточности и грамматические​​: Двнные — Текст​​ относительно первой закрывающейся​ очередной номер символа​ на то, что​Substring = «»​

​Тема — «обратная​ их помощью можно​

​ примере (выделим диапазон​ формулу так. Ставим​​ в предложении. Формула​​ Excel​. Данные можно предварительно​

Рекомендации

​Для объединения и форматирования​

​ в строке результата​

​Объединяет текст в ячейке​ Это может быть​

​ ошибки. Для нас​ по столбцам -​ скобки. А в​ первой закрывающейся квадратной​

​ они находятся в​End If​ функция функции -​ посчитать не все​ и поставим пробел​ знак «равно», затем​ получится такая =СЦЕПИТЬ(«Сегодня»;»​.​ просмотреть в окне​

​ строк используйте функцию​ они будут отображаться​

​ C2, строку, состоящую​ текстовое значение, число​ важно, чтобы эта​

​ Разделитель запятая.​ третьем аргументе эта​ скобки в строке.​ одной и той​End Function​

​ сцепить»;​ ячейки, а только​ после &), то​ выделяем все ячейки​ «;B1)​У нас такая​

​Образец разбора данных​

Разделение текста на столбцы с помощью мастера распределения текста по столбцам

​ слитно. Добавьте пробелы​ из запятой и​ или ссылка на​ статья была вам​Pav777​

​ же функция вычисляет​ И к этому​ же строке. В​

​Часто приходится оптимизировать структуру​​вопрос — «функция​​ нужные. Или написать​​ получится не та​​ со словами, которые​

​Вместо слова «СЦЕПИТЬ»​​ таблица с данными.​.​​Функция ТЕКСТ преобразует числовое​​ в формулу с​​ пробела, и значение​​ ячейку.​​ полезна. Просим вас​

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

​Нажмите кнопку​​ значение в текст​​ функцией СЦЕПИТЬ. Это​

​ в ячейке B2.​​текст2. ​​ уделить пару секунд​Manyasha​ текста в строке​

​ еще число 2,​​ интересуют только числа,​​ в Excel. Некоторые​ps Экстрасенсов, телепатов​ слова, числа (например​

​ нужна. В ней​​ предложение. В нашем​​ ставить знак «&»​

Функция «СЦЕПИТЬ» в Excel.

обратная функция функции — сцепить

​ исходные и разделенные​​Теперь скопируйте эту формулу​
​ ячеек. Ведь выполнять​ ячейке нужен результат​ ячейке (0609), а​
​ «. Нажимаем F9.​ Пишем после знака​ смотрите в статье​

​Готово​​ текст, содержащийся в​
​Вместо ожидаемого результата отображается​
​ что и в​

​вида​​ одну.​ ДОВОД ?​ разделенной строке. С​ строки имеют разную​
​ вдоль целого столбца:​ поиск по текстовой​ или в двух,​
​ надо (06)(09).​ Получилась такая формула.​
​ «равно» слово «СЦЕПИТЬ»​ «Добавить текст в​Получилось такая формула =СЦЕПИТЬ(A1;B1)​.​ одной ячейке, можно​

​ значение ошибки #ИМЯ?.​​ предыдущем примере, но​Виталий​Важно:​(внимание, =правсимв(С1;5) ответ​
​ помощью такой гибкой​ длину и разное​Выборка чисел из строк​
​ строке, которая содержится​ на всякий случай​
​Спасибо​Осталось написать после знака​ и фигурные скобки​ ячейки с текстом​ В ячейке C3​

​Рассмотрим,​ разделить на несколько.​Ошибка #ИМЯ? обычно означает,​

​ с помощью оператора​​Токарев​

​ В Excel 2016, Excel Mobile​​ будет тот же.​ формулы можно делать​ количество символов. Именно​ в отдельные ячейки.​ в ячейке ¬–​ оба варианта.​
​Marchuk​ «равно» слово «СЦЕПИТЬ»​ меняем на круглые.​ Excel».​ написано так.​

​как объединить содержимое ячеек​​Выделите ячейку или столбец​ что для​

​&​​речная форель​ и Excel Online​
​ но мне надо​ выборку разной длинны​
​ поэтому мы называли​​
​ это также распространенная​Да, и подойдёт​
​: =ЛЕВСИМВ()​ и заменить фигурные​ Нажимаем «Enter». Формула​В Excel можно​Между словом и числом​
​ Excel в предложение,​
​ с текстом, который​
​текстового​
​(амперсанд) вместо функции​

Как разделить текст по ячейкам формула в Excel

​Fourth​ эта функция заменена​ именно отнять 2​ разделенного текста из​ такую формулу –​Функция ПСТР возвращает текстовое​ потребность пользователей Excel.​ только для Вашего​=ПРАВСИМВ()​ скобки круглыми. Нажимаем​ стала такой.​ из даты извлечь​ нет пробела. Исправляем​ составить предложение из​ вы хотите разделить.​аргумента не хватает​ СЦЕПИТЬ. Результат: Fourth​Pine​ функцией СЦЕП. Функция​ левых а не​ разных длинны исходных​ гибкой, в начале​ значение содержащие определенное​ Их мы и​

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

​ примера, если чисел​=ПСТР()​ «ОК». Формула стала​=СЦЕПИТЬ(«Функция «;»»»СЦЕПИТЬ»» «;»в​ только месяц, год​ так – добавляем​ значений нескольких ячеек.​На вкладке​ кавычек.​ & Pine.​32​ СЦЕПИТЬ пока еще​ показать 5 правых)​ строк.​

​ статьи. Она подходит​ количество символов в​ будем использовать для​ будет больше ещё​Z​ такой =СЦЕПИТЬ(«Мама «;»мыла​ «;»Excel. «)​ или число. Какими​ в формулу пробел​Можно составить предложение​Данные​Действие​Проблема​Формула​ доступна для обратной​Сасибо​Pav777​ для любых условий​ строке. Аргументы функции:​ разделения строк.​

​ ДЛСТР надо использовать.​: Могзи набекрень: таки​

​ «;»раму. «)​Получилось так.​

​ формулами это сделать,​ (» «). Получилась​

​ из горизонтальных и​

Описание формулы для разделения текста по ячейкам:

1. ​ совместимости, однако рекомендуем​Makfromkz​: Здравствуй, форум.​
2. ​ при решении подобного​Первый аргумент – это​Допустим на лист Excel​Serge​
3. ​ да или таки​Получилось так.​Если нужно​ смотрите в статье​

​ такая формула =СЦЕПИТЬ(A1;»​ вертикальных ячеек, из​Текст по столбцам​Вместо функции СЦЕПИТЬ используйте​Кавычки отображаются в строке​=СЦЕПИТЬ(«Популяция рек для «;A2;»​ использовать вместо нее​: ответ звучит так:​Есть строка следующего​ рода задач. Гибкость​ ссылка на ячейку​ были импортированные данные​: Боюсь автор нас​ нет надо из​Еще один способ заполнить​соединить слова в Excel​ «Месяц из даты​ «;B1). Предложение получилось​ текста или из​.​ знак & (амперсанд).​ результата.​ «;A3;» составляет «;A4;»​

​ функцию СЦЕП. В​ПСТР («САДОВОД»;2) =​ вида (выгрузка с​ придает ей сложная​ с исходным текстом.​ из другой программы.​ покинул. ​ ниже. ​ бланк, таблицу данными​ через косую черточку​ Excel» здесь.​ такое.​ текста и цифр.​В​С помощью оператора &​Используйте точку с запятой​ на километр.»)​ последующих версиях Excel​ «ДОВОД»​ сайта-аукциона):​ комбинация из функций​Второй аргумент – это​ Из-за несовместимости структуры​Alex_ST​Тема — «обратная​ из разных ячеек​

​, то формулу напишем​Как сцепить слова в​Какие бывают пробелы ещё,​Например, можно заполнить​мастере распределения текста по​ можно объединять текстовые​ для разделения текстовых​Создает предложение, объединяя данные​ функция СЦЕПИТЬ может​Y-u-r-o-n​265572473,Fumiya Tanaka,Beautiful Town,Pluie/Noir​ НАЙТИ. Пользователю формулы​ позиция первого символа,​ данных при импорте​: в «Приёмах» на​ функция функции -​ описан в статье​ так. =СЦЕПИТЬ(А16;»/»;В16) Получилось​ строке Excel.​ где и для​ квитанцию на оплату,​ столбцам​ элементы, не прибегая​ элементов. Например: Excel​ в столбце А​ быть недоступна.​: интуиция мне подсказывает​ Recordings,PNR005,»12″», Ltd, Num,​ достаточно определить закономерность​

Расцепить текст из строки (Формулы/Formulas)

​ с которого должна​​ некоторые значение из​
​ описана функция:​ сцепить»;​ «Как заполнить бланк​
​ так.​В ячейке А7 пишем​ чего они, как​ др., взяв данные​
​установите переключатель​ к функции.​
​ отобразит результат функции​

​ с остальным текстом.​​Синтаксис:​​ что должна быть​​ Blu»,6969799,For Sale,13.4,2015-09-25 02:26:10,absolutely​ и указать их​ начинаться разделенная строка.​

​ разных категорий были​​Function Substring(Txt, Delimiter,​вопрос — «функция​ в Excel».​

​Как сцепить слова в​​ формулу =СЦЕПИТЬ(A6;» «;B6;»​​ поставить пробел или​​ из таблицы Excel.​​с разделителями​​Например, =A1 & B1​ =СЦЕПИТЬ («Привет, «»мир!»)​ ​ Результат: «Популяция рек​

Excel . «обратная» функция =СЦЕПИТЬ(

​СЦЕПИТЬ(текст1;[текст2];…)​
​ функция​
​ new,Mint (M),Mint (M),N,,0,0,0.0,​
​ в параметрах функций:​Последний аргумент – это​
​ внесены в одну​ n) As String​ обратная функции расцепить».​В Excel можно​
​ столбце Excel.​ «;C6;» «;D6)​ заменить его, смотрите​ Смотрите пример в​и нажмите кнопку​ возвращает то же​
​ как «Привет,»мир!» с​

​ для вида речная​​Ниже приведены примеры.​
​=ЛЕВСИМВ​Необходимо расцепить данные​

​ будут это квадратные​​ количество символов, которое​ ячейку. Необходимо из​Dim x As​
​ps Экстрасенсов, телепатов​

Функция расцепить в Excel

Одна из самых популярных операций в Exel по работе с текстом — это объединение текстовых строк. Например, объединение нескольких слов в одну строку. На практике в процессе рутинной работы с Excel часто приходиться объединять Имена и Фамилии сотрудников в одну ячейку.

Как сцепить два текста в один с помощью и без функции Excel?

В примере показанным на рисунке таблица содержит первые два столбца:

1. Имя — ячейки в этой колонке содержат текстовые данные полученные из базы картотек кадрового отдела.
2. Фамилия — в данном столбце находиться только фамилии сотрудников.

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

Для этого необходимо попарно объедини тексты из ячеек столбцов таблицы в общие строки.

На рисунке проиллюстрированный пример решения данной задачи. Для этой цели в примере должен использоваться оператор «&» (END вместо оператора «+»), который позволяет объединять значения ячеек в формулах:

Как показано выше на рисунке с помощью символа «&» можно соединить любые текстовые значения из разных ячеек без использования функции: =СЦЕПИТЬ(). В этом примере объединены значения ячеек A2 и B2 с добавленным между ними символом пробела (указанный в формуле между кавычками как текстовое значение).

Для объединения текстов в одну строку программа Excel предлагает встроенную функцию=СЦЕПИТЬ(A2;» «;B2), которая объединяет текстовые значения в одну строку. Однако на практике мы не рекомендуем использовать функцию СЦЕПИТЬ, а в место нее просто применять в формулах оператор &, так как она имеет ряд своих недостатков.

3 главных недостатка функции СЦЕПИТЬ по сравнению с оператором &:

1. Ограниченное максимальное количество объединяемых значений ячеек до 255.
2. Функция объединяет только текстовые значения, а оператор & объединит любые значения ячеек.
3. Функция требует немного больше затрат времени и ресурсов как у программы Excel (в процессе вычислений с использованием сложных формул), так и у пользователя (в процессе ввода функции в строку формул).

Интересный факт! В Excel нет обратной функции РАСЦЕПИТЬ и нет специального символа оператора. Для решения такой задачи применяются формулы с комбинациями текстовых функций. Например маркером для расцепления текста послужит символ пробела » » , а дальше:

• чтобы расцепить только имя используем формулу: =ЛЕВСИМВ(C2;ПОИСК(» «;C2;1))
• чтобы расцепить только фамилию — формула: =ПРАВСИМВ(C4;ПОИСК(» «;C4;1)+1)

Фактически функция СЦЕПИТЬ устарела, но все еще доступна пользователям в новых версиях программы Excel.

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

Добрый день уважаемый читатель!

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

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

Мастер разбора текстов

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

Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:

• С разделителями – это когда существует текст или символ, который условно будет отделять будущее содержимое отдельных ячеек;
• Фиксированной ширины – это когда при помощи пробелов в тексте имитируется столбики одинаковой ширины.

1. Вторым шагом, в нашем примере, указываем символ, выполняющий роль разделителя. В случаях, когда в тексте идут подряд пару разделителей, несколько пробелов, к примеру, то установка флажка для пункта «Считать последовательные разделители одним» укажет для Excel принимать их за один разделитель. Дополнительное условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не делить (к примеру, название фирмы «Рудольф, Петер и Саймон»);
2. Последним шагом, для уже разделённых столбиков, нужно указать в диалоговом окне мастера, предварительно выделив их, выбрать необходимый формат получаемых данных:

• Общий – не проводит изменения данных, оставляя их в первоначальном виде, будет оптимальным выбором в большинстве случаев;
• Текстовый – данный формат, в основном, необходим для столбиков с числовыми значениями, которые программа в обязательном порядке должна интерпретировать как текст. (К примеру, это числа с разделителем по тысяче или номер пластиковой карточки);
• Дата – этот формат используется для столбиков с датами, кстати, формат самой даты можно выбрать в выпадающем списке.

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

Рассоединяем текст с помощью формул

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

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

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

• Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(» «;B2;1), а для второго подойдет: =ПОИСК(» «;B2;C2+1);
• Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(» «;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(» «;B2;1)+1;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1) -ПОИСК(» «;B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1)+1;100).

В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

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

Выдергиваем слова с помощью макросов VBA

Рассмотрим два способа разделить текст в ячейке:

1. Выдергиваем отдельные слова по разделителю;
2. Делим текст без пробелов.

Способ №1.

Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

Как разделить текст по ячейкам формула в Excel

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

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

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

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

В ячейку B3 введите следующую формулу:

Теперь скопируйте эту формулу вдоль целого столбца:

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

Описание формулы для разделения текста по ячейкам:

Функция ПСТР возвращает текстовое значение содержащие определенное количество символов в строке. Аргументы функции:

1. Первый аргумент – это ссылка на ячейку с исходным текстом.
2. Второй аргумент – это позиция первого символа, с которого должна начинаться разделенная строка.
3. Последний аргумент – это количество символов, которое должна содержать разделенная строка.

С первым аргументом ПСТР все понятно – это ссылка на ячейку A3. Второй аргумент мы вычисляем с помощью функции НАЙТИ(«]»;A3)+2. Она возвращает очередной номер символа первой закрывающейся квадратной скобки в строке. И к этому номеру мы добавляем еще число 2, так как нам нужен номер символа после пробела за квадратной скобкой. В последнем аргументе функция вычисляет какое количество символов будет содержать разделенная строка после разделения, учитывая положение квадратной скобки.

Обратите внимание! Что в нашем примере все исходные и разделенные строки имеют разную длину и разное количество символов. Именно поэтому мы называли такую формулу – гибкой, в начале статьи. Она подходит для любых условий при решении подобного рода задач. Гибкость придает ей сложная комбинация из функций НАЙТИ. Пользователю формулы достаточно определить закономерность и указать их в параметрах функций: будут это квадратные скобки либо другие разделительные знаки. Например, это могут быть пробелы если нужно разделить строку на слова и т.п.

В данном примере функция НАЙТИ во втором аргументе определяет положение относительно первой закрывающейся скобки. А в третьем аргументе эта же функция вычисляет положение нужного нам текста в строке относительно второй открывающийся квадратной скобки. Вычисление в третьем аргументе более сложное и оно подразумевает вычитание одной большей длинны текста от меньшей. А чтобы учитывать еще 2 пробела следует вычитать число 3. В результате чего получаем правильное количество символов в разделенной строке. С помощью такой гибкой формулы можно делать выборку разной длинны разделенного текста из разных длинны исходных строк.

Как разделить текст в excel с помощью формулы

Раннее мы рассматривали возможность разделить текст по столбцам на примере деления ФИО на составные части.

Для этого мы использовали инструмент в Excel «Текст по столбцам».

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6) [Eugene Avdukhov, Excel Для Всех]

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

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

Содержание

• 1 Пример 1. Делим текст с ФИО по столбцам с помощью формул
• 1.1 Приступаем к делению первой части текста — Фамилии
• 1.2 Приступаем к делению второй части текста — Имя
• 1.3 Приступаем к делению третьей части текста — Отчество
• 2 Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

Пример 1. Делим текст с ФИО по столбцам с помощью формул Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию ПСТР и НАЙТИ, которую мы рассматривали в прошлых статьях.

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

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

Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу

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

Значит на необходимо поменять третий аргумент в функции НАЙТИ — начальная позиция — то есть позиция с которой функция будет искать искомый текст.

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

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

Далее протягиваем формулу и получаем позиции 1-го и 2-го пробела.

Приступаем к делению первой части текста — Фамилии

Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:

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

• текст — это ФИО, в нашем примере это ячейка A2;
• начальная_позиция — в нашем случае это 1, то есть начиная с первой буквы;
• число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.

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

Приступаем к делению второй части текста — Имя

Снова используем функцию =ПСТР(текст- начальная_позиция- число_знаков), где

• текст — это тот же текст ФИО, в нашем примере это ячейка A2;
• начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1;
• число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2

Получаем итоговую формулу:

Приступаем к делению третьей части текста — Отчество

И снова функция =ПСТР(текст- начальная_позиция- число_знаков), где

• текст — это тот же текст ФИО, в нашем примере это ячейка A2;
• начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1;
• — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50

Видео: Формулы в Эксель

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

Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(» «-A2-1), а расчет второго пробела в ячейке I2 — НАЙТИ(» «-A2-H2+1) .

Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)

Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается H2 или I2 и меняем их на формулы в этих ячейках, аналогично с Именем и Фамилией

• Фамилия =ПСТР(A2-1-H2-1) получаем =ПСТР(A2-1-НАЙТИ(» «-A2-1)-1)
• Имя =ПСТР(A2-H2+1-I2-H2) получаем =ПСТР(A2-НАЙТИ(» «-A2-1)+1; НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)-НАЙТИ(» «-A2-1))
• Отчество =ПСТР(A2-I2+1-50) получаем =ПСТР(A2-НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)+1-50)

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

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

Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

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

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

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

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

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

Теперь основная суть приема.

Шаг 1. В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки

То есть ищем запятую, в тексте, начиная с позиции 1

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

1. Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
2. Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.

Пропишем формулу и протянем ее вниз.

Шаг 3. Находимо позиции всех остальных разделителей. Для этого формулу нахождения второго разделителя (шаг 2) протянем вправо на то количество ячеек, сколько всего может быть отдельно разбитых значений с небольшим запасом. Получим все позиции разделителей. Там где ошибка #Знач означает что значения закончились и формула больше не находит разделителей. Получаем следующее

Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.

Начальная позиция у нас 1, количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз

Шаг 5. Находимо второе слово так же с помощью функции ПСТР в ячейке P1

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

Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1 Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.

Шаг 6. Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.

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

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

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

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

а первого текста как

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

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

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

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

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

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

Оба варианта выложу для скачивания.

1. Скачать пример: Как разделить текст по столбцам с помощью функции_1.xlsx (исправлено: доп поле)
2. Скачать пример: Как разделить текст по столбцам с помощью функции_2.xlsx (исправлено: заведомо большое число)

(Пока оценок нет)