Excel текст по столбцам при формуле

Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы

​Смотрите также​ фамилию каждого сотрудника​ Marangoni *», блок6=»79Q».​ предусматреть! А Если​ — «дата».​ ячейке Иванова, во​ (» «).​ для хранения каждого​Штат​MID​ найти символ пробела​Если «Мгновенное заполнение» включено,​ до Excel 2013,​Вот что Вам нужно​ указать, куда поместить​ данных. Так как​Country​В этой статье Вы​ для последующего использования​

​ Куда делось «TL»?​ после номера идет​Нажимаем кнопку «Готово».​ второй — Мария.​Формула будет такая.​ составные части распределенных​Аббревиатура​(ПСТР) – извлекает​ в ячейке​ но не предлагает​ то можете воспользоваться​ сделать, чтобы разбить​ разделённые ячейки.В разделе​ записи разделены пробелами​(Страна),​ найдёте несколько способов,​ в сводных таблицах​Легко определить начало​ трех значное число,​ Получилось так.​ Нам нужно написать​

• ​ =A2&» «&B2&» «&C2​ данных.​Столица​
• ​ часть текстовой строки​A2​
• ​ никаких вариантов, которые​ преимуществами нового инструмента​ такой столбец на​
• ​Column data format​ и запятыми, мы​Arrival Date​

Разбиваем ячейки в Excel при помощи инструмента «Текст по столбцам»

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

​ (то есть заданное​и начнём поиск​ соответствуют определённому шаблону,​ «​ два:​(Формат данных столбца)​ выбираем формат​(Ожидаемая дата прибытия)​ или целые столбцы​

• ​ программой Excel.​ блок6, основная сложность​
• ​ знак обрезает!​ из одной ячейки​

Разбиваем текстовые данные с разделителями по столбцам в Excel

​ Мария, во второй​ только слова, но​ весь столбец, где​Alabama, AL, Montgomery​ количество символов). Синтаксис:​ с первого символа.​​ Вы можете запустить​​Мгновенное заполнение​Запустите инструмент​ Вы можете выбрать​

​Delimited​ и​ в Excel 2010​Для этого необходимо попарно​ для формулы -​​vikttur​​ на большее количество​​ — Иванова. Как​​ и цифры. Можно​​ содержатся текстовые значения,​​Alabama​​=MID(text,start_num,num_chars)​​Замечание:​ этот инструмент вручную​​» и заставить Excel​​Text to Columns​

1. ​ формат данных отдельно​(С разделителями). Вариант​Status​ и 2013. Приведённые​ объедини тексты из​ определение места разрыва​: Пока еще не​ столбцов. Например, здесь​ это сделать быстро​составить предложение из данных​ которые требуется разделить.​AL​=ПСТР(текст;начальная_позиция;количество_знаков)​Если поиск начинается​ на вкладке​​ автоматически заполнять (в​​(Текст по столбцам),​​ для каждого столбца,​​Fixed width​(Статус).​ примеры и скриншоты​​ ячеек столбцов таблицы​​ этих блоков.​​ массивная, но если​​ разделили дату отдельно​​ в большой таблице,​​ ячеек в Excel​На вкладке​Montgomery​В качестве аргументов функции​ с первого символа,​Data​​ нашем случае –​​ как мы это​ в которые будут​(Фиксированной ширины) будет​Если в таблице есть​ иллюстрируют работу с​ в общие строки.​Vanya7819​ еще всплывут условия…​ по столбцам –​ смотрите в статье​.​Данные​​Извлекаем название штата:​​ указываем: какой текст​

   

   ​ Вы можете вообще​(Данные) >​ разбивать) данные, при​ делали в предыдущем​ помещены разделённые данные.​ рассмотрен чуть позже.​

   

​ хотя бы один​​ инструментами «Текст по​На рисунке проиллюстрированный пример​: TL сдесь не​=ЕСЛИ(E2=B2;»»;ЕСЛИ(ЕЧИСЛО(—ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);3;1));ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;3);ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;2)))​ число, месяц, год.​ «Как поменять местами​Можно установить формулы​в группе​=LEFT(A2,SEARCH(«,»,A2)-1)​ взять, позицию символа,​ пропустить аргумент​Flash Fill​ обнаружении определенной закономерности.​ примере. На первом​

