Как расцепить строку в excel - Word и Excel - помощь в работе с программами

Содержание

Способ 1: Использование автоматического инструмента
Способ 2: Создание формулы разделения текста
- Шаг 1: Разделение первого слова
- Шаг 2: Разделение второго слова
- Шаг 3: Разделение третьего слова
Вопросы и ответы

Способ 1: Использование автоматического инструмента

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

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

После этого перейдите на вкладку «Данные» и нажмите кнопку «Текст по столбцам».

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

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

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

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

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

Способ 2: Создание формулы разделения текста

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

Шаг 1: Разделение первого слова

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

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

Выберите ячейку, где хотите расположить первое слово, и запишите формулу =ЛЕВСИМВ(.

После этого нажмите кнопку «Аргументы функции», перейдя тем самым в графическое окно редактирования формулы.

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

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

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

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

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

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

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

Полностью созданная формула имеет вид =ЛЕВСИМВ(A1;ПОИСК(" ";A1)-1), вы же можете создать ее по приведенной выше инструкции или вставить эту, если условия и разделитель подходят. Не забывайте заменить обрабатываемую ячейку.

Шаг 2: Разделение второго слова

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

В этом случае основной формулой станет =ПСТР( — запишите ее в таком виде, а затем переходите к окну настройки аргументов.

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

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

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

Вернитесь к предыдущей формуле, где добавьте к функции «ПОИСК» +1 в конце, чтобы начинать счет со следующего символа после найденного пробела.

Сейчас формула уже может начать поиск строки с первого символа имени, но она пока еще не знает, где его закончить, поэтому в поле «Количество_знаков» снова впишите формулу ПОИСК().

Перейдите к ее аргументам и заполните их в уже привычном виде.

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

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

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

Кликните по корню =ПСТР и поставьте курсор в конце строки «Количество_знаков».

Допишите там выражение -ПОИСК(" ";A1)-1) для завершения расчетов пробелов.

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

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

Шаг 3: Разделение третьего слова

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

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

В качестве текста указывайте ячейку с надписью для разделения.

В этот раз вспомогательная функция для поиска слова называется ДЛСТР(A1), где A1 — та же самая ячейка с текстом. Эта функция определяет количество знаков в тексте, а нам останется выделить только подходящие.

Для этого добавьте -ПОИСК() и перейдите к редактированию этой формулы.

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

Добавьте для начальной позиции еще один ПОИСК().

Ему укажите ту же самую структуру.

Вернитесь к предыдущей формуле «ПОИСК».

Прибавьте для его начальной позиции +1.

Перейдите к корню формулы ПРАВСИМВ и убедитесь в том, что результат отображается правильно, а уже потом подтверждайте внесение изменений. Полная формула в этом случае выглядит как =ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(" ";A1;ПОИСК(" ";A1)+1)).

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

На чтение 7 мин Опубликовано 07.01.2021

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

Содержание

Первый метод: применение автоматического инструмента
Второй метод: применение формулы расцепления текста
Этап первый: расцепление 1-го слова
Этап второй: расцепление 2-го слова
Третий этап: расцепление 3-го слова
Заключение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

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

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

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

Чтобы разбить текст в Excel, вы обычно используете функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) или ПСТР (MID) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH). На первый взгляд, некоторые рассмотренные ниже приёмы могут показаться сложными. Но на самом деле логика довольно проста, и следующие примеры помогут вам разобраться.

Для преобразования текста в ячейках в Excel ключевым моментом является определение положения разделителя в нем. Что может быть таким разделителем? Это запятая, точка с запятой, наклонная черта, двоеточие, тире, восклицательный знак и т.п. И, как мы далее увидим, даже целое слово.

Как распределить ФИО по столбцам
Как использовать разделители в тексте
Разделяем текст по переносам строки
Как разделить длинный текст на множество столбцов
Как разбить «текст + число» по разным ячейкам
Как разбить ячейку вида «число + текст»
Разделение ячейки по маске (шаблону)
Использование инструмента Split Text

В зависимости от вашей задачи эту проблему можно решить с помощью функции ПОИСК (без учета регистра букв) или НАЙТИ (с учетом регистра).

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

Для лучшего понимания пошагово рассмотрим несколько примеров.

Делим текст вида ФИО по столбцам.

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

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

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

Кратко напомним:

На ленте «Данные» выбираем «Текст по столбцам» — с разделителями.

Далее в качестве разделителя выбираем пробел.

Обращаем внимание на то, как разделены наши данные в окне образца.

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

В итоге имеем следующую картину:

При желании можно дать заголовки новым столбцам B,C,D.

А теперь давайте тот же результат получим при помощи формул.

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

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

