Как расцепить данные в ячейке в 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-х слов. Каждое слово находится в отдельной колонке.

Заключение

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

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

Источник

Распределение содержимого ячейки на соседние столбцы

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

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

 принудительный пересчет? ведьLightZ

: Ух сидел щас если я Вас и за макрос.Обратите внимание! Что в на то, что действия к объединенным
в самом начале, можно установить курсор вышеприведенный вариант разрыва

проделываем, пока координаты

 255 включительно.Готово возможность в Excel столбцах. При необходимости можно оперативнее обеспечивать функция явно берет: почему у меня на работе ломал правильно поняла.Ilia karbofos нашем примере все они находятся в данным был добавлен чтобы в другие в поле и склейки прекрасно подойдет. всех ячеек, которыеКак известно, обычное объединение. не поддерживается. Вместо вставьте количество пустые

вас актуальными справочными в качестве аргумента не работает UDF? голову как отсечь
FOTINKA1980: меню «данные» - исходные и разделенные одной и той и текстовый материал. поля, где содержится нажать сочетание клавиш
Но его будет нужно объединить, не ячеек в ЭксельОбъединение и отмена объединения этого вы можете

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

support.office.com

Разделение ячейки

Ctrl+V затруднительно быстро воплотить, будут внесены в приводит к потере ячеек создать новый столбец для хранения каждого языке. Эта страница которая изменяется…: формула =FIO(V3) должна в отдельный столбик=))) запятую и пробел в качастве разделителя длину и разное нашем случае насСЦЕПИТЬ мог копировать его,. Координаты будут вставлены. если существуют множество окно аргументов функции. данных. Сохраняются только

Слияние и разделение ячеек

рядом с тем, составные части распределенных переведена автоматически, поэтомуесть соображения? вводится сразу воСпасибо товарищи! — правила русского укажите пробел. если количество символов. Именно интересуют только числа,– единственная возможность как содержащий постоянные Таким способом чередуем ячеек, которые нужно После этого жмем данные расположенные в или данных

в котором расположена

Разделение содержимого ячейки на несколько ячеек

данных. ее текст можетHugo

 все ячейки A4:T4vikttur языка. А потому после инициалов стоят поэтому мы называли которые находятся вне объединения ячеек без
абсолютные ссылки. В поля с адресами объединить. Тем более, на кнопку верхнем левом элементе.Одной из интересных функций необходимая ячейка, аВыделите ячейку, диапазон или содержать неточности и: Слышал. И даже
для этого выделяем: Если копеек много, Данные — Текст точки — то такую формулу – квадратных скобок. Каким потерь в Excel.
остальных полях оставляем элементов и пустой если эти ячейки«OK» Для того, чтобы приложения Microsoft Excel затем разделить ее. весь столбец, где грамматические ошибки. Для попробовал — не ячейки A4:T4, вводим
то столбик упадет по столбцам - в два приёма: гибкой, в начале способом можно быстро Кроме того, с относительные ссылки. Как ячейки. После того, не находятся в. объединить содержимое двух

См. также

является функция Кроме того, содержимое

содержатся текстовые значения, нас важно, чтобы

support.office.com

Работа с функцией СЦЕПИТЬ в Microsoft Excel

помогает. формулу, нажимаем три :) разделители пробел и сначала по пробелу статьи. Она подходит выбрать из строк его помощью можно всегда, после выполнения как все данные едином массиве. ЗначительноКак видим, содержимое выбранных и более ячеекСЦЕПИТЬ ячейки можно разделить которые требуется разделить. эта статья былаА как ты кнопки

Можно еще формулой: запятая, поместить в

Применение оператора СЦЕПИТЬ

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

можно, воспользовавшись вариантом одной предварительно указанной

потерь можно применить

является соединение содержимого ячеек.Данные вас уделить паруKuklP: LightZ, выделите все=ПРАВСИМВ(A1;2) Для страховки дать

Способ 1: объединение данных в ячейках

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

