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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Формула счетесли в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Содержание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция сцепить в excel

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

Читать еще:  Как в excel сделать формулу на весь столбец

Общая информация

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

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

Функция СЦЕПИТЬ имеет также символьный аналог в виде значка амперсанда & (Shift+6 в английской раскладке клавиатуры), который намного удобнее использовать.

На заметку! В последней версии редактора 2016 года данный инструмент на английском называется Concat.

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

Рассмотри примеры использования СЦЕПИТЬ. Будем идти от простых задач к более сложным. В конце разберем примеры с несколькими условиями.

Пример 1. Свести в одну ячейку фамилию имя и отчество. В строку формул запишем выражение со ссылками на ячейки.

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

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

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

Пример 2. Объедим ячейки различных форматов. Будем использовать текстовые вставки и ссылки на ячейки с датами и числовым форматом. Исходные данные:

Формула будет иметь такой вид:

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

Пример 3. Объединение ячеек через знак препинания или символ.

Здесь кавычки помогают правильно разграничить символы.

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

Обратная функция

На первый взгляд обратная функция СЦЕПИТЬ – РАСЦЕПИТЬ. Однако это не так. Для отбора слов в одной ячейке используются следующие формулы:

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

Рассмотрим использование функций на основании первого примера из статьи:

Задача 1. Нужна только фамилия:

Задача 2. Аналогично, только необходимо имя:

Задача 3. В конце отобразим отчество:

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

СЦЕП (функция СЦЕП)

Функция CONCAT объединяет текст из нескольких диапазонов и (или) строк, но не предоставляет разделители или аргументы аргументы ignoreempty.

Функция CONCAT заменяет функцию СЦЕПИТЬ. Функция СЦЕПИТЬ (CONCATENATE) также будет поддерживаться для совместимости с более ранними версиями Excel.

Примечание: Эта функция доступна в Windows или Mac, если у вас есть Office 2019 или подписка на Office 365. Если у вас есть подписка на Office 365, убедитесь в том, что у вас установлена последняя версия Office.

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

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

Например, выражение =СЦЕП(«Не»;» «;»слышны»;» «;»в»;» «;»саду»;» «;»даже»;» «;»шорохи.») вернет строку Не слышны в саду даже шорохи.

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

Примечания

Если объединенная строка содержит свыше 32767 символов (ограничение для ячейки), функция СЦЕП вернет ошибку #ЗНАЧ!.

Скопируйте данные примеров из приведенных ниже таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция расцепить в 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.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
×
×
Adblock
detector