1. ​ По умолчанию для​ Если все готово,​ столбец справа от​​ столбцам» и «Мгновенное​​ решения данной задачи.​​ играет роли. Основа​​Vanya7819​Для большей наглядности​​ столбцы в Excel».​​ в нужных ячейках​
2. ​Работа с данными​​=ЛЕВСИМВ(A2;ПОИСК(«,»;A2)-1)​ с которого нужно​​start_num​(Мгновенное заполнение) или​Если Вы ещё не​ шаге мастера выберите​ всех столбцов задан​ жмите​ столбца, который необходимо​ заполнение», кроме этого​​ Для этой цели​​ в 6 столбцах​​: Можно ли с​​ в таблице Excel​Здесь рассмотрим,​ бланка. Например, у​нажмите кнопку​​Извлекаем аббревиатуру штата:​​ начать, и сколько​
3. ​(нач_позиция) в формуле​ нажав сочетание клавиш​ знакомы с этой​ параметр​
   • ​ формат​​Next​ разбить, тогда первым​ Вы увидите подборку​ в примере должен​ (как в примере).​​ помощью формул расделить​​ можно выделить некоторые​​как разделить текст в​ нас есть такой​Текст по столбцам​​=MID(A2,SEARCH(«,»,A2)+2,SEARCH(«,»,A2,SEARCH(«,»,A2)+2)-SEARCH(«,»,A2)-2)​​ символов извлечь.​ и упростить её​​Ctrl+E​​ функцией, я попробую​​Fixed width​​General​(Далее), чтобы продолжить.​ делом создайте новые​​ формул для разделения​ использоваться оператор «&»​​Vanya7819​ ячейки по столбцам​ строки, столбцы (например,​ ячейке Excel на​ список клиентов с​.​=ПСТР(A2;ПОИСК(«,»;A2)+2;ПОИСК(«,»;A2;ПОИСК(«,»;A2)+2)-ПОИСК(«,»;A2)-2)​
   • ​Вы можете использовать аналогичные​​ до такого вида:​.​ кратко объяснить её​(Фиксированной ширины) и​(Общий). Мы оставим​На следующем шаге определяем​ пустые столбцы, в​ имён, текстовых и​ (END вместо оператора​: Буду очень ВАМ​ в данном примере?​ итоговые) выделить другим​ несколько ячеек.​ адресами.​Следуйте инструкциям в​Извлекаем столицу штата:​ формулы, чтобы разбить​=LEFT(A2,SEARCH(» «,A2)-1)​Существуют формулы, которые могут​ суть. Этот инструмент​ нажмите​ его без изменений​ разделители, которые содержатся​ которые будут помещены​​ числовых значений. Этот​​ «+»), который позволяет​ благодарен, — распишите​Большое СПАСИБО!!!​ цветом, разделительной линией.​Например, предложение из​

   ​Нам нужно составить предложение.​Столбцах мастера​​=RIGHT(A2,LEN(A2)-(SEARCH(«,»,A2,SEARCH(«,»,A2)+1)+1))​​ имена с суффиксами​=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1)​​ быть очень полезны,​​ анализирует данные, которые​Next​ для первых трёх​ в данных, и​ полученные данные. Этот​ урок поможет Вам​

4. ​ объединять значения ячеек​ подробно, как работает​vikttur​ Подробнее, смотрите в​ первой ячейки разнести​​ В ячейке пишем​​, чтобы указать способ​=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«,»;A2;ПОИСК(«,»;A2)+1)+1))​ в конце:​LEFT​ когда возникает необходимость​ Вы вводите на​(Далее).​ столбцов, а для​ ограничитель строк.​​ шаг необходим для​​ выбрать наилучший метод​ в формулах:​ каждая строка в​: Можно. Но чем​ статье «Разделительная линия​ по словам в​​ формулу.​​ разбиения текста на​А вот пример реальных​A​(ЛЕВСИМВ) и​ разбить ячейки или​ рабочий лист, и​В разделе​ четвёртого столбца установим​​Настраиваем разделители​​ того, чтобы результаты​ разбиения данных в​Как показано выше на​ VBA (в Вашем​​ больше различий в​​ строк в таблице​

   

   ​ несколько других ячеек,​=A2&» «&B2&» «&C2&»​ разных столбцах.​ данных из Excel​B​RIGHT​ столбцы с данными​ пытается выяснить, откуда​Data preview​​ формат​​. Если данные разделены​​ не были записаны​​ Excel.​ рисунке с помощью​ примере, — можно​ расположении блоков и​ Excel» тут.​ ФИО, дату, т.д.​ «&»проживает по адресу»&»​Примечание:​ 2010. Данные из​C​(ПРАВСИМВ) – возвращает​ в Excel. На​ они взялись и​(Образец разбора данных)​

​Data​​ одним или несколькими​ поверх уже существующих​Говоря в общем, необходимость​ символа «&» можно​​ в екселе). У​​ в самих блоках,​Можно разделить ячейку​Например, у нас​​ «&»г.»&» «&D2&» «&»ул.»&»​​ Для получения справки по​ первого столбца разбиты​​D​​ левую или правую​

1. ​ самом деле, следующих​​ существует ли в​​ настройте ширину столбцов.​

Разбиваем текст фиксированной ширины по нескольким столбцам

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

​ по диагонали и,​ есть список с​ «&E2&» «&»д.»&» «&F2&».»​ заполнению все действия​ на три отдельных​1​ часть текста из​

​ шести функций будет​ них какая-либо закономерность.​ Как видно на​ ведь в этот​

1. ​ выбрать все подходящие​​ сразу после столбца​​ Excel может возникнуть​ значения из разных​ по 25 вариантах​Для блок3 и​ в каждой части​ фамилиями, именами в​​Получилось такое предложение.​​ мастера читайте статью​ столбца:​​Полное имя​​ заданной ячейки соответственно.​
2. ​ достаточно в большинстве​​ Как только «Мгновенное​​ рисунке ниже, край​ столбец попадут даты​ варианты в разделе​Participant​ в двух случаях:​ ячеек без использования​ — в остальном​ блок4 достаточно​ написать текст. Читайте,​ одном столбце. Нам​По такому принципу составляем​ Разделение текста по​Урок подготовлен для Вас​Имя​ Синтаксис формулы:​ случаев –​ заполнение» распознает Ваши​ столбца символизирует вертикальная​ прибытия.Чтобы изменить формат​D​находится столбец​ ​ Во-первых, при импорте​ функции: =СЦЕПИТЬ(). В​ я постараюсь розобратся​=ЛЕВСИМВ(A4;3)​ как это сделать,​ нужно написать фамилии​ любые предложения.​
3. ​ различным столбцам с​ командой сайта office-guru.ru​Фамилия​=LEFT(text,[num_chars])​LEFT​ действия и вычислит​ линия, и чтобы​​ данных для каждого​​elimiters​

