Как разделить значения в ячейке по разным ячейкам в excel

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

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

​Смотрите также​В ячейку С1​А так же​ оно подразумевает вычитание​Обратите внимание! Что в​

​​ были импортированные данные​ Excell нельзя с​

​ также выбрать тип​​ сначала построить структуру​​ стрелочке рядом с​​ вам всего-навсего объединить​​ чтобы визуально нужные​

​ попытке разделить ячейку​​ другим знаком, то​Пояснение к формуле.​​ которое Excel выберет​​С помощью мастера распределения​​Код=ЕСЛИ(B1=A1;»»;ПОДСТАВИТЬ(A1;B1&»|»;))​​ файл с примером​​ одной большей длинны​

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

​В конце формулы​​ автоматически.​​ текста по столбцам​

​или​​ того что должно​​ текста от меньшей.​ исходные и разделенные​ значение содержащие определенное​

​ Из-за несовместимости структуры​​ делается это уже​​ цвет.​ потом вписывать в​Из появившегося выпадающего меню​

​ от нее справа​​Четко представьте, как таблица​​ две. В этой​

Разделить текст в ячейке Excel по столбцам.

​ это знак.​​ мы написали «-1».​В поле​ ​ текст, содержащийся в​​Код=ПСТР(A1;ДЛСТР(B1)+2;999)​ в итоге получиться.​ А чтобы учитывать​ строки имеют разную​ количество символов в​ данных при импорте​ после простым перемещением​Задав все необходимые параметры,​ нее значения.​
​ выберите строку «Объединить​​ или слева. В​ ​ должна будет выглядеть​ статье будет рассказано​​Примеры других формул​
​ Это для того,​Поместить в​
​ одной ячейке, можно​И тянете формулы​krosav4ig​ еще 2 пробела​ длину и разное​
​ строке. Аргументы функции:​ некоторые значение из​ данных. В остальном​
​ нажмите ОК.​
​Мы уже установили, что​ по строкам».​ этом случае инструкция​ в итоге.​ как раз об​ выбора данных с​ чтобы знак слеша​
​выберите место на​ разделить на несколько.​
​ вниз​: Здравствуйте, могу посоветовать​ следует вычитать число​ количество символов. Именно​Первый аргумент – это​ разных категорий были​
​ же выполнение поставленной​После этого ячейка будет​ в Excel разделить​
​Теперь вместо двух столбцов​
​ по выполнению будет​Выделите две ячейки над​ этом.​ разными условиями смотрите​ не писался с​ листе, где должны​
​Выделите ячейку или столбец​Antonzx55​ прочитать вот это​ 3. В результате​ поэтому мы называли​ ссылка на ячейку​ внесены в одну​
​ задачи не требует​ разбита на две,​
​ значение ячейки на​
​ у вас один.​ такой же.​ теми, которые предполагаются​Грубо говоря, специальных инструментов​ в статье «Как​
​ кодом в столбце​ отображаться разделенные данные.​ с текстом, который​: Излишнее цитирование удалено​китин​ чего получаем правильное​
​ такую формулу –​ с исходным текстом.​ ячейку. Необходимо из​ много знаний в​ и вы сможете​ две невозможно, придется​ Именно в нем​
​Это был первый способ​ быть раздельными.​ для разделения ячеек​ сделать выбор в​ В.​Нажмите кнопку​ вы хотите разделить.​ администрацией​:​ количество символов в​ гибкой, в начале​Второй аргумент – это​

Четыре способа разделения ячейки на две в Excel

​ этой ячейки отделить​ работе с табличным​ ввести в нее​ информацию вписывать после,​ вы можете разделить​ разделения ячейки на​Перейдите на вкладку «Главная».​ на две в​ ячейке Excel» тут.​Копируем формулу по​Готово​На вкладке​Вот так спасибо,​Antonzx55​ разделенной строке. С​ статьи. Она подходит​

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

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

Способ 1: через объединение ячеек

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

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