=ЛЕВСИМВ(A2; ПОИСК(» «;A2;1)-1)

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

Далее будет чуть сложнее.

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

=ПСТР(A2; ПОИСК(» «;A2) + 1; ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) — 1)

Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:

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

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

Начальная позиция — это позиция первого пробела плюс 1:

ПОИСК(» «;A2) + 1

Количество знаков для извлечения: разница между положением 2- ^го и 1- ^го пробелов, минус 1:

ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1

В итоге имя у нас теперь находится в C.

Осталось отчество. Для него используем выражение:

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(» «; A2; ПОИСК(» «; A2) + 1))

В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- ^го пробела. Получаем количество символов после 2- ^го пробела, и функция ПРАВСИМВ их и извлекает.

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

Распределение текста с разделителями на 3 столбца.

Предположим, у вас есть список одежды вида Наименование-Цвет-Размер, и вы хотите разделить его на 3 отдельных части. Здесь разделитель слов – дефис. С ним и будем работать.

Чтобы извлечь Наименование товара (все символы до 1-го дефиса), вставьте следующее выражение в B2, а затем скопируйте его вниз по столбцу:

=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)

Здесь функция мы сначала определяем позицию первого дефиса («-«) в строке, а ЛЕВСИМВ извлекает все нужные символы начиная с этой позиции. Вы вычитаете 1 из позиции дефиса, потому что вы не хотите извлекать сам дефис.

Чтобы извлечь цвет (это все буквы между 1-м и 2-м дефисами), запишите в C2, а затем скопируйте ниже:

=ПСТР(A2; ПОИСК(«-«;A2) + 1; ПОИСК(«-«;A2;ПОИСК(«-«;A2)+1) — ПОИСК(«-«;A2) — 1)

Логику работы ПСТР мы рассмотрели чуть выше.

Чтобы извлечь размер (все символы после 3-го дефиса), введите следующее выражение в D2:

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(«-«; A2; ПОИСК(«-«; A2) + 1))

Аналогичным образом вы можете в Excel разделить содержимое ячейки в разные ячейки любым другим разделителем. Все, что вам нужно сделать, это заменить «-» на требуемый символ, например пробел (« »), косую черту («/»), двоеточие («:»), точку с запятой («;») и т. д.

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

Как разбить текст по переносам строки.

Чтобы разделить слова в ячейке по переносам строки, используйте подходы, аналогичные тем, которые были продемонстрированы в предыдущем примере. Единственное отличие состоит в том, что вам понадобится функция СИМВОЛ (CHAR) для передачи символа разрыва строки, поскольку вы не можете ввести его непосредственно в формулу с клавиатуры.

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

Напомню, что перенести таким вот образом текст внутри ячейки можно при помощи комбинации клавиш ALT + ENTER.

Возьмите инструкции из предыдущего примера и замените дефис («-») на СИМВОЛ(10), где 10 — это код ASCII для перевода строки.

Чтобы извлечь наименование товара:

=ЛЕВСИМВ(A2; ПОИСК(СИМВОЛ(10);A2;1)-1)

Цвет:

=ПСТР(A2; ПОИСК(СИМВОЛ(10);A2) + 1; ПОИСК(СИМВОЛ(10);A2; ПОИСК(СИМВОЛ(10);A2)+1) — ПОИСК(СИМВОЛ(10);A2) — 1)

Размер:

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(СИМВОЛ(10); A2; ПОИСК(СИМВОЛ(10); A2) + 1))

Результат вы видите на скриншоте выше.

Таким же образом можно работать и с любым другим символом-разделителем. Достаточно знать его код.

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

Изучив представленные выше примеры, у многих из вас, думаю, возник вопрос: «А что, если у меня не 3 слова, а больше? Если нужно разбить текст в ячейке на 5 столбцов?»

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

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

Для первого слова (наименования одежды) используем:

=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)

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

Для второго столбца и далее понадобится более сложное выражение:

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1);1)-1);»»)

Замысел здесь состоит в том, что при помощи функции ПОДСТАВИТЬ мы удаляем из исходного содержимого наименование, которое уже ранее извлекли (то есть, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-M-39-42-50». В нём мы снова ищем позицию первого дефиса, как это делали ранее. И при помощи ЛЕВСИМВ вновь выделяем первое слово (то есть, «Синий»).

А далее можно просто «протянуть» формулу из C2 по строке, то есть скопировать ее в остальные ячейки. В результате в D2 получим

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1);1)-1);»»)

Обратите внимание, жирным шрифтом выделены произошедшие при копировании изменения. То есть, теперь из исходного текста мы удаляем все, что было уже ранее найдено и извлечено – содержимое B2 и C2. И вновь в получившейся фразе берём первое слово — до дефиса.