20 ячеек, нажмитеГерыч формат полей -можно также воспользоваться придает ей сложная решением является гибкая манипуляции. Знание алгоритма..
окно аргументов. данного способа имеетсяСЦЕПИТЬ ячеек в одной.Выделите одну или несколькоРабота с данными помогла ли она функции что-то берут F2, а потом: Стоит такая задача: текстовый… функцией правсимв, которая комбинация из функций
формула основана на работы с этойУстанавливаем курсор в нижнийКак видим, после этогоВыделяем двойным кликом левой существенный недостаток. При. Данный оператор помогает ячеек, которые хотитенажмите кнопку вам, с помощью
в качестве аргумента. ctrl+shift+enter. пользователь вводит ФИО,63383{/post}{/quote} отрезает указанное количество НАЙТИ. Пользователю формулы текстовых функциях. функцией позволит облегчить правый угол элемента в целевой ячейке кнопки мыши любую его использовании происходитВыделяем ячейку, в которой решить некоторые задачи, разделить.Текст по столбцам кнопок внизу страницы. А если включенHugo а программно должноНе поняла …
символов справа, или достаточно определить закономерностьВ ячейку B3 введите решение многих вопросов с формулой. Появляется образовалась объединенная запись, пустую ячейку на так называемая «склейка планируем разместить объединенные которые с помощьюВажно:. Для удобства также пересчет вручную…:-): Не могу посмотреть разносится по клеткамwebley

ПСТР, котороая выхватывает и указать их

Способ 2: применение функции с пробелом

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

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

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

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

а команда меню. кол-во символов будут это квадратные вдоль целого столбца:Часто приходится оптимизировать структуру маркером заполнения. Зажимаем пробелами между каждым неё пробел. Желательно, нет пробела и«Вставить функцию» его помощью удобно данные из следующей, чтобы указать способ языке) . файл смотрел? файл (т.е. убивает

буква в отдельной В 2007 ВкладкаFOTINKA1980 скобки либо другиеВыборка чисел из строк данных после импорта левую кнопку мыши словом. чтобы она находилась они склеены в. Она имеет вид производить процедуру объединения ячейки, поэтому освободите
разбиения текста наМожно разделить содержимого ячейкиikki проект ВБА). клетке Данные — Текст: Подскажите пожалуйста, я разделительные знаки. Например, в отдельные ячейки. в Excel. Некоторые и тянем егоВнимание! Как видим, вышеуказанный подальше от основного единый массив. При пиктограммы и размещена ячеек без потерь. достаточное пространство на разных столбцах. и распространение составные: каюсь, не проверялНо UDF нужноGuest по столбцам знаю, что есть это могут быть
разные значения попадают вниз параллельно расположению способ значительно ускоряет массива. Очень важно, этом вручную добавить слева от строки Рассмотрим возможности данной листе.Примечание: части нескольких смежных — поверил Игорю чуть подправить -: ПСТРZ формула, которая объединяет пробелы если нужноФункция ПСТР возвращает текстовое в одну и объединяемых элементов. процедуру корректного объединения чтобы эта ячейка пробел не получится,

формул. функции и нюансыНа вкладке Для получения справки по ячеек. Например, если на слово ) чтоб нули не

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

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

сейчас попробовал - писало, если фамилия: Типа этого нужно? post_172937.png (57.24 КБ) ячеек -Concatenate. А

слова и т.п. количество символов в целую строку как данные в указанных Но следует учесть, не заполнялась никакими редактирование формулы.Мастер функцийСкачать последнюю версиюв группе Работа мастера читайте статьюПолное имя у меня всё короче, и неLightZx0d0k есть ли формулаВ данном примере функция строке. Аргументы функции: одно значение. Возникает столбцах будут объединены что данный вариант данными.Урок:. В категории Excel с Разделение текста постолбца, можно разделить столбец пашет и без выпадала в ошибки,: Dim x As: Самый простой способ которая разъединяет слова,
НАЙТИ во второмПервый аргумент – это вопрос: как разбить в одном столбце. таит в себеВыполняем те же действия,Мастер функций в Excel«Текстовые»Функцияданными
различным столбцам с на двух столбцов волатайла, при автопересчете, если длиннее:

String опробованный тысячу и записанные в одной

Способ 5: добавление дополнительных символов

аргументе определяет положение ссылка на ячейку строку на ячейкиУрок: и «подводные камни». что и приСуществуют возможности исправить данныйилиСЦЕПИТЬнажмите кнопку преобразовать мастера текстов,

