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

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

Способ 1: деление числа на число

Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) – «/» .


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

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

Как известно, деление на 0 является некорректным действием. Поэтому при такой попытке совершить подобный расчет в Экселе в ячейке появится результат «#ДЕЛ/0!» .

Способ 2: деление содержимого ячеек

Также в Excel можно делить данные, находящиеся в ячейках.


Можно также комбинировать, в качестве делимого или делителя используя одновременно и адреса ячеек и статические числа.

Способ 3: деление столбца на столбец

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


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

Способ 4: деление столбца на константу

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


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

Способ 5: деление столбца на ячейку

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


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

Способ 6: функция ЧАСТНОЕ

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

Посмотрим применение данной функции на примере.


После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.

Эту функцию можно также ввести вручную без использования Мастера. Её синтаксис выглядит следующим образом:

ЧАСТНОЕ(числитель;знаменатель)

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

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

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

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

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

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

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

  1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 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. (детальнее в статье « »).

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

Function Substring(Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) If n > 0 And n - 1 <= UBound(x) Then Substring = x(n - 1) Else Substring = "" End If End Function

Function Substring (Txt , Delimiter , n ) As String

Dim x As Variant

x = Split (Txt , Delimiter )

If n > 0 And n - 1 <= UBound (x ) Then

Substring = x (n - 1 )

Else

Substring = ""

End If

End Function

Теперь эта функция появилась в «Мастер функций» в разделе «Определенные пользователем». Синтаксис созданной функции очень прост и выглядит так:

SUBSTRING (_Txt_;_Delimeter_;_n_), где:

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

Пример:

Рассмотрим, как возможно изъять текст по разнообразным символам-разделителям. Для удобства и лучшей автоматизации не забывайтесь значения формулы .
Способ №2.

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

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

Function CutWords(Txt As Range) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len(Txt) If Mid(Txt, i, 1) Like "" And Mid(Txt, i + 1, 1) Like "" Then Out = Out & Mid(Txt, i, 1) & " " Else Out = Out & Mid(Txt, i, 1) End If Next i CutWords = Out End Function

Function CutWords (Txt As Range ) As String

Dim Out $

If Len (Txt ) = 0 Then Exit Function

Out = Mid (Txt , 1 , 1 )

For i = 2 To Len (Txt )

If Mid (Txt , i , 1 ) Like "" And Mid (Txt , i + 1 , 1 ) Like "" Then

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

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

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

Способ 1: через объединение ячеек

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

  1. Четко представьте, как таблица должна будет выглядеть в итоге.
  2. Выделите две ячейки над теми, которые предполагаются быть раздельными.
  3. На панели инструментов нажмите по кнопке «Объединить и поместить в центр». Расположена она в категории «Выравнивание».

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

  1. Выделите диапазон ячеек, которые входят в вашу таблицу.
  2. На панели инструментов нажмите по кнопке «Границы».
  3. Из выпадающего меню выберите пункт «Все границы».

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

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

Способ 2: разделение объединенных ячеек

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

  1. Запустите программу.
  2. Зажав левую кнопку мыши на наименованиях столбцов (латинские буквы, находящиеся над первой строкой таблицы), выделите два столбца.
  3. Находясь на главной вкладке приложения, нажмите по стрелочке рядом с кнопкой «Выравнивание».
  4. Из появившегося выпадающего меню выберите строку «Объединить по строкам».

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

  1. Кликните по той ячейке, которую желаете разделить на две. Важно, чтобы она находилась в столбце, который получился после объединения.
  2. Перейдите на вкладку «Главная».
  3. В группе инструментов «Выравнивание» нажмите по кнопке «Объединить и поместить в центр».
  4. В появившемся выпадающем меню выберите опцию «Отменить объединение ячеек».

Теперь вместо одной ячейки у вас две. Кстати, если вы думаете, как разделить данные ячейки в Excel на две, то, к сожалению, каких-то специальных инструментов для этого не предусмотрено. Вам необходимо будет пользоваться обычным «Копировать»/«Вставить». А лучше сначала построить структуру таблицы и уже потом вписывать в нее значения.

Способ 3: разделение по диагонали с помощью форматирования

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

  1. Откройте книгу и перейдите на лист, в котором хотите поделить ячейку.
  2. Выделите ее.
  3. Нажмите затем правую кнопку мыши.
  4. В контекстном меню выберите опцию «Формат ячеек».
  5. Появится окно, в котором необходимо перейти на вкладку «Граница».
  6. Здесь располагается интерфейс ячейки с текстом «Надпись». В нижнем правом или левом углу находятся кнопки, после нажатия которых ячейка разбивается линией по диагонали справа налево или слева направо соответственно.
  7. После этого вы можете также выбрать тип линии и ее цвет.
  8. Задав все необходимые параметры, нажмите ОК.

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

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

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

  1. Выделите нужную ячейку.
  2. На вкладку «Вставка» нажмите по кнопке «Фигуры».
  3. Из выпадающего списка выберите из категории «Линии» одноименный объект.
  4. Зажав левую кнопку мыши, проведите линию от одного угла к другому.

После этого ячейка будет разбита на две по диагонали.

Вместо заключения

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

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

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

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6)

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

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

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

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

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

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

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

НАЙТИ(» «-A2-1)

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

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

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

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

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

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

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

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

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

ПСТР(A2-1-H2-1)

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

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

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

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

ПСТР(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 и протянем ее вниз на все строки

НАЙТИ(B1-A1-1)

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

НАЙТИ($B1-$A1-D1+1)

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

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

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

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

ПСТР(A1-1-D1-1)

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

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

ПСТР($A1-D1+1-E1-D1-1)

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

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

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

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

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

ЕСЛИОШИБКА(ПСТР($A1-D1+1-E1-D1-1)- «»)

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

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

НАЙТИ($B1-$A1-C1+1)

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

ПСТР($A1-C1+1-D1-C1-1)

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

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

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

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

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

ЕСЛИОШИБКА(НАЙТИ($B1-$A1-C1+1)-1000)

msoffice-prowork

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Приобрести продукты