Если же брать больше нечего, то функция ЕСЛИОШИБКА обработает это событие и вставит в виде результата пустое значение «».

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

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

Как разделить ячейку вида ‘текст + число’.

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

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

Метод 1. Подсчитайте цифры и извлеките это количество символов

Самый простой способ разбить выражение, в котором число идет после текста:

Чтобы извлечь числа, вы ищите в строке все возможные числа от 0 до 9, получаете общее их количество и отсекаете такое же количество символов от конца строки.

Если мы работаем с ячейкой A2:

=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

Чтобы извлечь буквы, вы вычисляете, сколько их у нас имеется. Для этого вычитаем количество извлеченных цифр (C2) из общей длины исходной ячейки A2. После этого при помощи ЛЕВСИМВ отрезаем это количество символов от начала ячейки.

=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))

здесь A2 – исходная ячейка, а C2 — извлеченное число, как показано на скриншоте:

Метод 2: узнать позицию 1- ^й цифры в строке

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

{=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))}

Как видите, мы последовательно ищем каждое число из массива {0,1,2,3,4,5,6,7,8,9}. Чтобы избежать появления ошибки если цифра не найдена, мы после содержимого ячейки A2 добавляем эти 10 цифр. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге получаем опять же массив из 10 цифр — номеров позиций, в которых они нашлись. И из них функция МИН выбирает наименьшее число. Это и будет та позиция, с которой начинается группа чисел, которую нужно отделить от основного содержимого.

Также обратите внимание, что это формула массива и ввод её нужно заканчивать не как обычно, а комбинацией клавиш CTRL + SHIFT + ENTER.

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

Чтобы получить текст:

=ЛЕВСИМВ(A2; B2-1)

Чтобы получить числа:

=ПРАВСИМВ(A2; ДЛСТР(A2)-B2+1)

Где A2 — исходная строка, а B2 — позиция первого числа.

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

Для вытаскивания текста:

=ЛЕВСИМВ(A2; МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))-1)

Для чисел:

=ПРАВСИМВ(A2; ДЛСТР(A2)-МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))+1)

Этого же результата можно достичь и чуть иначе.

Сначала мы извлекаем из ячейки числа при помощи вот такого выражения:

=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) -ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

А затем уже берём оставшееся:

=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))

Как видите, результат тот же. Можете воспользоваться любым способом.

Как разделить ячейку вида ‘число + текст’.

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

=ЛЕВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

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

=ПРАВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(B2))

Где A2 — исходная строка, а B2 — искомое число, как показано на снимке экрана ниже:

Как разбить текст по ячейкам по маске (шаблону).

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

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

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

Решением является разбиение строки по следующей маске: * ERROR: * Exception: *

Здесь звездочка (*) представляет любое количество символов.

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

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

Итак, в начале ищем позицию первого разделителя.

=ПОИСК(«ERROR:»;A2;1)

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

=ПОИСК(«Exception:»;A2;1)

Итак, для ячейки A2 шаблон выглядит следующим образом:

С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.

Таким образом, в первый столбец мы поместим первые 20 знаков:

=—ЛЕВСИМВ(A2;ПОИСК(«ERROR:»;A2;1)-1)

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

Далее нужно получить код:

=ПСТР(A2;ПОИСК(«ERROR:»;A2;1)+6;ПОИСК(«Exception:»;A2;1)-(ПОИСК(«ERROR:»;A2;1)+6))

Думаю, вы понимаете, что 6 – это количество знаков в нашем слове-разделителе «ERROR:».

Ну и, наконец, выделяем из этой фразы пояснение:

=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«Exception:»;A2;1)+10))

Аналогично добавляем 10 к найденной позиции второго разделителя «Exception:», чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.

Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.

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

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

Альтернативный способ разбить столбец в Excel — использовать функцию разделения текста, включенную в надстройку Ultimate Suite for Excel. Она предоставляет следующие возможности:

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

Чтобы было понятнее, давайте более подробно рассмотрим каждый вариант по очереди.

Разделить ячейку по символу-разделителю.

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

Для этого примера возьмем строки шаблона Товар-Цвет-Размер , который мы использовали в первой части этого руководства. Как вы помните, мы разделили их на 3 разных столбца, используя 3 разные формулы . А вот как добиться того же результата за 2 быстрых шага:

Предполагая, что у вас установлен Ultimate Suite , выберите ячейки, которые нужно разделить, и щелкните значок «Разделить текст (Split Text)» на вкладке «Ablebits Data».

Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
- Разверните группу «Разбить по символам (Split by Characters)» и выберите один из предопределенных разделителей или введите любой другой символ в поле «Пользовательский (Custom)» .
- Выберите, как именно разбивать ячейки: по столбцам или строкам.
- Нажмите кнопку «Разделить (Split)» .

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

