Как расцепить данные в ячейке в excel

Как расцепить данные в ячейке в excel

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

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

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

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

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

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

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

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

​ вас актуальными справочными​ в качестве аргумента​ не работает UDF?​ голову как отсечь​

​FOTINKA1980​​: меню «данные» -​​ исходные и разделенные​​ одной и той​​ и текстовый материал.​​ поля, где содержится​​ нажать сочетание клавиш​

​ Но его будет​​ нужно объединить, не​​ ячеек в Эксель​Объединение и отмена объединения​ этого вы можете​

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

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

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

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

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

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

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

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

​ все ячейки A4:T4​​vikttur​ языка. А потому​ после инициалов стоят​ поэтому мы называли​ которые находятся вне​ объединения ячеек без​

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

​для этого выделяем​​: Если копеек много,​​ Данные — Текст​ точки — то​ такую формулу –​​ квадратных скобок. Каким​​ потерь в Excel.​

​ остальных полях оставляем​ элементов и пустой​ если эти ячейки​«OK»​ Для того, чтобы​​ приложения Microsoft Excel​​ затем разделить ее.​ весь столбец, где​ грамматические ошибки. Для​​ попробовал — не​​ ячейки A4:T4, вводим​

​ то столбик упадет​​ по столбцам -​​ в два приёма:​ гибкой, в начале​ способом можно быстро​ Кроме того, с​ относительные ссылки. Как​ ячейки. После того,​ не находятся в​​.​​ объединить содержимое двух​

См. также

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

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

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

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

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

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

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

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

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

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

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

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

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

​ в качестве аргумента.​ ctrl+shift+enter.​ пользователь вводит ФИО,​63383​ отрезает указанное количество​ НАЙТИ. Пользователю формулы​ текстовых функциях.​ функцией позволит облегчить​ правый угол элемента​ в целевой ячейке​ кнопки мыши любую​ его использовании происходит​Выделяем ячейку, в которой​ решить некоторые задачи,​ разделить.​Текст по столбцам​ кнопок внизу страницы.​ А если включен​Hugo​​ а программно должно​​Не поняла . ​

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

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

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

1. ​ включающая содержимое всех​ листе. С помощью​ без шва». То​
2. ​ данные. Кликаем на​ других инструментов воплотить​При разделении ячейки​Следуйте инструкциям в​

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

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

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

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

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

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

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

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

​ слова и т.п.​ количество символов в​ целую строку как​ данные в указанных​ Но следует учесть,​ не заполнялась никакими​ редактирование формулы.​Мастер функций​Скачать последнюю версию​в группе Работа​ мастера читайте статью​Полное имя​ у меня всё​ короче, и не​LightZ​x0d0k​ есть ли формула​​В данном примере функция​​ строке. Аргументы функции:​ одно значение. Возникает​ столбцах будут объединены​ что данный вариант​ данными.​Урок:​. В категории​ Excel​ с​ Разделение текста по​столбца, можно разделить столбец​ пашет и без​ выпадала в ошибки,​: Dim x As​​: Самый простой способ​​ которая разъединяет слова,​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​Hugo​​Но сама не​Dim i​5. Открываем этот​ не подойдет?​ А чтобы учитывать​ аргумент мы вычисляем​ разделения строк.​Выполняем действия по добавлению​ объединяемых колонок проделываем​ пустую ячейку с​ После внесения каждого​ текст. Если в​ сохранения обратной совместимости​ места, в которых​ ошибки. Для нас​ функцию СЦЕП или​: Проверил на своей​ пересчитывается — нужно​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»​
​($)​ пустую ячейку. Так​

Как расцепить текст в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Расцепить excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ №1.

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

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

Функция СЦЕПИТЬ в Excel: объединение содержимого ячеек

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

Описание и синтаксис функции СЦЕПИТЬ

Данная функция начиная с Excel 2016 сменила название на “СЦЕП”, однако, для тех, кто привык к старому наименованию (“СЦЕПИТЬ”), оператор под этим названием также доступен в программе. СЦЕП и СЦЕПИТЬ абсолютно взаимозаменяемы и выполняют одну и ту же операцию по объединению содержимого выбранных ячеек в одну общую.

Формула функций выглядит так:

=СЦЕП(текст1;текст2;. ) или =СЦЕПИТЬ(текст1;текст2;. )

Максимальное количество аргументов – 255.

Вставка и настройка функции

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