​Текст по столбцам​ моего времени, благодарю​ посоветовать текст по​ выборку разной длинны​ рода задач. Гибкость​Последний аргумент – это​ ниже на рисунке:​ как это делать.​ помощью инструмента «Фигуры».​ действий предпринимать не​Кликните по той ячейке,​ он подойдет как​ центр». Расположена она​

1. ​ будет предложено четыре​ оно считалось, смотрите​ способами установить формат,​
2. ​ ячейках​.​
3. ​nick_dnu​ столбцам на вкладке​

​ разделенного текста из​ придает ей сложная​ количество символов, которое​Сначала определим закономерность, по​Автор: Вадим Матузок​ Однако стоит учитывать,​ нужно. А выполняется​ которую желаете разделить​ никогда кстати, но​ в категории «Выравнивание».​ способа, которые можно​

​ в статье «Не​ смотрите в статье​Excel по столбцам формулой​В​: Есть электронные адреса​ Данные​ разных длинны исходных​ комбинация из функций​ должна содержать разделенная​ которой можно определить,​Часто приходится оптимизировать структуру​

Способ 2: разделение объединенных ячеек

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

1. ​Antonzx55​
2. ​ строк.​ НАЙТИ. Пользователю формулы​ строка.​ что данные из​ данных после импорта​ вариант только в​
3. ​ форматирования:​ чтобы она находилась​ лучше воспользоваться другими​ заметите, что все​
4. ​ категории: те, что​ Excel».​ здесь.​

​ специальная функция –​ столбцам​ эксель. Как эти​: Спасибо, прикрепил файл​Antonzx55​ достаточно определить закономерность​С первым аргументом ПСТР​

1. ​ разных категорий, несмотря​ в Excel. Некоторые​ том случае, если​Откройте книгу и перейдите​ в столбце, который​ способами. Поэтому незамедлительно​
2. ​ два столбца ячеек,​
3. ​ подразумевают разделение ячейки​В Excel есть​В ячейке С27​ «Текст по столбцам».​
4. ​установите переключатель​ адреса разнести по​китин​

​: Доброго времени суток!​ и указать их​ все понятно –​ на то, что​ разные значения попадают​ ваша ячейка образована​ на лист, в​ получился после объединения.​ переходим к ним.​ находящиеся под объединенными,​ по диагонали, и​ несколько разных способов​ пишем такую формулу.​ Где она находится​с разделителями​ ячейкам, чтобы в​

Способ 3: разделение по диагонали с помощью форматирования

​: Ну как я​Есть интересная задача.​ в параметрах функций:​ это ссылка на​ они находятся в​ в одну и​ путем объединения нескольких.​ котором хотите поделить​Перейдите на вкладку «Главная».​А сейчас будет рассказано,​ будут казаться разделенными,​ по вертикали или​

1. ​ сгрупприровать данные в​ =ДАТАЗНАЧ(ПСТР(A27;НАЙТИ(«/»;A27;1)+1;255))​ и как с​и нажмите кнопку​
2. ​ каждой ячейке был​
3. ​ и писал:​Есть столбик с​
4. ​ будут это квадратные​ ячейку A3. Второй​
5. ​ одной и той​ туже ячейку образуя​Выделите нужную ячейку.​
6. ​ ячейку.​В группе инструментов «Выравнивание»​ как разделить ячейку​ хотя над ними​ горизонтали. Долго не​ таблице по нашим​Пояснение в формуле.​ ней работать, смотрите​Далее​ только один эл.​
7. ​Выделяете столбец А-вкладка​ данными, в каждой​ скобки либо другие​ аргумент мы вычисляем​
8. ​ же строке. В​ целую строку как​

​На вкладку «Вставка» нажмите​Выделите ее.​ нажмите по кнопке​ в Excel на​ мы не производили​

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

​ задерживаясь на нюансах,​ условиям, например, по​В формуле написали​ в статье «Разделить​.​ адрес?​ Данные-текст по столбцам-с​ ячейке несколько ссылок​ разделительные знаки. Например,​

1. ​ с помощью функции​
2. ​ нашем случае нас​ одно значение. Возникает​
3. ​ по кнопке «Фигуры».​Нажмите затем правую кнопку​ «Объединить и поместить​
4. ​ две по-настоящему. Конечно,​ никаких действий. Чтобы​ перейдем непосредственно к​ датам, по определенному​