Готово! Задача, которая требовала 3 формул и 5 различных функций, теперь занимает всего пару секунд и одно нажатие кнопки.

Разделить ячейку по нескольким разделителям.

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

Например, чтобы разделить предложение на части, используя запятые и союзы, активируйте инструмент «Разбить по строкам (Split by Strings)» и введите разделители, по одному в каждой строке:

В данном случае в качестве разделителей мы используем запятую и союз “или”.

В результате исходная фраза разделяется при появлении любого разделителя:

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

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

5.1.2021 12:20

9.8.2021 14:50

Этот формат не является обычным для Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Spilt by strings:

Точка (.) Для разделения дня, месяца и года
Двоеточие (:) для разделения часов и минут
Пробел для разграничения даты и времени

Нажмите кнопку Split, и вы сразу получите результат:

Разделить ячейки по маске (шаблону).

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

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

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

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

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

* ERROR:* Exception: *

Где звездочка (*) представляет любое количество символов.

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

А теперь нажмите кнопку «Разбить по маске (Split by Mask)» на панели «Split Text» , введите маску в соответствующее поле и нажмите «Split».
Результат будет примерно таким:

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

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

* * ERROR:* Exception: *

Проще говоря, маска указывает надстройке разделить исходные строки на 4 части:

Все символы перед 1-м пробелом в строке (дата)
Символы между 1-м пробелом и словом ERROR: (время)
Текст между ERROR: и Exception: (код ошибки)
Все, что идет после Exception: (текст описания)

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

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

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

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

Смотрите также вида (выгрузка с было: для любых условий решением является гибкая

функциях читайте в столбец. формулами это сделать,
строке «Текст 1» формула будет выглядеть 54 на 89, «ФИО». что предыдущий способ
нужно вписать адреса К сожалению, стандартная них сотрется иНапример, у насС помощью мастера распределения сайта-аукциона):Как в Эксель при решении подобного
формула основана на статье «Функция «ЕСЛИ»В ячейку А12 пишем смотрите в статье указываем адрес первой вот так: ваша формула должнаПерейдите на этап заполнения расстановки пробелов в нужных ячеек: для программы опция
заменится на новую. есть список с текста по столбцам
265572473,Fumiya Tanaka,Beautiful Town,Pluie/Noir Excel расцепить, разъединить рода задач. Гибкость текстовых функциях. в Excel».
формулу. Если сделаем «Месяц из даты ячейки (А1) нашегоПРОИЗВЕД(A1:A3;C1:C3) выглядеть следующим образом:
аргументов. функции «Сцепить» вВ поле «Текст1» укажите

support.office.com

Разделить текст Excel на несколько ячеек.

«Объединить» в этом Если нет пустых фамилиями, именами в текст, содержащийся в Recordings,PNR005,»12″», Ltd, Num, текст или предложение придает ей сложнаяВ ячейку B3 введитеЧасто приходится оптимизировать структуру
как в предыдущем Excel» здесь. будущего предложения. ВРезультат равен 18 164 250. Он=54*89Во всех ссылках на Excel не подойдет ячейку с фамилией не поможет, так столбцов, можно предварительно одном столбце. Нам одной ячейке, можно Blu»,6969799,For Sale,13.4,2015-09-25 02:26:10,absolutely из одной ячейки
комбинация из функций следующую формулу: данных после импорта примере (выделим диапазон
Как сцепить слова в следующей строке указываем не изменится, еслиЕсли ввести эту формулу ячейку поставьте знак вам. Тогда воспользуйтесь человека. Сделать это как при ее вставить столбцы. Как нужно написать фамилии разделить на несколько.
new,Mint (M),Mint (M),N,,0,0,0.0, на две ячейки НАЙТИ. Пользователю формулыТеперь скопируйте эту формулу в Excel. Некоторые и поставим пробел строке Excel. адрес второй ячейки ввести формулу в в ячейке C1, «$» перед буквой другим. Он подразумевает можно как вручную, выполнении часть данных это сделать, смотрите в одном столбце,Выделите ячейку или столбецНеобходимо расцепить данные по
достаточно определить закономерность вдоль целого столбца: разные значения попадают после &), тоВ ячейке А7 пишем  (В2). Нажимаем «ОК». таком виде: в ней будет и перед числом. включение пробелов непосредственно прописав «B4», так обязательно пропадет. Однако в статье «Как а имена в с текстом, который до каждой запятой.Надо чтобы в и указать их
Выборка чисел из строк в одну и получится не та формулу =СЦЕПИТЬ(A6;» «;B6;»Получилось такая формула =СЦЕПИТЬ(A1;B1)=A1*A2*A3*C1*C2*C3 показан результат (4806).Нажмите ОК.
в аргументы в и автоматически, нажав
можно обратиться к добавить строку, столбец другом столбце. Например, вы хотите разделить.Благодарю ячейке С2 было в параметрах функций:

в отдельные ячейки. туже ячейку образуя формула, которая нам «;C6;» «;D6) В ячейке C3В одной формуле можно Та же формула,После этого можно приступать «Мастере функций». Ну по кнопке справа
формуле «Сцепить» в в Excel» тут. чтобы определить полНа вкладкеManyasha остальное предложение: будут это квадратные
целую строку как нужна. В нейЭту формулу писать написано так. одновременно использовать числа но со ссылками к копированию функции а теперь давайте