1. Для начала определяемся с ячейкой, в которой планируем объединить данные из других. Переходим в нее (выделяем) и щелкаем по значку “Вставить функцию” (fx).
2. В открывшемся окне вставки функции выбираем категорию “Текстовые” (или “Полный алфавитный перечень”), отмечаем строку “СЦЕП” (или “СЦЕПИТЬ”) и кликаем OK.
3. На экране появится окно, в котором нужно заполнить аргументы функции, в качестве которых могут быть указаны как конкретные значения, так и ссылки на ячейки. Причем последние можно указать как вручную, так и просто кликнув по нужным ячейкам в самой таблице (при это курсор должен быть установлен в поле для ввода значения напротив соответствующего аргумента). В нашем случае делаем следующее:
   • находясь в поле “Текст1” щелкаем по ячейке (A2), значение которой будет стоять на первом месте в объединенной ячейке;
   • кликаем по полю “Текст2”, где ставим запятую и пробел (“, “), которые будут служит разделителем между содержимыми ячеек, указанных в аргументах “Текст1” и “Текст3” (появится сразу же после того, как мы приступим к заполнению аргумента “Текст2”). Можно на свое усмотрение указывать любые символы: пробел, знаки препинания, текстовые или числовые значения и т.д.
   • переходим в поле “Текст3” и кликаем по следующей ячейке, содержимое которой нужно добавить в общую ячейку (в нашем случае – это B2).
   • аналогичным образом заполняем все оставшиеся аргументы, после чего жмем кнопку OK. При этом увидеть предварительный результат можно в нижней левой части окна аргументов.
4. Все готово, нам удалось объединить содержимое всех выбранных ячеек в одну общую.
5. Выполнять действия выше для остальных ячеек столбца не нужно. Просто наводим указатель мыши на правый нижний угол ячейки с результатом, и, после того как он сменит вид на небольшой черный плюсик, зажав левую кнопку мыши тянем его вниз до нижней строки столбца (или до строки, для которой требуется выполнить аналогичные действия).
6. Таким образом, получаем заполненный столбец с новыми наименованиями, включающими данные по размеру и полу.

Аргументы функции без разделителей

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

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

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

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

Например, мы добавляем запятую и пробел (“, “) в ячейку B16.

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

Но здесь есть один нюанс. Чтобы при копировании формулы функции на другие ячейки не произошло нежелательного сдвига адреса ячейки с разделителем, ссылку на нее нужно сделать абсолютной. Для этого выделив адрес в поле соответствующего аргумента нажимаем кнопку F4. Напротив обозначений столбца и строки появятся символы “$”. После этого можно нажимать кнопку OK.

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

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

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

Таким образом, функция СЦЕП (СЦЕПИТЬ) предлагает большую вариативность действий, что позволяет наилучшим образом представить объединенные данные.

Редактирование функции

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

Способ 1: изменения в окне аргументов

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

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

Мы получим результат с учетом скорректированных данных.

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

Альтернативный метод изменения аргументов функции – ручная корректировка формулы. Перейти в режим редактирования можно двумя по-разному:

1. Выделяем ячейку с функцией, затем щелкаем по строке формул и вносим в ней изменения.
2. Двойным щелчком мыши по ячейке с формулой активируем режим редактирования и корректируем формулу, непосредственно, в самой ячейке.

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

• адреса ячеек пишутся без кавычек;
• перечисление аргументов – через знак “;”
• если в качестве аргумента используется символ или текст, наличие кавычек с двух сторон указанного значения обязательно.

Заключение

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

Функция СЦЕПИТЬ в Excel: синтаксис и примеры использования

Чтобы объединить значения (текстовые, цифровые) из разных ячеек в одну, используется функция СЦЕПИТЬ. Ее аналог – & (амперсанд). Знак справляется с простейшими задачами. Но для объединения множества строк не подходит.

Синтаксис функции СЦЕПИТЬ

Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.

Общие правила использования функции СЦЕПИТЬ:

1. Для разделения аргументов применяется точка с запятой. Пропуск или использование других знаков приведет к отображению в ячейке с результатом кавычек.
2. Текстовые аргументы обязательно заключать в кавычки. В противном случае появляется ошибка – #ИМЯ? Числа и ссылки на ячейки – не нужно.
3. Чтобы соединяемые элементы в строке результата отобразились с пробелом, пробелы нужно добавлять в формулу с функцией. С помощью двойных кавычек с пробелом внутри (“ “) или с помощью пробела после текстового аргумента (“Иванов ”;…).

Как пользоваться функцией СЦЕПИТЬ в Excel

Начнем с простейших примеров.

Данные для объединения:

Ставим курсор в ячейку Е2 и вызываем мастер функций (кнопка fx). В категории «Текстовые» находим функцию СЦЕПИТЬ. Аргументы – ссылки на ячейки А2, В2, С2 и пробелы:

Результат объединения значений в ячейках:

Такой же результат получим с помощью амперсанда:

Один из аргументов – текст. Формула: =СЦЕПИТЬ(“слесарь”; ” “;A2;” “;B2;” “;C2). Результат:

И с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:

1. Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:
2. Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.
3. Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2).
4. Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.

Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.

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

Обратная функция СЦЕПИТЬ в Excel

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

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

1. Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
2. Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
3. Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.

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

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

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

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

Функция СЦЕПИТЬ относится к группе текстовых операторов Excel. Её основная задача — объединение в одной ячейке содержимого нескольких ячеек, а также отдельных символов. Начиная с версии Excel 2016, вместо данного оператора используется функция СЦЕП. Но в целях сохранения обратной совместимости оператор СЦЕПИТЬ тоже оставлен, и его можно использовать наравне со СЦЕП.

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

В качестве аргументов могут выступать, как текст, так и ссылки на ячейки, которые его содержат. Количество аргументов может варьироваться от 1 до 255 включительно.

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

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

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

Открывается Мастер функций. В категории «Текстовые» или «Полный алфавитный перечень» ищем оператор «СЦЕПИТЬ». Выделяем это наименование и жмем на кнопку «OK».

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

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

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

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

1. Выполняем задачу по тому же алгоритму, который описан выше.
2. Двойным щелчком левой кнопки мыши по ячейке с формулой активируем её для редактирования.

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

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

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

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

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

Выполняем те же действия, что и при первом способе применения функции СЦЕПИТЬ, вплоть до открытия окна аргументов оператора. Добавляем значение первой ячейки с данными в поле окна, как это уже было описано ранее. Затем устанавливаем курсор во второе поле, и выделяем ту пустую ячейку с пробелом, о которой шла речь ранее. Появляется ссылка в поле окна аргументов. Для ускорения процесса можно её скопировать, выделив и нажав сочетание клавиш Ctrl+C.

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

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

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

С помощью функции СЦЕПИТЬ можно быстро объединять данные нескольких колонок в одну.

С ячейками первой строки объединяемых колонок проделываем на выбор те действия, которые указаны во втором и третьем способе применения аргумента. Правда, если вы решили воспользоваться способом с пустой ячейкой, то ссылку на неё нужно будет сделать абсолютной. Для этого, перед каждым знаком координат по горизонтали и вертикали этой ячейки ставим знак доллара ($). Естественно, что лучше всего это сделать в самом начале, чтобы в другие поля, где содержится этот адрес, пользователь мог копировать его, как содержащий постоянные абсолютные ссылки. В остальных полях оставляем относительные ссылки. Как всегда, после выполнения процедуры, жмем на кнопку «OK».

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

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

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

Итак, существует два способа разделить текст по столбцам.

1-й способ разделить текст по столбцам. Мастер текста

Выделите столбец с текстом который хотите разделить. Затем выберите в верхней панели Данные — Текст по столбцам . Появится окно Мастера текста (разбор).

Если хотите разделить текст по пробелам или другим символам выбирайте «с разделителями».

В открывшемся меню выбираем пробел или нужный символ.

Выбираем формат текста и нажимаете «готово». Текст разделен!

Если все данные имеют одинаковую длину, можно разделить по фиксированной ширине (см. картинку 2)

2-й способ. Разделить текст в Excel формулами. Функция ПСТР

Если необходимо разделить текст по столбцам с использованием формулы, воспользуйтесь функциями =ПСТР() и =НАЙТИ().

Для того чтобы найти Фамилию в ячейку В2 занесите формулу

Где функция Найти будет находить кол-во символов до пробела, а функция =ПСТР() возвращать это кол-во символов.

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

Чтобы найти Отчество в ячейку D3 занесите формулу

Выглядит тяжеловато, но если разобраться вы увидите, что все несложно, особенно с примером:

Как сцепить ячейки Имя, Отчество, Фамилия

Для того чтобы сцепить данные в ячейках воспользуйтесь функцией = СЦЕПИТЬ () или структурой с использованием & (амперсант) — символа сцепки, например, внесите в ячейку D3 формулу

Очень часто с этим вопросом, задают вопрос «Как столбцы сделать строками?»

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

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

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

Способ 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) и копируем туда текст вот этой пользовательской функции:

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

=SUBSTRING(Txt; Delimeter; n)

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

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

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

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