​ «+1» — это​ текст Excel на​Выберите​

Вместо заключения

​Vlanib​ разделителями-далее- в квадратике​ с разделителем «|».​ это могут быть​ НАЙТИ(«]»;A3)+2. Она возвращает​ интересуют только числа,​ вопрос: как разбить​Из выпадающего списка выберите​ мыши.​ в центр».​ для этого необходимо​ увидеть это более​ методам того, как​ периоду, шагу цен,​ значит, чтобы в​ несколько ячеек» тут.​разделители​: А чем разделены?​ другой ставите знак​Так вот нужно​

​ пробелы если нужно​

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

​ очередной номер символа​ которые находятся вне​ строку на ячейки​ из категории «Линии»​В контекстном меню выберите​В появившемся выпадающем меню​ будет изначально прибегнуть​ наглядно, рекомендуется выделить​ разделить ячейку на​ сумм, т. д.​ ячейке с датой​Здесь рассмотрим,​для своих данных.​nick_dnu​ | — Далее-готово.​ первую ссылку в​ разделить строку на​ первой закрывающейся квадратной​ квадратных скобок. Каким​ в Excel. Программа​ одноименный объект.​ опцию «Формат ячеек».​ выберите опцию «Отменить​ к некоторым действиям,​

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

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

​Появится окно, в котором​ объединение ячеек».​ а если быть​ этого выполните следующие​Как говорилось ранее, чтобы​ смотрите в статье​ слеш (косая черточка).​Excel формулой текст числа,​запятую​ запятой эти адреса.​Antonzx55​ в отдельный столбик,​В данном примере функция​ И к этому​ выбрать из строк​ функциями: одни ищут​ проведите линию от​ необходимо перейти на​Теперь вместо одной ячейки​ точнее, то объединить​

​ действия:​ разделить ячейку на​

​ «Как группировать в​«255» — это​

​ коды на составляющие​и​

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

​: Этот способ разделяет​ а оставшиеся ссылки​ НАЙТИ во втором​ номеру мы добавляем​

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

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

​ отдельные ячейки? Эффективным​ ячеек. Ведь выполнять​После этого ячейка будет​ с текстом «Надпись».​ думаете, как разделить​Запустите программу.​ таблицу.​ Это так, потому​ процессоре Excel у​ ячейке. По факту,​ таблица с данными.​ просмотреть в окне​ в один столбец​ что бы было​ два столбика: первый​ скобки. А в​ нужен номер символа​ решением является гибкая​ поиск по текстовой​ разбита на две​ В нижнем правом​ данные ячейки в​Зажав левую кнопку мыши​На панели инструментов нажмите​ что ячейка –​ пользователя возникают различные​

​ отображается столько символов,​В столбце А написан​Образец разбора данных​ все поставить. Вертикально.​ 2 столбика, в​ столбик с одной​ третьем аргументе эта​ после пробела за​ формула основана на​ строке, которая содержится​ по диагонали.​ или левом углу​ Excel на две,​ на наименованиях столбцов​ по кнопке «Границы».​ первичный структурный элемент,​ задачи, однако даже​ сколько есть в​ код, составленный через​.​Vlanib​ одном первая ссылка​ ссылкой, второй столбик​ же функция вычисляет​ квадратной скобкой. В​ текстовых функциях.​ в ячейке ¬–​Вот мы и разобрались,​ находятся кнопки, после​

Как разделить ячейку с данными на две ячейки (Формулы/Formulas)

​ то, к сожалению,​​ (латинские буквы, находящиеся​
​Из выпадающего меню выберите​
​ и она не​ простое на первый​ дате (после слеша).​ слеш из кода​
​Нажмите кнопку​: выделить все столбцы,​ а во втором​ с несколькими ссылками​ положение нужного нам​ последнем аргументе функция​
​В ячейку B3 введите​ это также распространенная​ что нужно делать​ нажатия которых ячейка​ каких-то специальных инструментов​ над первой строкой​ пункт «Все границы».​
​ может быть разбита​ взгляд действие в​
​Копируем формулу по​
​ и года. Нам​Далее​ cntr C, выделить​ все остальные ссылки​

​ и разделителем «,»​​ текста в строке​ вычисляет какое количество​

​ следующую формулу:​​ потребность пользователей Excel.​​ для разделения ячеек​​ разбивается линией по​ для этого не​ таблицы), выделите два​Кстати, если вы хотите​ надвое. Из-за этого​