excel-office.ru

Функция «Сцепить» в Excel

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

Способ 1: простое объединение

указав ячейку мышкой. статье мы расскажем, Ячеек столбцов. Например, Подробнее об этомнажмите кнопкуPav777 в нём много разделительные знаки. Например, значение содержащие определенное вопрос: как разбить

запятой будут стоять ячеек больше, то нет пробела. Исправляем ячейки, а количество A1 и B1 оставшиеся в колонке инструкции:В «Текст2» укажите ячейку как ею пользоваться. если вы делите читайте в статьеТекст по столбцам, а вкладка Данные-Текст слов и символов это могут быть количество символов в строку на ячейки

двоеточия. — напряжно. Но, так – добавляем
аргументов (элементов данных, в формуле ячейки. Для этого:Далеко от таблицы выделите
с именем.Логично будет начать статью
столбец на дни «В Excel по
.

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

которые обрабатывает функция)=A1*B1Выделите первую ячейку с ячейку, которую никогдаВ «Текст3» – с с информации о и месяцы, год, имени определить пол».В
запятая) не подходит? как расцепить текст
разделить строку наПервый аргумент – это
располагает разными поисковыми

диапазон в горизонтальный. дает нам способ (» «). Получилась не ограничено. Если) вместо чисел, вернет введенной функцией. не будете использовать. отчеством.

Способ 2: формула «Сцепить» с пробелом в Excel

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

сделать всё быстрее. такая формула =СЦЕПИТЬ(A1;» умножить данные из
такой же результатНаведите курсор на ееВведите в нее знакНажмите ОК. Excel сцепить ячейки «Формат данных столбца» в которых нужно столбцам: Двнные — Текст два слова:
В данном примере функция с исходным текстом.

по ячейках другие «ТРАНСП» в Excel. Используем &. В «;B1). Предложение получилось нашего примера на (4806). нижний правый угол.

Способ 3: расстановка пробелов через дополнительный аргумент

пробела.Это первый пример использования обычным способом, рассказать — «дата». разделить или весьустановите переключатель по столбцам -Ячейка А1: Добрый НАЙТИ во второмВторой аргумент – это ищут по содержимомуПишем в ячейке ячейке А8 пишем

такое. 2, результат будетПреимущество использования ссылок на
После появления крестика зажмитеСоздайте функцию «Сцепить» так,
функции «Сцепить» в о самой функцииНажимаем кнопку «Готово». столбец.с разделителями Разделитель запятая.
день
аргументе определяет положение позиция первого символа, ячеек. Ведь выполнять
А12 =ТРАНСП, затем формулу так. СтавимКакие бывают пробелы ещё,
равен 36 328

ячейки заключается в левую кнопку мыши. как было это Excel.Теперь вы можете «Сцепить» и привести Получилось так.Заходим на закладке «Данные»и нажмите кнопкуPav777Ячейка С1 формула: относительно первой закрывающейся

Способ 4: объединение колонок

с которого должна поиск по текстовой указываем в скобках знак «равно», затем где и для 500. Формула будет том, что ExcelУдерживая ее, протяните курсор описано в первом наблюдать, что в пример ее использования.Так можно разделить текст в раздел «Работа

Далее: =ПСТР (A1;1;НАЙТИ («
скобки. А в начинаться разделенная строка.
строке, которая содержится диапазон ячеек со выделяем все ячейки чего они, как
выглядеть следующим образом:

Online автоматически обновляет до конца колонки, способе, только остановитесь колонке «ФИО» появился Предварительно рассмотрим синтаксис:

из одной ячейки с данными» и
.Manyasha
«;A1)-1) третьем аргументе эта
Последний аргумент – это в ячейке ¬– словами, ставим &» со словами, которые
поставить пробел или

=ПРОИЗВЕД(A1:A3;C1:C3;2) результат формулы. выделив все сопутствующие на этапе ввода результат – фамилия, =СЦЕПИТЬ (текст1;текст2;…). на большее количество