Разбиваем объединённые ячейки в Excel

​Status​ информации из какой-либо​ этом примере объединены​ сам по ВАШИМ​=ПСТР(A4;5;2)​ в статье «Как​​ в одном столбце,​​Если текст в​ преобразовать мастера текстов,​​Источник: https://www.ablebits.com/office-addins-blog/2014/02/27/split-cells-excel/​​Суффикс​=ЛЕВСИМВ(текст;[количество_знаков])​(ЛЕВСИМВ),​​ закономерность, Excel предложит​​ задать край следующего​ конкретного столбца, выделите​(Символом-разделителем является) или​, и мы собираемся​​ внешней базы данных​​ значения ячеек A2​

​ подсказкам. «TL» -​В блок5 перед​ разделить ячейку в​ а имена в​ ячейках уже написан,​ или нажмите кнопку​Перевел: Антон Андронов​2​В качестве аргументов указываем:​​MID​​ вариант, и последовательность​ столбца, просто кликните​ его, кликнув по​ ввести свой вариант​ добавить между ними​

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

​ или с веб-страницы.​ и B2 с​ не играет роли​ R может быть​ Excel» здесь.​​ другом столбце. Например,​​ но нам нужно​Справка​Автор: Антон Андронов​Robert Furlan Jr.​ какой текст взять​

​(ПСТР),​ записей в новом​ в нужном месте.​ нему в области​ разделителя в поле​ новые столбцы​ При таком импорте​ добавленным между ними​ — лучше бы​ пробел, нужно добавить​Удобнее работать в​ чтобы определить пол​вставить дополнительные слова​в мастере разделения​Примечание:​Robert​ и сколько символов​RIGHT​ столбце появится буквально​ Двойной щелчок по​

​Data preview​Other​Last Name​ все записи копируются​ символом пробела (указанный​ его тоже забрать.​ функцию:​ таблице, где в​ людей в списке.​перед имеющимися, то​ текста столбцов.​Мы стараемся как​

​Furlan​ извлечь. В следующем​(ПРАВСИМВ),​ за мгновение.​ вертикальной линии удалит​​(Образец разбора данных),​​(Другой).В нашем примере​​,​​ в один столбец,​​ в формуле между​​ Спасибо!!!​​=ПОДСТАВИТЬ(ПСТР(A4;ПОИСК(«R»;A4)+1;3);» «;»»)​​ ячейках не видны​ Подробнее об этом​

​ сделать это можно​Список в Excel​ можно оперативнее обеспечивать​Jr.​ примере формула будет​FIND​Таким образом, при помощи​ край столбца, а​ а затем установите​ мы выбираем​Country​ а нужно, чтобы​ кавычками как текстовое​vikttur​Для блок1:​ нули. Как их​ читайте в статье​ с помощью формулы.​можно корректировать формулами​ вас актуальными справочными​

​Извлекаем имя:​ извлекать левую часть​(НАЙТИ),​​ этого инструмента Вы​​ если Вам нужно​ желаемый формат в​Space​и​​ они были помещены​​ значение).​: Формулы. ради интереса​​=ПСТР(ПОДСТАВИТЬ(A4;»*»;»»);ПОИСК(«-«;A4)+2;15)​​ убрать, скрыть, заменить,​ «В Excel по​ У нас такой​ – имя и​

​ материалами на вашем​=LEFT(A2,FIND(» «,A2,1)-1)​ текста из ячейки​SEARCH​ можете взять какую-то​ переместить границу столбца​ разделе​​(Пробел) и​​Arrival Date​​ в разных столбцах.​​Для объединения текстов в​ домучил определение блок2​​В конце строки​​ читайте в статье​

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

​ имени определить пол».​ список.​ отчество заменить на​ языке. Эта страница​=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1)​A2​(ПОИСК) и​ часть данных, находящихся​ в другое место,​Column data format​​Comma​​.Если кто-то забыл, я​​ Во-вторых, при разбиении​​ одну строку программа​​ и блок6.​​ перед знаком «*»​​ «Как убрать нули​​Выделяем ячейки, текст​​Нам нужно перед фамилиями​​ инициалы, соединить слова​​ переведена автоматически, поэтому​​Извлекаем фамилию:​вплоть до позиции​LEN​ в одном или​ просто перетащите вертикальную​(Формат данных столбца).​(Запятая), а также​ напомню быстрый способ​

Пример 1

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

​=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(«​ первого найденного пробела.​(ДЛСТР). Далее в​ нескольких столбцах, и​

• ​ линию мышью. На​На этом же шаге​

  ​ ставим галочку напротив​
​ вставить сразу несколько​

• ​ чтобы получить возможность​ функцию​

  ​ и без; "*"​
​ а может и​

​Khlopkov​ разделить или весь​ В ячейке нового​ предложение, вставить слова​

​ содержать неточности и​​ «,A2,1)+1))​​=LEFT(A2,SEARCH(» «,A2)-1)​​ этом разделе я​ ввести их в​ самом деле, все​ мастера Вы можете​ параметра​ столбцов на лист​