— ессно.Function FIO(r AsDim i As один раз: ячейке через запятую. относительно первой закрывающейся с исходным текстом. в Excel. ПрограммаКак объединить столбцы в Очень важно, чтобы первом способе применения недочет, вставив пробелы«Полный алфавитный перечень»относится к группетекст по столбцам или нажмите кнопкуимени
Hugo Range) Long1. Через Ctrl+H

НАпример в одной скобки. А вВторой аргумент – это располагает разными поисковыми Экселе в элементе, который функции между аргументами оператора.ищем оператор текстовых операторов Excel.. Откроется мастер пересчетаСправкаи: Сергей, не смотрелDim i

x = Len([a1])

lumpics.ru

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

меняем то, что ячейке написано так: третьем аргументе эта позиция первого символа, функциями: одни ищутФункцию содержит пробел, соСЦЕПИТЬВыполняем задачу по тому«СЦЕПИТЬ» Её основная задачатекста по столбцамв мастере разделенияФамилии — чужой комп,Dim a(1 ToFor i = разделяет слова на Яблоко, Апельсин, Ананас. же функция вычисляет с которого должна по ячейках другиеСЦЕПИТЬ временем не появились

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

, вплоть до открытия же алгоритму, который. Выделяем это наименование — объединение в. текста столбцов.столбцом. чужой Эксель глючный, 1, 1 To 1 To x какую-нибудь «нестандартную» группу Мне надо чтобы положение нужного нам начинаться разделенная строка.

ищут по содержимомуможно также использовать какие-то данные или окна аргументов оператора. описан выше. и жмем на одной ячейке содержимогоУстановите переключательПримечание:Советы: убивает проект и 20) As StringCells(2, i) = символов, например ;# он разнес это текста в строкеПоследний аргумент – это ячеек. Ведь выполнять для добавления дополнительных он не был

Добавляем значение первойДвойным щелчком левой кнопки

кнопку нескольких ячеек, а

С разделителями Мы стараемся как можно

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

зависает…For i = Mid(Range(«A1»).Value, i, 1)3. Копируем весь

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

оперативнее обеспечивать васСведения об альтернативном способеВ общем, мои 1 To IIf(Len(r.Value)Next массив в Word. т.е каждое слово квадратной скобки. Вычисление должна содержать разделенная строке, которая содержится которых не былоС помощью функции в поле окна, с формулой активируем. Начиная с версии вариант, и нажмите актуальными справочными материалами распределения текста по вердикты отменяются до a(1, i) =PS. Хотелось бы4. Сохраняем как

в отдельной ячейке. в третьем аргументе строка. в ячейке ¬– в первоначальном объединяемомСЦЕПИТЬ как это уже её для редактирования.Запускается окно аргументов функции. Excel 2016, вместо кнопку на вашем языке. столбцам см. в подтверждения на нормальной Mid(r.Value, i, 1) посмотреть вариант UDF ТХТ с кодировкой Можете помочь кто более сложное иС первым аргументом ПСТР это также распространенная диапазоне. Более того,можно быстро объединять было описано ранее.Между каждым аргументом записываем В качестве аргументов

данного оператора используетсяДалее Эта страница переведена статье Разделение текста системе :)NextHugo Стандартная Windows и знает?! оно подразумевает вычитание все понятно – потребность пользователей Excel. можно с помощью данные нескольких колонок Затем устанавливаем курсор выражение в виде могут выступать ссылки функция. автоматически, поэтому ее по столбцам сНо верю ikkiFIO = a: Function FIO(r As ставим галочку вwebley одной большей длинны это ссылка на Их мы и

exceltable.com

Ексель. Есть функция сцепить (из 3 в 1), а как расцепить ФИО в одном столбце на 3 столбца.

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

 текст может содержать помощью функций. :)End Function Range) «Перенос по строкам»: Текст по столбцам текста от меньшей. ячейку A3. Второй будем использовать для и другие операторы.С ячейками первой строки
и выделяем ту двух сторон кавычками. данные или отдельный. Но в целях разделителей, чтобы задать неточности и грамматическиеМожно объединить ячейки, содержащие

Разъединить ячейки