выбираем функцию «Текст

fb.ru

Умножение чисел в ячейках

Выберите,Ячейка С2 формула: же функция вычисляет количество символов, которое это также распространенная «. Нажимаем F9. должны войти в заменить его, смотритеРассмотрим,

Если для умножения вы ячейки. аргументов. имя и отчествоИз него становится понятным, столбцов. Например, здесь по столбцам». В
разделителиTimSha = =ПСТР (A1;НАЙТИ положение нужного нам должна содержать разделенная
потребность пользователей Excel. Получилась такая формула.

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

Отпустите кнопку мыши.

Первым аргументом введите фамилию. записаны последовательно. что функция довольна разделили дату отдельно появившемся окне «Мастердля своих данных., дико извиняюсь и (» «;A1)+1;100) текста в строке строка. Их мы иОсталось написать после знака примере – это

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

Внимательные пользователи могли заметить, простая. Из аргументов по столбцам – текстов», в строке

Умножение чисел с помощью функции ПРОИЗВЕД в формуле

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

«Формат исходных данных»запятуюСпасибо! Добрый день

квадратной скобки. Вычисление все понятно – разделения строк. и заменить фигурные & и пробел писать слова, а значений нескольких ячеек.Функция ПРОИЗВЕД также умножает заметить, это очень есть пробел. в ячейке записаны который нужно объединитьДля большей наглядности указываем – «С

Роман ДубровА как расцепить, в третьем аргументе это ссылка на

Допустим на лист Excel

скобки круглыми. Нажимаем » » – цифры вставлять, указываяМожно составить предложение числа и наиболее эффективный способ, еслиДалее выберите имя, снова без пробелов. В в единой ячейке. в таблице Excel разделителями». Нажимаем кнопкупробел: Здравствуйте! Помогите, пожалуйста,

разъединить текст или

support.office.com

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

более сложное и ячейку A3. Второй были импортированные данные «ОК». Формула стала это значит, после адрес ячейки с из горизонтальных и эффективна при умножении вам нужно справиться пробел и в
некоторых случаях, например Ну а теперь можно выделить некоторые «Далее».. Данные можно предварительно расцепить формулой текст предложение если в оно подразумевает вычитание аргумент мы вычисляем из другой программы. такой =СЦЕПИТЬ(«Мама «;»мыла каждого слова ставить ними. Тогда при
 вертикальных ячеек, из большого количества чисел с огромным объемом
последней – отчество. в нашем, это давайте рассмотрим все строки, столбцы (например,Здесь указываем в строке просмотреть в окне в примере. Данные нём много слов одной большей длинны с помощью функции Из-за несовместимости структуры «;»раму. «) пробел. Теперь нажимаем изменении цифры, они текста или из или диапазонов чисел. данных.Нажмите ОК. недопустимо. Поэтому между на примере. Допустим,
итоговые) выделить другим «Символом – разделителемОбразец разбора данных текста из web-запроса и символов по текста от меньшей. НАЙТИ(«]»;A3)+2. Она возвращает данных при импорте
Получилось так. клавишу на клавиатуре будут меняться и текста и цифр. Если вы попытаетесьАвтор: Вадим МатузокКак можно заметить, второй
аргументами нужно поставить у вас есть цветом, разделительной линией. является» — «пробел».. и в таком необходимой длине на А чтобы учитывать очередной номер символа некоторые значение из
Еще один способ заполнить F9. У нас в предложении. ФормулаНапример, можно заполнить перемножить диапазоны чиселНезависимо от того, насколько
 способ постановки пробелов пробелы. Делается это
таблица с именами Подробнее, смотрите в Поставили «пробел», потомуНажмите кнопку вот интересном формате. две ячейки еще 2 пробела первой закрывающейся квадратной разных категорий были бланк, таблицу данными получилась такая формула.
получится такая =СЦЕПИТЬ(«Сегодня»;» квитанцию на оплату, с помощью знака хорошо вы знаете между аргументами формулы следующим образом: людей. При этом статье «Разделительная линия
 что в нашемДалее
Стандартно расцепить не… следует вычитать число
скобки в строке. внесены в одну из разных ячеекЭто формула массива. Нам «;B1) др., взяв данные «звездочка», например таблицу умножения, позвольте «Сцепить» в ExcelКликните дважды левой кнопкой имя, отчество и строк в таблице списке имя и. получается.: Если правильно понял 3. В результате И к этому ячейку. Необходимо из описан в статье нужно её подкорректировать.Вместо слова «СЦЕПИТЬ» из таблицы Excel.=A1:A3*C1:C3 Excel Online стать
также отлично справляется мыши по ячейке фамилия вписаны в Excel» тут. фамилия написаны черезВыберите значение в разделеСпасибо!C1 =ЛЕВСИМВ (A1;длина)
чего получаем правильное номеру мы добавляем
этой ячейки отделить
«Как заполнить бланк Пишем после знака в формулах можно Смотрите пример в, функция вернет ошибку вашим калькулятором. Умножайте
 со своими задачами. с формулой.