​ качественнее настроить работу​
​=СЦЕПИТЬ(A2;" ";B2), которая​

​ — то оно​ не быть, поэтому,​: Здравствуйте,​ столбец.​ столбца пишем формулу.​ в список Excel.​ грамматические ошибки. Для​=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«​​=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1)​​ кратко объясню назначение​​ новый столбец. Думаю,​​ эти инструкции подробно​ выбрать, в какой​Treat consecutive delimiters as​ Excel. Для этого​​ фильтра, сортировку или​​ объединяет текстовые значения​ есть, то нет,​

​ если нужно удалить​​Подскажите, есть такая​Заходим на закладке «Данные»​=»Квартиросъемщик»&» «&A8​У нас есть​​ нас важно, чтобы​​ «;A2;1)+1))​LEN​ каждой из этих​

​ Вы лучше поймёте​
​ расписаны в верхней​

​ столбец поместить разделённые​​ one​​ выберите столбец​​ для более детального​ в одну строку.​ то с пробелом;​ последний пробел, формулу​ функция как текст​

​ в раздел «Работа​
​Копируем эту формулу​

​ таблица, где фамилия,​ эта статья была​Извлекаем суффикс:​(ДЛСТР) – считает​ функций и приведу​ о чём я​ части диалогового окна​​ данные. Для этого​​(Считать последовательные разделители​Status​

​ анализа.​
​ Однако на практике​

​ блок6 то с​​ блок1 необходимо дорастить:​ по столбцам с​ с данными» и​ вниз по столбцу.​ имя и отчество​

​ вам полезна. Просим​
​=RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))​

​ длину строки, то​ примеры, которые Вы​​ говорю из следующего​​Так как каждый ID​

​ кликните по иконке​
​ одним). Этот параметр​

​, кликнув по его​Разбиваем ячейки при помощи​ мы не рекомендуем​ пробелом после числа,​=СЖПРОБЕЛЫ(ПСТР(…))​ разделителями (Данные текст​​ выбираем функцию «Текст​​ Получился такой список.​

Пример 2

​ написаны в разных​ вас уделить пару​=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))​ есть количество символов​ сможете использовать в​ примера.​

• ​ в заданной ячейке.​

  ​ своих книгах Excel.​
​Первым делом, убедитесь, что​
• ​ символов, устанавливаем линию​

  ​ терминах Microsoft эта​ разбиения данных, например,​
​ нажатой левую кнопку​ столбцам»​

• ​ а в место​

  ​ имеет общего параметра​
​ и блок6. Если​

​ ли формулой сделать​​ появившемся окне «Мастер​​ или скопировать значение​ разместить их в​ помогла ли она​ разбить имена с​

​ Синтаксис формулы:​
​Самая распространённая ситуация, когда​

​ инструмент «Мгновенное заполнение»​ границы столбца на​ иконка называется​ когда между словами​ мыши, протащите указатель​Как разбить объединённые ячейки​

Пример 3

​ нее просто применять​ и т.д.​ условия задачи больше​ то же самое?​

• ​Практического применения почти​

  ​ не будут меняться​
​Пример прилагаю​

• ​ «Формат исходных данных»​

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

• ​=ДЛСТР(текст)​

  ​ формулы – это​
​ этот параметр на​

Пример 4

​ показано на рисунке​) справа от поля​ более последовательных пробела.​ нужное количество столбцов​Разделяем данные в Excel​ &, так как​ никакого, потому что​

• ​ 2013 при помощи​

  ​ она имеет ряд​ такие формулы нужно​
​ других строках структурно​: Здравствуйте.​

• ​ разделителями». Нажимаем кнопку​

  ​ с формулами -​
​ Excel, есть специальная​

• ​ приводим ссылку на​

  ​ отчеством, находящимся в​
​ символов в ячейке​

Пример 5

​ из одного столбца​File​На следующем шаге выберите​(Поместить в) и​. Этот параметр может​ Затем кликните правой​ инструмента «Мгновенное заполнение»​ своих недостатков.​ писать под конкретную​ похоже на показанное​34,5 Кб​ «Далее».​ удалить.​

• ​Khlopkov​

  ​Здесь указываем в строке​
​Ещё один способ​
• ​ варианта.​

  ​ языке) .​
​A​

• ​:​

  ​ рисунке ниже показано,​
​Options​

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

​ мелкие изменения данных​ сочинить чего-нибудь на​
​: Спасибо )))​
​ «Символом – разделителем​

​ добавить текст, числа,​

office-guru.ru

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

​Первый вариант.​​Можно разделить содержимого ячейки​B​=LEN(A2)​ какого результата мы​(Параметры) >​ поместить результат, как​ в которые Вы​ разбиваете, содержатся какие-либо​ в контекстном меню​ текстовых данных)​ СЦЕПИТЬ по сравнению​ повлекут неработоспособность формул​ досуге.​vikttur​ является» — «пробел».​ символ к тексту​У нас такой​ и распространение составные​C​=ДЛСТР(A2)​ пытаемся достичь:​