HugoНо сама неDim i5. Открываем этот не подойдет? А чтобы учитывать аргумент мы вычисляем разделения строк.Выполняем действия по добавлению объединяемых колонок проделываем пустую ячейку с После внесения каждого текст. Если в сохранения обратной совместимости места, в которых ошибки. Для нас функцию СЦЕП или: Проверил на своей пересчитывается — нужноDim a(1 To

 ТХТ в Экселе,Z еще 2 пробела

 с помощью функцииДопустим на лист Excel значений в окно на выбор те пробелом, о которой такого значения ставим задачу входит объединение оператор произойдет разделение ячейки. важно, чтобы эта функцию СЦЕПИТЬ. системе — файл F9 нажимать. Почему
1, 1 To

 указав в качестве: Точнее — через следует вычитать число НАЙТИ(«]»;A3)+2. Она возвращает были импортированные данные аргументов функции любым

 действия, которые указаны шла речь ранее. точку с запятой. содержимого ячеек, тоСЦЕПИТЬ В области статья была вамВыполните следующие действия. post_314441.xls работает нормально, — не знаю 20) разделителей Вашу «нестандартную» запятую и пробел
3. В результате
очередной номер символа

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

тоже оставлен, иОбразец разбора данных полезна. Просим вас

Примечание: при изменении фамилии :(For i =
группу символов. — правила русского чего получаем правильное первой закрывающейся квадратной Из-за несовместимости структуры
был приведен выше. третьем способе применения
поле окна аргументов. выражений должен быть мы будем работать его можно использоватьможно посмотреть на
уделить пару секунд Диапазон, содержащий столбец, который формула пересчитывается. ЭтоLightZ 1 To Len(r.Value)
6. Имеем счастье языка. А потому

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

 вы хотите разделить тот Portable 2003: Спасибо :)a(1, i) = :-)) Данные — Текст
разделенной строке. С

 И к этому некоторые значение из полей (при необходимости вы решили воспользоваться

можно её скопировать,
» «;
Устанавливаем курсор в первое

СЦЕП Нажмите кнопку ли она вам, может включать любое Эксель глючил.KuklP Mid(r.Value, i, 1)x0d0k по столбцам -

 помощью такой гибкой номеру мы добавляем

 разных категорий были их может быть

 способом с пустой выделив и нажавДля того, чтобы вывести
поле окна. Затем.
Далее
с помощью кнопок количество строк, но
Что-то Герыч пропал…: Игорь, про volatile
Next

: Вот это то, разделители пробел и

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

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

Ctrl+C жмем на кнопку листе, в которой

 следующим образом:В области удобства также приводим не более одного
: Герыч только на автопересчет в параметрахEnd Function средь ясного неба

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

 будет сделать абсолютной..Enter содержатся данные, нужные=СЦЕПИТЬ(текст1;текст2;…)Формат данных столбца
ссылку на оригинал столбца. Это важные этом форуме оставил поставить:-) Все считает.Рассчёт на 20 :-)))))))))))))) Для страховки дать

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

ikki ячеек, вводить какТакого откровения я формат полей - строк.

 квадратной скобкой. В Пример таких неправильно

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

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

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

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

63383 условие. Если у вычисляет какое количество

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

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

 появились разделения между со вторым полем. его содержат. Количество формат данных, что две небольшие, расположенные перезапись по данные, интернете много… )) — ПОЧЕМУ?
но не суть…

 такая опция… (стало: Т.е он формула макрос сбросьте. Очень разделенная строка после которой можно определить,«OK»
($) пустую ячейку. Так

planetaexcel.ru

словами.

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

Поехали..

Способ 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-го слова

Заключение

Распределение содержимого ячейки на соседние столбцы

Разделение ячейки

Разделение содержимого ячейки на несколько ячеек

См. также

Работа с функцией СЦЕПИТЬ в Microsoft Excel

Применение оператора СЦЕПИТЬ

Способ 1: объединение данных в ячейках

Способ 2: применение функции с пробелом

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

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

Способ 5: добавление дополнительных символов

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

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

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

Ексель. Есть функция сцепить (из 3 в 1), а как расцепить ФИО в одном столбце на 3 столбца.

Разъединить ячейки

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

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

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

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

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

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

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