отдельные колонки. НашаМожно разделить ячейку пробел. Если быФормат данных столбцаВладимирC2 =ПРАВСИМВ (A1;
количество символов в еще число 2, целые числовые значения. в Excel». «равно» слово «СЦЕПИТЬ» ставить знак «&» статье «Объединить слова #ЗНАЧ!. Вот где числа с помощью Некоторым пользователям онВпишите между аргументами пробелы. задача — объединить
по диагонали и, имя и фамилияили используйте то,: Роман, Вы откройте
ДЛСТР (A1)-длина) разделенной строке. С так как нам Пример таких неправильноВ Excel можно и фигурные скобки (амперсанд). Тогда формула
из разных ячеек функция ПРОИЗВЕД действительно простой формулы, используя придется по душе, Однако делается это их в одной: в каждой части
(др. слова) были
которое Excel выберет свой пример..Как вариант помощью такой гибкой нужен номер символа импортированных данных отображен
посчитать символы, цифры, меняем на круглые. получится такая =»Сегодня»&» в текст Excel». незаменима: она умножает символ «звездочка» (*) так как все нестандартным способом. ПробелВыделите пустую ячейку. В
написать текст. Читайте, написаны через запятую, автоматически.Роман ДубровC2 =ПСТР (A1;нач_позиция; формулы можно делать после пробела за ниже на рисунке: слова, конкретные буквы, Нажимаем «Enter». Формула «&B1 В этой же диапазоны, не используя в качестве оператора

excel-office.ru

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

действия выполняются непосредственно должен выступать также нашем случае в как это сделать, то мы быВ поле: Извините! Посмотрите, пожалуйста, кол_во_символов) выборку разной длинны квадратной скобкой. ВСначала определим закономерность, по числа в ячейках. стала такой.Добавить слово в каждую статье приведена формула, «звездочку». умножения. в графическом интерфейсе. в роли аргумента. колонке «ФИО». в статье «Как указали разделителем «запятая».Поместить в этот.

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

причем кол-во символов разделенного текста из последнем аргументе функция которой можно определить, Какими способами это=СЦЕПИТЬ(«Функция «;»»»СЦЕПИТЬ»» «;»в ячейку Excel. как преобразовать вВ этой таблице нужноВыделите пустую ячейку иВписывать в каждую ячейку Это означает, чтоНажмите по кнопке «Вставить разделить ячейку в

В этом окне, ввыберите место наNic70y можно указывать больше, разных длинны исходных вычисляет какое количество что данные из сделать, смотрите в «;»Excel. «)Функцию «СЦЕПИТЬ» можно Excel имя и перемножить числа в введите знак равенства нашей таблицы формулу его необходимо взять функцию». Находится она Excel» здесь. разделе «Образец разбора листе, где должны: удалил

чем длина строки строк.

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

статье «Как посчитатьПолучилось так.

применить для того,

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

отчество в инициалы. столбцах A и ( «Сцепить» – это

в кавычки, а слева от строкиУдобнее работать в
данных» сразу видно, отображаться разделенные данные.Михаил С. — сам «обрежет»
Например в ячейке А1 разделенная строка после на то, что количество символов в

Если нужно чтобы в заполненныеОбъединить данные в ячейках C.= не самый удачный после них поставить формул. таблице, где в какие столбцы уНажмите кнопку: Так а что до конца. предложение: разделения, учитывая положение они находятся в ячейке Excel».соединить слова в Excel ячейки добавить текст, ExcelВведите знак равенства, затем). Помните, что все вариант, ведь можно

точку с запятой.В появившемся окне выберите ячейках не видны нас получатся. НажимаемГотово надо то? ТамP.S: Уберите всеКак в Эксель квадратной скобки. одной и тойЕсть в Excel через косую черточку слова, числа, знаки,. ПРОИЗВЕД, а затем формулы начинаются со воспользоваться специальной опциейНажмите Enter для применения категорию «Текстовые». нули. Как их кнопку «Далее».. все без формул, пробелы в формулах. Excel расцепить, разъединитьОбратите внимание! Что в