​Advanced​ это было сделано​ хотите поместить разделённые​ значения, заключённые в​ выберите команду​​Инструмент «​​ с оператором &:​ блок2 и блок6.​Казанский​​: Вариант:​​ Поставили «пробел», потому​​ в ячейке, смотрите​​ список.​

​ части нескольких смежных​​D​

• ​Если имена в Вашей​Вы легко сможете разбить​(Дополнительно) >​ в предыдущем примере,​ данные. К сожалению,​ кавычки или в​

• ​Insert​Текст по столбцам​Ограниченное максимальное количество объединяемых​

​Vanya7819​

​: Попробуйте. Можно еще​​=ПСТР(B2&» №»;1;ПОИСК(» №»;B2&»​ что в нашем​ в статье «Добавить​Нам нужно написать в​ ячеек. Например, если​1​ таблице содержат отчества​ такие имена на​Automatically Flash Fill​ а затем нажмите​ невозможно импортировать разделённые​ апострофы, и Вы​(Вставить).​» действительно очень удобен,​ значений ячеек до​: Спасибо!!!​ доделать ограничение второй​ №»)-1)​ списке имя и​ текст в ячейки​

1. ​ ячейке D1 ФИО​ лист содержит​Полное имя​ или суффиксы, то​

2. ​ два столбца при​​(Автоматически выполнять мгновенное​​Finish​​ данные на другой​​ хотите, чтобы такие​​Результат будет примерно таким,​​ когда нужно разделить​

   

3. ​ 255.​​У меня ещё​​ подстроки в 3​=ЕСЛИ(E2=B2;»»;ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2))​ фамилия написаны через​

   ​ с текстом Excel».​​ одним предложением. Пишем​Полное имя​Имя​ потребуются немного более​ помощи следующих формул:​ заполнение).​(Готово).​​ лист или в​ ​ участки текста не​ что Вы видите​

support.office.com

Объединить слова из разных ячеек в текст Excel.

​ данные из одного​​Функция объединяет только текстовые​ такой вопрос, может​ слова, т.е.​Khlopkov​ пробел. Если бы​С помощью формул​ в этой ячейке​
​столбца, можно разделить столбец​Отчество​ сложные формулы с​Извлекаем имя (столбец First​Теперь давайте посмотрим, как​Если Вы объединили несколько​ другую рабочую книгу,​ разбивались, а рассматривались​ на рисунке ниже​ столбца по нескольким​ значения, а оператор​ глупый ))), а​
​METEO GRIP E+​
​: Да работает, но​ имя и фамилия​
​ можно преобразовать список,​ (D1) формулу. Нажимаем​ на двух столбцов​Фамилия​ использованием функции​ name):​ можно автоматически разбить​
​ ячеек на листе​ попытка сделать это​ как цельные значения.​ (новые столбцы вставлены​ в Excel 2013,​ & объединит любые​ ВПР, в данной​ TL -> METEO​ есть но.. не​ (др. слова) были​
​ где имя, отчество​ на ячейку (активной​ —​2​MID​=LEFT(A2,SEARCH(» «,A2,1)-1)​​ данные по ячейкам.​​ Excel и теперь​
​ приведёт к сообщению​
​ Например, если Вы​
​ слева от выделенных​ 2010, 2007 или​
​ значения ячеек.​
​ ситуации, ничем не​ GRIP E+​ получается вытащить из​ написаны через запятую,​
​ и фамилия написаны​
​ сделаем).​имени​White, David Mark​(ПСТР).​=ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)-1)​ Итак, Вы включили​
​ хотите вновь разбить​ об ошибке выбора​
​ выберите в качестве​ столбцов):​ 2003.​​Функция требует немного больше​ поможет?​​Vanya7819​
​ текста число если​ то мы бы​ полностью, в​Заходим на закладке​и​David​
​Вот такие формулы нужно​Извлекаем фамилию (столбец Last​ инструмент «Мгновенное заполнение»,​
​ их по отдельным​ конечной ссылки.​ разделителя запятую, а​Примечание:​
​«Текст по столбцам» позволяет​
​ затрат времени и​vikttur​
​: Виктор, Спасибо за​ к примеру​ указали разделителем «запятая».​​список с фамилией и​​ «Формулы» в раздел​Фамилии​Mark​ использовать, когда имена,​ name):​
​ и начинаете вводить​ столбцам, откройте вкладку​Совет:​ в качестве ограничителя​
​Если у Вас​
​ разбивать значения ячеек,​ ресурсов как у​: Слабо представляю. Почему​
​ ответ! Блок2 и​0,02 г №50​В этом окне, в​ инициалами​ «Библиотека функций», выбираем​столбцом.​White​
​ которые требуется разбить,​=RIGHT(A2,LEN(A2)-SEARCH(» «,A2,1))​ с клавиатуры данные,​Home​Если Вы не​ строк – кавычки​ нет столбцов, следующих​
​ отделённые разделителями, или​ программы Excel (в​ ВПР, а не,​ блок6 в основном​ табл​​ разделе «Образец разбора​. Например, в ячейке​​ – «Текстовые», и​Советы:​
​Извлекаем имя:​ содержат отчество или​
​=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(» «;A2;1))​ которые нужно поместить​
​(Главная) и в​
​ хотите импортировать какой-то​ («), тогда любые​ непосредственно за тем,​ выделять данные фиксированной​ процессе вычислений с​ скажем, СЛУЧМЕЖДУ? :)​ будет, как в​25 мг №20упак​ данных» сразу видно,​ написано.​ выбираем функцию «СЦЕПИТЬ».​ ​=MID(A2,SEARCH(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(«​ только один инициал​Для тех, кому интересно,​ в отдельные ячейки.​
​ группе команд​ столбец (столбцы), который​ слова, заключённые в​ что Вы хотите​​ ширины (когда все​​ использованием сложных формул),​Одна из самых популярных​ примере. Если у​Serge​ какие столбцы у​В соседнем столбце пишем​ В появившемся окне​Сведения об альтернативном способе​ «,A2,1)+1))​ отчества посередине.​ что означают эти​ По мере ввода​Alignment​