​ нем выполнить не​​ столбцу. Получится так.​

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

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

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

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

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

​Формат данных столбца​​ специальная вставка -​: В ячейку В1​ посоветовать?​ в третьем аргументе​ разделения, учитывая положение​Выборка чисел из строк​ разделения строк.​ ячейку со значениями​

​ соответственно.​​ «Копировать»/«Вставить». А лучше​

​ приложения, нажмите по​​ по горизонтали, тогда​ таблице таким образом,​ испытывают проблемы при​ не слешем, а​ =ЗНАЧЕН(ЛЕВСИМВ(A27;НАЙТИ(«/»;A27;1)-1))​или используйте то,​ транспонировать.​

​=ЛЕВСИМВ(A1;ПОИСК(«|»;A1&»|»)-1)​​Прикрепил исходный файл.​ более сложное и​ квадратной скобки.​ в отдельные ячейки.​Допустим на лист Excel​ на две в​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 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 знаков, то есть до конца строки, что нам и требуется.

7 примеров использования функции «Текст по столбцам» в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как разделить содержимое одной ячейки на несколько?

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

1. Выделите одну или несколько ячеек, которые хотите разделить. …
2. На вкладке данные в группе Работа с данными нажмите кнопку текст по столбцам. …
3. Установите переключатель С разделителями, если выбран другой вариант, и нажмите кнопку Далее.

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

Ввод разрыва строки

1. Дважды щелкните ячейку, в которую требуется ввести разрыв строки. Совет: Можно также выделить ячейку, а затем нажать клавишу F2.
2. Дважды щелкните в ячейке то место, куда нужно вставить разрыв строки, и нажмите сочетание клавиш ALT+ВВОД.

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

1. В таблице щелкните ячейку, которую вы хотите разделить.
2. Перейдите на вкладку макет.
3. В группе Слияние щелкните разбить ячейки.
4. В диалоговом окне разделенные ячейки выберите нужное количество столбцов и строк и нажмите кнопку ОК.

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

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

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

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

Как разбить одну ячейку на две в ворде?

1. Выделите одну или несколько ячеек, которые требуется разделить.
2. В группе Работа с таблицами на вкладке Макет в группе Слияние выберите команду Разделить ячейки.
3. Введите число столбцов и строк, на которое следует разбить выделенные ячейки.

Как изменить размер только одной ячейки?

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

Как найти все объединенные ячейки в Excel?

Поиск объединенных ячеек

1. На вкладке Главная выберите Найти и выделить > Найти.
2. Нажмите кнопку Параметры _гт_ Формат.
3. Нажмите кнопку Выравнивание _гт_ объединить ячейки _гт_ ОК.
4. нажмите кнопку Найти все, чтобы просмотреть список всех объединенных ячеек на данном листе. Если щелкнуть элемент в списке, Excel выделит объединенную ячейку на листе.

Как объединить ячейки в таблице Excel Горячие клавиши?

Для объединения и центрирования ячеек в моей версии Excel необходимо последовательно нажать Alt – Я – Щ – Б. Это был первый способ. Второй способ. Любым способом объединить ячейки и повторно вызвать команду клавишей F4 (повтор последнего действия).

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

Создание раскрывающегося списка в Excel

1. Выберите ячейки, в которой должен отображаться список.
2. На ленте на вкладке «Данные» щелкните «Проверка данных».
3. На вкладке «Параметры» в поле «Тип данных» выберите пункт «Список».
4. Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.

Как вставить текст в Excel несколько строк?

При необходимости ввода в одну ячейку двух или более строк текста, начните ввод текста и в конце строки нажмите сочетание клавиш [Alt] + [Enter]. После этого в этой же ячейке можно начинать вторую строку текста.