же строке. В логические функции. С, то формулу напишем т. д. Подробнее,У нас такая в скобках введите знака равенства. для этого. Однако изменений.В самом списке выделите убрать, скрыть, заменить,Здесь, в разделеЗдесь рассмотрим, если это время.Валентин б текст или предложение нашем примере все нашем случае нас их помощью можно так. =СЦЕПИТЬ(А16;»/»;В16) Получилось как это сделать, таблица с данными. числа, ячейки илиВведите числа, добавляя между предварительно, если дляТеперь, как вы можете функцию «Сцепить». читайте в статье «Поместить в:» указываем

exceltable.com

Как в Эксель Excel расцепить, разъединить текст или предложение из одной ячейки на две ячейки по необходимой длине

как разделить текст вNic70y
: Я подготовил вам из одной ячейки исходные и разделенные интересуют только числа, посчитать не все так. смотрите в статьеВ ячейку С3 пишем диапазоны, которые нужно

каждой парой символ расстановки пробелов вы заметить, между всеми
Нажмите ОК. «Как убрать нули диапазон столбцов, в ячейке Excel на: :D таблицы с использованием

на две ячейки строки имеют разную которые находятся вне
ячейки, а толькоКак сцепить слова в «Добавить текст в

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

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

нужные. Или написать столбце Excel. ячейки с текстом «Главная» в разделе или ссылки на* необходимо будет сделать

 Однако это только выбранной функции. Так
При работе в табличном
данные.Например, предложение из
: =ОТБР(A5*24) =ОСТАТ(A5*24*60;60)
на п/я [email protected] если в нём
количество символов. Именно способом можно быстро в ячейке нужныеЗдесь действуем немного Excel».
«Библиотека функций» выбираем ячейки точками с

). ссылку на ячейку первый способ вставки, как мы будем редакторе Excel иногдаВнимание!

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

 первой ячейки разнестиРоман Дубров
Я вышлю файл много слов и поэтому мы называли
выбрать из строк слова, числа (например по другому принципу,В Excel можно
– «Текстовые». Здесь запятой, а чтобы
Нажмите клавишу ВВОД, чтобы

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

: Спасибо Nic70y иPav777 символов такую формулу –

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

excelworld.ru

Расцепить текст в формате времени

 ко второму. то в соответствующее в одной ячейке должны быть пустыми, несколько других ячеек, Михаилу С.! Работают: Здравствуй, форум.Надо что бы гибкой, в начале
поместить их в

баллы), и многое работает только со только месяц, год

 В появившемся диалоговом двоеточия, например: A1:A3.Например, для умножения числа

 ячейки в колонкеМожет случиться и так,

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

Есть строка следующего в ячейке С1

 статьи. Она подходит отдельные ячейки? Эффективным

 другое. Об этих строками. Вот наш или число. Какими окне пишем в

planetaexcel.ru

В этом примере

Источник

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

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

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

Поехали..

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=SUBSTRING(Txt; Delimeter; n)

где

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

Например:

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

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО «ИвановИванИванович»), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор 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 "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then
            Out = Out & Mid(Txt, i, 1) & " "
        Else
            Out = Out & Mid(Txt, i, 1)
        End If
    Next i
    CutWords = Out
End Function

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

Ссылки по теме

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

Источник

Способ 1: Использование автоматического инструмента

Способ 2: Создание формулы разделения текста

Шаг 1: Разделение первого слова

Шаг 2: Разделение второго слова

Шаг 3: Разделение третьего слова

Еще статьи по данной теме:

Помогла ли Вам статья?

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

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

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

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

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

Заключение

Делим текст вида ФИО по столбцам.

Распределение текста с разделителями на 3 столбца.

Как разбить текст по переносам строки.

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

Как разделить ячейку вида ‘текст + число’.

Метод 1. Подсчитайте цифры и извлеките это количество символов

Метод 2: узнать позицию 1- й цифры в строке

Как разделить ячейку вида ‘число + текст’.

Как разбить текст по ячейкам по маске (шаблону).

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

Разделить ячейку по символу-разделителю.

Разделить ячейку по нескольким разделителям.

Разделить ячейки по маске (шаблону).

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

Разделить текст Excel на несколько ячеек.

Функция «Сцепить» в Excel

Способ 1: простое объединение

Способ 2: формула «Сцепить» с пробелом в Excel

Способ 3: расстановка пробелов через дополнительный аргумент

Способ 4: объединение колонок

Умножение чисел в ячейках

Умножение чисел с помощью функции ПРОИЗВЕД в формуле

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

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

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

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

Как в Эксель Excel расцепить, разъединить текст или предложение из одной ячейки на две ячейки по необходимой длине

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

Расцепить текст в формате времени

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

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

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

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

Ссылки по теме

А вот еще интересные статьи:

Метод 2: узнать позицию 1- ^й цифры в строке