excel-office.ru

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

​ показан в области​​ кавычки (например, «California,​ разбить, то необходимость​ значения содержат определённое​​ так и у​ операций в Exel​ ВАС будет время​: Ну так рисуйте​ нас получатся. Нажимаем​
​ такую формулу.​ указываем адреса ячеек,​ распределения текста по​=ПСТР(A2;ПОИСК(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«​A​ формулы, я попробую​ Excel будет пытаться​(Выравнивание) нажмите маленькую​Data preview​ USA»), будут помещены​ в этом шаге​ количество символов). Давайте​ пользователя (в процессе​ по работе с​
​ — напишите ещё​ пример согласно правилам​ кнопку «Далее».​=СЦЕПИТЬ(ЛЕВСИМВ(СЖПРОБЕЛЫ(A1);НАЙТИ(» «;СЖПРОБЕЛЫ(A1);1));ПСТР(СЖПРОБЕЛЫ(A1);НАЙТИ(» «;СЖПРОБЕЛЫ(A1);1)+1;1);».»;ПСТР(СЖПРОБЕЛЫ(A1);НАЙТИ(«​
​ которые нам нужно​ столбцам см. в​ «;A2;1)+1))​B​ объяснить более подробно.​ распознать шаблон в​ чёрную стрелку рядом​(Образец разбора данных),​ в одну ячейку.​ отпадает и его​ рассмотрим эти варианты​
​ ввода функции в​ текстом – это​ формулы для них.​ — будет решение.​Здесь, в разделе​ «;СЖПРОБЕЛЫ(A1);НАЙТИ(» «;СЖПРОБЕЛЫ(A1);1)+1)+1;1);».»)​ объединить в одно​ статье Разделение текста​Извлекаем отчество:​C​SEARCH​ вводимых значениях, и​ с кнопкой​​ то выделите его​ Если же в​ можно пропустить. Главное​ подробнее:​ строку формул).​ объединение текстовых строк.​
​ Они могут быть​Khlopkov​ «Поместить в:» указываем​Получилось.​ предложение. Получилось так.​ ​ по столбцам с​​=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))​D​(ПОИСК) или​ как только он​Merge & Center​ и выберите вариант​ качестве ограничителя строк​ не упустите, что​Как разбить текст с​Интересный факт! В Excel​ Например, объединение нескольких​ любой сложности, только​
​: Простите, что сразу​ диапазон столбцов, в​Если между словами​ФИО написаны без пробелов.​ помощью функций.​=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))​1​FIND​
​ его распознает, данные​(Объединить и поместить​
​Do not import column​ установить значение​ пустых столбцов должно​ разделителями по столбцам​ нет обратной функции​ слов в одну​ чтоб был желаемый​

​ такого варианта не​ которые поместятся новые​ появились лишние пробелы,​ Чтобы это исправить,​Можно объединить ячейки, содержащие​Извлекаем фамилию:​Полное имя​(НАЙТИ) – это​ автоматически будут вставлены​ в центре). Далее​
​(Пропустить столбец) в​None​ быть не меньше,​Как выделить текстовые данные​ РАСЦЕПИТЬ и нет​ строку. На практике​ результат. Ещё раз​ было в примере.​
​ данные.​ их можно удалить.​ формулу нужно доработать.​ функцию СЦЕП или​=LEFT(A2,FIND(» «,A2,1)-2)​Имя​ абсолютно идентичные функции,​ в остальные ячейки.​

excel-office.ru

Текст по столбцам формулой

​ из выпадающего списка​​ разделе​
​(Нет), тогда слово​ чем количество столбцов,​ фиксированной величины​ специального символа оператора.​ в процессе рутинной​ Спасибо!!!​vikttur​
​Внимание!​

​=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-2)​​Отчество​

​ которые выполняют поиск​​ Чтобы понять, как​
​ выберите​Column data format​
​ «California» будет помещено​

​Выполните следующие действия.​​Как Вы понимаете, эти​Фамилия​ позиции определенной текстовой​

​ это работает, посмотрите​​Unmerge Cells​(Формат данных столбца).​ в один столбец,​

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

​ запятой написать​​Примечание:​
​ формулы работают не​2​ строки в заданной​ на рисунок ниже:​
​(Отменить объединение ячеек).​Нажмите​ а «USA» –​Выделите столбец, который требуется​

​ или какое-то другое​​ с комбинациями текстовых​ Имена и Фамилии​ Спасибо!!! Только я​ подправил, не вдаваясь​ иначе информация в​ лишние пробелы в​

​» «​​ Диапазон, содержащий столбец, который​ только для разделения​Sally K. Brooke​
​ ячейке. Синтаксис формулы:​

​Как видите, я ввёл​​Таким образом объединение ячеек​Finish​ в другой.​ разбить. Затем откройте​
​ мероприятие. На рисунке​

​ функций. Например маркером​​ сотрудников в одну​ не совсем понял​ в подробности:​ них сотрется и​ Excel». Этими же​
​. Получилась такая формула.​ вы хотите разделить​
​ имён в Excel.​
​Sally​
​=SEARCH(find_text,within_text,[start_num])​ только пару имён​ будет отменено, но​(Готово)!​
​В нижней части диалогового​
​ вкладку​
​ ниже видно, что​
​ для расцепления текста​ ячейку.​ ВАШ ответ. Результат​=ЕСЛИ(E2=B2;»»;ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;2))​ заменится на новую.​ способами можно удалить​=СЦЕПИТЬ(A1;» «;B1;» «;C1)​ может включать любое​
​ Вы можете использовать​
​K.​=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])​ в столбец​ удовольствие от результата​Если данные состоят из​ окна находится область​Data​ в столбце​ послужит символ пробела​В примере показанным на​

​ то что надо,​​ПРАВСИМВ(…;1;2) — извлекает​ Если нет пустых​ пробелы между числами,​Получилось так.​
​ количество строк, но​ их для разбиения​Brooke​

​В качестве аргументов Вы​​B​ будет испорчено тем,​ текстовых или числовых​Data preview​(Данные) >​Participant​ « » ,​ рисунке таблица содержит​ только как это​ только два знака.​ столбцов, можно предварительно​ в формуле, т.к.​Теперь формулу копируем вниз​

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

​Data Tools​​(Участник) перечислены имена​ а дальше:​ первые два столбца:​

​чтобы расцепить только имя​​Имя – ячейки в​Казанский​: Работает!! Спасибо!​ это сделать, смотрите​

​ привести к ошибке​​Второй вариант.​ столбца. Это важные​ нескольким. Например, следующие​=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1)​ нужно искать, а​ ячейки именами из​ столбце. Думаю, Вы​ можете разбить их​Next​ >​ ожидаемая дата прибытия:​ используем формулу: =ЛЕВСИМВ(C2;ПОИСК(«​ этой колонке содержат​: Это функция, написанная​Ну раз уж​ в статье «Как​ при подсчете или​

​Вместо функции «СЦЕПИТЬ»​​ из них достаточно​ формулы Вы можете​Извлекаем отчество:​
​ также позицию символа,​ столбца​ догадались, что нужно​ на несколько столбцов​(Далее) будет разумным​Text to Columns​Необходимо разбить этот текст​ «;C2;1))​ текстовые данные полученные​ на VBA. См.​
​ как говориться просить​ добавить строку, столбец​ формула не будет​ можно просто нажать​ пустыми столбцами справа​ использовать, чтобы разбить​=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(«​ с которого следует​

​A​​ снова использовать функцию​
​ следующим способом.​ пролистать это поле​(Текст по столбцам).​ на отдельные столбцы,​чтобы расцепить только фамилию​ из базы картотек​

​ Alt+F11​​ о помощи до​ в Excel» тут.​ считать.​

planetaexcel.ru

Лучшая альтернатива функции СЦЕПИТЬ и объединить текст в Excel

​ кнопку амперсанд (&).​ от выбранных столбец,​ текстовые данные, разделённые​ «,A2,1)+1))​ начать поиск. В​. Если вы довольны​Text to Columns​К примеру, есть список​ и убедиться, что​Откроется диалоговое окно​ чтобы таблица имела​ – формула: =ПРАВСИМВ(C4;ПОИСК(«​ кадрового отдела.​vikttur​

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

​ до конца))​Можно указать формат​Можно данные в​

1. ​ Формула получится такая.​ который будет предотвратить​ запятыми:​=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«​ нашем примере​
2. ​ результатом, просто нажмите​(Текст по столбцам),​ товаров с ID​

​ Excel правильно распределил​Convert Text to Columns​ следующие данные (слева​ «;C4;1)+1)​Фамилия – в данном​: Строка 8: «…85​А если нет​ Ячеек столбцов. Например,​

​ строке переместить из​=A2&B2&С1​ перезапись по данные,​A​

​ «;A2;1)+1))​SEARCH(» «,A2,1)​Enter​ чтобы разбить данные​ и наименованием, причем​ все данные по​ wizard​ направо):​Фактически функция СЦЕПИТЬ устарела,​

​ столбце находиться только​ T XL -​ № то вместо​ если вы делите​ последних ячеек в​Результат такой же,​ которые распространяться данные​B​Извлекаем фамилию:​или​, и весь столбец​ из одного столбца​ идентификатор товара –​ столбцам.​(Мастер распределения текста​

​First Name​ но все еще​ фамилии сотрудников.​ Marangoni *», блок6=»85​
​ пустого значения, значение​ столбец на дни​ первые,​ как в первом​ в любой смежных​C​=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))​ПОИСК(» «;A2;1)​ будет заполнен именами.​ на два или​ это 9 символов,​Осталось сделать всего две​

​ по столбцам). На​

​(Имя),​ доступна пользователям в​В третьем столбце «Имя​

1. ​ T XL»​ =1​ и месяцы, год,​
2. ​перевернуть строку​ варианте. Если нет​ столбцах. При необходимости​D​
3. ​=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))​говорит о том,​ Очень умный инструмент,​ более столбцов.​ которые стоят перед​ вещи – выбрать​ первом шаге мастера​Last Name​ новых версиях программы​ + Фамилия» необходимо​

​Строка 7: «…​Khlopkov​ то можно указать​. Например, в ячейках​ пропусков между словами,​ вставьте количество пустые​1​Функция​ что мы хотим​ не правда ли?​Если Вы уже обновились​ наименованием этого товара:​

• ​ формат данных и​ Вы выбираете формат​(Фамилия),​
• ​ Excel.​ объединить имя и​ TL 79Q -​

​: Блин! Всего не​ «Формат данных столбца»​ написано: в первой​ то вставьте пробел​ столбцы, будет достаточно​

exceltable.com

​Полное обозначение​

Эта функция крайне удивительна и может послужить вам в разных ситуациях.

Самое простое её использование — разделить текст по нескольким столбикам, но её также можно использовать и для других целей.

Сегодня, я продемонстрирую несколько вариантов использования функции «Текст по столбцам».

Итак, начнём!

Как использовать функцию «Текст по столбцам»

Все очень просто — щелкните «Данные» -> «Текст по столбцам»

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

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

Деление строки

Допустим, у нас есть такая табличка:

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

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями», так как мы будем делить строку по пробелу;

• На втором этапе, собственно, указываем что пробел в нашем случае будет разделителем строки;

• И на третьем этапе указываем куда поместить данные;

• Подтверждаем.

Результат:

Примечание:

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

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

Делим электронную почту на имя пользователя и домен

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

Допустим, у нас есть следующая табличка:

Я просто придумал эти почты исходя из фильмов по супергероям 🙂

Разделителем, как вы могли догадаться, в этом случае будет знак «@».

Давайте разделим строки.

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями», так как мы будем делить строку по знаку «@»;

• На втором этапе, в опции «другой» укажите наш разделитель;

• И укажите куда поместить данные после обработки;

• Подтвердите.

Результат:

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

Бывает такое, что из множества ссылок вам нужно «вытащить» только главные адреса сайтов.

Например, в случае https://yandex.ru/primer1 главный адрес будет yandex.ru.

Итак, допустим у нас есть следующая табличка:

Давайте разделим строки и получим главные адреса сайтов!

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе выберите «другой» и укажите «/»;

• Укажите куда нужно поместить данные;

• Подтвердите.

Результат:

Далее можно очистить таблицу от ненужных столбцов и дубликатов.

Важная информация: в случае, если ваши ссылки не имеют https:// в начале — все будет еще проще, вы получите главный адрес сайта в первом столбике.

Приведение дат к общему формату

Часто бывает так, что при импорте дат из разных источников они имеют разный формат.

Но что делать если этих данных очень много, а необходимо сделать так, чтобы все даты были в одном, «общем», формате?

Допустим, у нас есть такая табличка:

Давайте приведем все даты к одному формату!

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе уберите все галочки;

• На третьем этапе выберите опцию «дата — ДМГ» и укажите куда поместить обработанные данные;

• Подтвердите.

Готово! Таким образом даты были отформатированы.

Смена формата с текстового на числовой

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

Такое может быть по нескольким причинам:

• Перед числом есть знак “ ‘ “;

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

Допустим, у нас есть такая табличка:

Давайте переведем их в формат «Числовой».

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе уберите все галочки;

• нужная опция «общий»;

• Подтвердите.

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

Убираем несколько первых символов значения каждой ячейки

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

Допустим, у нас есть такая табличка:

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

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «фиксированной ширины»;

• На втором этапе — поставьте указатель на нужное количество символов (в нашем случае пять);

• Укажите куда нужно поместить данные;

• Подтвердите.

Результат:

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

Преобразуем числа со знаком минус на конце в отрицательные числа

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

Допустим, у нас есть такая табличка:

Давайте обработаем эти числа.

Как это сделать?

Пошаговая инструкция:

• Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

• На первом этапе выбираем «с разделителями»;

• На втором этапе уберите все галочки;

• На третьем этапе щелкните на «Подробнее…»;

• В открывшемся окне поставьте галочку на опции указанной на картинке ниже;

• Укажите куда поместить обработанные данные;

• Подтвердите.

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

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

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

Пример 1. Делим текст с ФИО по столбцам с помощью формул

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

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

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

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

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

=НАЙТИ(" ";A2;1)

и протянем вниз. Формулу объяснять не буду — смотрите предыдущую статью

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

=НАЙТИ(" ";A2;H2+1)

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

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

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

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

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

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

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

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

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

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

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

=ПСТР(A2;H2+1;I2-H2)

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

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

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

Получаем функцию

=ПСТР(A2;I2+1;50)

Далее выделяем все три ячейки и протягиваем формулы вниз и получаем нужный нам результат. На этом можно закончить, а можно промежуточные расчеты позиции пробелов прописать в сами формулы деления текста. Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(» «;A2;1), а расчет второго пробела в ячейке I2 — НАЙТИ(» «;A2;H2+1) .  Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)

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

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

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

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

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

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

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

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

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

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

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

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

=НАЙТИ(B1;A1;1)

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

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

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

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

Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать. 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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