Разбить данные столбца по столбцам в excel - Word и Excel

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

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

Проверьте, как это работает!

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

См. также

Разделение текста по столбцам с помощью функций

Нужна дополнительная помощь?

Источник

Вариант 1: Разделение столбцов с числами

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

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

Выделите столбец, который требуется разделить, а затем перейдите на вкладку «Данные».

Щелкните по кнопке «Текст по столбцам». Да, хоть инструмент и имеет отношение к тексту, ничего не помешает использовать его для денежных сумм, дат или других чисел.

Отобразится окно «Мастера распределения текста по столбцам», в котором выберите вариант «с разделителями» и переходите к следующему шагу.

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

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

Формат данных оставьте общим или установите для него дату, если речь идет о ней.

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

Вручную напишите область или отметьте ее на таблице.

Как только действия распределения завершены, нажмите «Готово» для применения новых настроек.

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

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

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

Вариант 2: Разделение текста

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

Подробнее: Разделение текста в Microsoft Excel

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

На чтение 7 мин Опубликовано 18.01.2021

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

Содержание

Необходимо разделить ФИО по отдельным столбцам
Разделение текста с помощью формулы
Этап №1. Переносим фамилии
Этап №2. Переносим имена
Этап №3. Ставим Отчество
Заключение

Необходимо разделить ФИО по отдельным столбцам

Для выполнения первого примера возьмем таблицу с прописанными в ней ФИО разных людей. Делается это с использованием инструмента «Текст по столбцам». После составления одного из документов была обнаружена ошибка: фамилии имена и отчества прописаны в одном столбце, что создает некоторые неудобства при дальнейшем заполнении документов. Для получения качественного результата, необходимо выполнить разделение ФИО по отдельным столбцам. Как это сделать – рассмотрим далее. Описание действий:

Открываем документ с допущенной ранее ошибкой.
Выделяем текст, зажав ЛКМ и растянув выделение до крайней нижней ячейки.

В верхней ленте находим «Данные» — переходим.

После открытия отыскиваем в группе «Работа с данными» «Текст по столбцам». Кликаем ЛКМ и переходим в следующее диалоговое окно.

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

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

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

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

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

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

Этап №1. Переносим фамилии

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

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

Выберите ячейку, где будет записываться информация о фамилии сотрудника. Активируйте ее нажатием ЛКМ.

Нажмите на кнопку «Аргументы и функции», активация которой способствует открытию окна для редактирования формулы.
Здесь в рубрике «Категория» нужно пролистать вниз и выбрать «Текстовые».

Далее находим продолжение формулы ЛЕВСИМВ и кликаем по этой строке. Соглашаемся с выполненными действиями нажатием кнопки «ОК».
Появляется новое окно, где нужно указать адресацию ячейки, нуждающейся в корректировке. Для этого нажмите на графу «Текст» и активируйте необходимую ячейку. Адресация вносится автоматически.
Чтобы указать необходимое количество знаков, можно посчитать их вручную и вписать данные в соответствующую графу либо воспользоваться еще одной формулой: ПОИСК().
После этого формула отобразится в тексте ячейки. Кликните по ней, чтобы открыть следующее окно.

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

Соглашаемся и кликаем по кнопке «ОК».

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

Этап №2. Переносим имена

Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.

В качестве основной формулы прописываем аналогичным предыдущему способу образом =ПСТР(.

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

В строке «Искомый текст» указываем пробел.
Кликнув по «Текст для поиска», активируем ячейку.

Возвращаемся к формуле =ПСТР в верхней части экрана.
В строке «Нач.позиция» приписываем к формуле +1. Это будет способствовать началу счета со следующего символа от пробела.

Переходим к определению количества знаков – вписываем формулу ПОИСК().
Перейдите по данной формуле вверху и заполните все данные уже понятным вам образом.
Теперь в строке «Нач.позиция» можно прописать формулу для поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, не указывая ничего в «Нач.позиция».
Переходим к предыдущей формуле ПОИСК и в «Нач.позиция» дописываем +1.
Возвращаемся к формуле =ПСТР и в строке «Количество знаков» дописываем выражение ПОИСК(« »;A2)-1.

Этап №3. Ставим Отчество

Активировав ячейку и перейдя в аргументы функции, выбираем формулу ПРАВСИМВ. Жмем «ОК».

В поле «Текст» вписываем адресацию редактируемой ячейки.
Там, где необходимо указать число знаков, пишем ДЛСТР(A2).

Примечание эксперта! Формула определит автоматически количество символов.

Для точного определения количества знаков в конце необходимо написать: -ПОИСК().
Перейдите к редактированию формулы. В «Искомый текст» укажите пробел. В «Текст для поиска» — адресацию ячейки. В «Нач.позиция» вставьте формулу ПОИСК(). Редактируйте формулу, установив те же самые значения.
Перейдите к предыдущему ПОИСК и строке «Нач.позиция» допишите +1.

Перейдите к формуле ПРАВСИМВ и убедитесь, что все действия произведены правильно.

Заключение

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

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

Источник

Имеем в одной ячейке «Иванов Иван Иванович», хотим получить в трех ячейках «Иванов» «Иван» «Иванович». Как это сделать быстро? Ответ ниже.

Итак, имеем ФИО сотрудника в одном столбце, хотим разбить на 3 столбца:

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

В открывшемся окне мастера переключаем тумблер в положение «с разделителями» и нажимаем кнопку «Далее >»:

В следующем окне мастера снимаем галочку с пункта «знак табуляции» и устанавливаем галочку напротив пункта «пробел». Нажимаем кнопку «Далее >»:

В следующем окне мастера нажимаем кнопку «Готово»:

Получаем результат:

Добавить комментарий

Источник

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

Смотрите также улица была написана выхода из режима. другому. на две. Важно, таблице таким образом, листе. не более одного формулы Вы можете=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(« начать поиск. В результатом, просто нажмите чтобы разбить данные идентификатор товара –Осталось сделать всего две первом шаге мастера(Фамилия),В этой статье Вы в одной строке.

После этого ячейка будет чтобы она находилась чтобы визуально нужныеНа вкладке столбца. Это важные использовать, чтобы разбить «,A2,1)+1)) нашем примереEnter из одного столбца это 9 символов, вещи – выбрать Вы выбираете форматCountry найдёте несколько способов,Выделяем ячейку. На вкладкеРазбить на две ячейки разбита на две в столбце, который оказались разделенными.

данные из них достаточно текстовые данные, разделённые
=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«SEARCH(» «,A2,1)
, и весь столбец на два или которые стоят перед
формат данных и данных. Так как(Страна),

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

как разбить ячейки «Выравнивание» нажимаем кнопку можно только объединенную по диагонали. получился после объединения.Четко представьте, как таблицав группе Работа пустыми столбцами справа запятыми:

«;A2;1)+1))или будет заполнен именами. более столбцов. наименованием этого товара: указать, куда поместить записи разделены пробеламиArrival Date или целые столбцы

«Перенос текста». ячейку. А самостоятельную,
Вот мы и разобрались,Перейдите на вкладку «Главная».

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

должна будет выглядеть с от выбранных столбец,AИзвлекаем фамилию:ПОИСК(» «;A2;1) Очень умный инструмент,Если Вы уже обновилисьВот что Вам нужно разделённые ячейки.В разделе

и запятыми, мы(Ожидаемая дата прибытия) в Excel 2010Данные в ячейке автоматически которая не была что нужно делатьВ группе инструментов «Выравнивание» в итоге.данными который будет предотвратитьB=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))говорит о том, не правда ли? до Excel 2013, сделать, чтобы разбить

Column data format выбираем формат и и 2013. Приведённые распределятся по нескольким объединена – нельзя. для разделения ячеек нажмите по кнопкеВыделите две ячейки наднажмите кнопку перезапись по данные,C=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)) что мы хотимЕсли «Мгновенное заполнение» включено, то можете воспользоваться такой столбец на(Формат данных столбца)DelimitedStatus примеры и скриншоты строкам. НО как получить в Excel. Стоит «Объединить и поместить теми, которые предполагаютсятекст по столбцам которые распространяться данныеDФункция найти символ пробела но не предлагает преимуществами нового инструмента два: Вы можете выбрать(С разделителями). Вариант(Статус). иллюстрируют работу сПробуйте, экспериментируйте. Устанавливайте наиболее такую таблицу: заметить, что разделить в центр». быть раздельными.. Откроется мастер пересчета в любой смежных1MID

в ячейке никаких вариантов, которые «Запустите инструмент формат данных отдельноFixed width

Если в таблице есть инструментами «Текст по удобные для своихДавайте посмотрим на нее ячейку со значениямиВ появившемся выпадающем менюПерейдите на вкладку «Главная».текста по столбцам столбцах. При необходимостиПолное обозначение(ПСТР) – извлекаетA2 соответствуют определённому шаблону,Мгновенное заполнениеText to Columns

для каждого столбца,(Фиксированной ширины) будет хотя бы один столбцам» и «Мгновенное читателей форматы. внимательнее, на листе на две в выберите опцию «ОтменитьНа панели инструментов нажмите.
вставьте количество пустыеШтат часть текстовой строкии начнём поиск Вы можете запустить» и заставить Excel(Текст по столбцам), в которые будут рассмотрен чуть позже. столбец справа от заполнение», кроме этогоamiraraya Excel. Excell нельзя с объединение ячеек». по кнопке «ОбъединитьУстановите переключатель столбцы, будет достаточноАббревиатура (то есть заданное
с первого символа. этот инструмент вручную автоматически заполнять (в как мы это
-  помещены разделённые данные. Если все готово, столбца, который необходимо Вы увидите подборку: здравствуйте, у меняЧерта разделяет не одну помощью специального инструмента,Теперь вместо одной ячейки и поместить вС разделителями для хранения каждогоСтолица количество символов). Синтаксис:Замечание: на вкладке нашем случае – делали в предыдущем По умолчанию для жмите разбить, тогда первым формул для разделения проблема следующего порядка. ячейку, а показывает делается это уже у вас две. центр». Расположена она, если выбран другой составные части распределенных2
- =MID(text,start_num,num_chars)Если поиск начинаетсяData разбивать) данные, при примере. На первом всех столбцов заданNext делом создайте новые имён, текстовых иу меня в границы двух ячеек. после простым перемещением Кстати, если вы в категории «Выравнивание». вариант, и нажмите данных.Alabama, AL, Montgomery=ПСТР(текст;начальная_позиция;количество_знаков) с первого символа,(Данные) > обнаружении определенной закономерности. шаге мастера выберите формат(Далее), чтобы продолжить. пустые столбцы, в числовых значений. Этот одной ячейке А1 Ячейки выше «разделенной» данных. В остальном думаете, как разделитьСразу после этого вы
кнопкуВыделите ячейку, диапазон илиAlabamaВ качестве аргументов функции Вы можете вообщеFlash FillЕсли Вы ещё не параметрGeneralНа следующем шаге определяем которые будут помещены урок поможет Вам
«0,33793170937306005;10,226503051245885″ и ниже объединены же выполнение поставленной данные ячейки в заметите, что всеДалее весь столбец, гдеAL указываем: какой текст пропустить аргумент(Мгновенное заполнение) или знакомы с этойFixed width(Общий). Мы оставим разделители, которые содержатся полученные данные. Этот выбрать наилучший методмне нужно сделать по строкам. Первый задачи не требует Excel на две, два столбца ячеек,. содержатся текстовые значения,Montgomery взять, позицию символа,start_num нажав сочетание клавиш функцией, я попробую(Фиксированной ширины) и его без изменений в данных, и шаг необходим для разбиения данных в две ячейки с столбец, третий и много знаний в то, к сожалению,

находящиеся под объединенными,Выберите один или несколько которые требуется разделить.Извлекаем название штата: с которого нужно(нач_позиция) в формулеCtrl+E кратко объяснить её нажмите для первых трёх ограничитель строк. того, чтобы результаты Excel. значениями до ; четвертый в этой работе с табличным каких-то специальных инструментов будут казаться разделенными, разделителей, чтобы задатьНа вкладке=LEFT(A2,SEARCH(«,»,A2)-1) начать, и сколько и упростить её. суть. Этот инструментNext

 столбцов, а дляНастраиваем разделители не были записаныГоворя в общем, необходимость и вторая после таблице состоят из редактором, тем более для этого не хотя над ними места, в которыхДанные=ЛЕВСИМВ(A2;ПОИСК(«,»;A2)-1) символов извлечь. до такого вида:

Существуют формулы, которые могут анализирует данные, которые(Далее).

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

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

предусмотрено. Вам необходимо мы не производили произойдет разделение ячейки.в группеИзвлекаем аббревиатуру штата:Вы можете использовать аналогичные=LEFT(A2,SEARCH(» «,A2)-1)

быть очень полезны, Вы вводите наВ разделе формат

одним или несколькими данных.В нашем примере Excel может возникнутьВ1 «0,33793170937306005» и столбец – из есть пошаговая инструкция, будет пользоваться обычным никаких действий. Чтобы В областиРабота с данными=MID(A2,SEARCH(«,»,A2)+2,SEARCH(«,»,A2,SEARCH(«,»,A2)+2)-SEARCH(«,»,A2)-2) формулы, чтобы разбить=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1)
когда возникает необходимость рабочий лист, иData previewData разделителями, то нужно сразу после столбца в двух случаях: С1 «10,226503051245885» двух. как это делать. «Копировать»/«Вставить». А лучше увидеть это болееОбразец разбора данныхнажмите кнопку=ПСТР(A2;ПОИСК(«,»;A2)+2;ПОИСК(«,»;A2;ПОИСК(«,»;A2)+2)-ПОИСК(«,»;A2)-2) имена с суффиксамиLEFT разбить ячейки или пытается выяснить, откуда(Образец разбора данных)(Дата), что логично, выбрать все подходящиеParticipant Во-первых, при импортетак как столбикТаким образом, чтобы разбитьАвтор: Вадим Матузок сначала построить структуру наглядно, рекомендуется выделитьможно посмотреть на
Текст по столбцамИзвлекаем столицу штата: в конце:(ЛЕВСИМВ) и столбцы с данными они взялись и настройте ширину столбцов. ведь в этот варианты в разделе

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

находится столбец информации из какой-либо из 25т. значений нужную ячейку наФорматирование и редактирование ячеек таблицы и уже границы ячеек. Для предполагаемые результаты разделения..=RIGHT(A2,LEN(A2)-(SEARCH(«,»,A2,SEARCH(«,»,A2)+1)+1))ARIGHT в Excel. На существует ли в Как видно на столбец попадут датыDStatus внешней базы данных делать это руками

две части, необходимо в Excel – потом вписывать в этого выполните следующие Нажмите кнопкуСледуйте инструкциям в=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«,»;A2;ПОИСК(«,»;A2)+1)+1))B(ПРАВСИМВ) – возвращает самом деле, следующих них какая-либо закономерность. рисунке ниже, край прибытия.Чтобы изменить форматelimiters, и мы собираемся

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

или с веб-страницы. будет не целесообразно, объединить соседние ячейки. удобный инструмент для нее значения. действия:ДалееСтолбцах мастераА вот пример реальныхC левую или правую

шести функций будет Как только «Мгновенное столбца символизирует вертикальная данных для каждого(Символом-разделителем является) или добавить между ними При таком импорте как сделать макрос, В нашем примере наглядного представления информации.Мы уже установили, чтоВыделите диапазон ячеек, которые., чтобы указать способ данных из ExcelD часть текста из достаточно в большинстве заполнение» распознает Ваши линия, и чтобы

конкретного столбца, выделите ввести свой вариант новые столбцы все записи копируются который бы сделал – сверху и Такие возможности программы в Excel разделить входят в вашуВ области разбиения текста на 2010. Данные из

1 заданной ячейки соответственно. случаев – действия и вычислит задать край следующего его, кликнув по разделителя в полеLast Name в один столбец, бы эту всю снизу. Ту ячейку, для работы бесценны. значение ячейки на таблицу.

Формат данных столбца разных столбцах. первого столбца разбитыПолное имя Синтаксис формулы:LEFT закономерность, Excel предложит столбца, просто кликните нему в областиOther, а нужно, чтобы работу автоматически? которую нужно разделить,Значимость оптимальной демонстрации данных две невозможно, придетсяНа панели инструментов нажмитевыберите формат данныхПримечание: на три отдельных

Имя=LEFT(text,[num_chars])(ЛЕВСИМВ), вариант, и последовательность в нужном месте.Data preview(Другой).В нашем примереCountry они были помещеныза ранее благодарен не объединяем. объяснять никому не информацию вписывать после, по кнопке «Границы». для новых столбцов. Для получения справки по

столбца:Фамилия=ЛЕВСИМВ(текст;[количество_знаков])MID записей в новом Двойной щелчок по(Образец разбора данных), мы выбираеми в разных столбцах.amirarayaДля решения данной задачи нужно. Давайте посмотрим, однако если делить

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

Из выпадающего меню выберите По умолчанию столбцы заполнению все действияУрок подготовлен для ВасСуффиксВ качестве аргументов указываем:(ПСТР), столбце появится буквально вертикальной линии удалит а затем установитеSpaceArrival Date Во-вторых, при разбиении: Данные — Текст следует выполнить следующий что можно сделать ее по диагонали, пункт «Все границы». имеют тот же мастера читайте статью командой сайта office-guru.ru2 какой текст взятьRIGHT за мгновение. край столбца, а желаемый формат в(Пробел) и.Если кто-то забыл, я

Пример 1

уже существующей таблицы, по столбцам - порядок действий: с ячейками в то никаких предварительныхКстати, если вы хотите формат данных, что Разделение текста поИсточник: https://www.ablebits.com/office-addins-blog/2014/02/27/split-cells-excel/

Robert Furlan Jr. и сколько символов(ПРАВСИМВ),Таким образом, при помощи

если Вам нужно разделе
Comma
напомню быстрый способ
чтобы получить возможность разделитель точка с
Щелкаем правой кнопкой по
Microsoft Excel. Из

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

Перевел: Антон АндроновRobert извлечь. В следующемFIND этого инструмента Вы переместить границу столбцаColumn data format(Запятая), а также вставить сразу несколько

качественнее настроить работу
запятой

ячейке и выбираем данного урока вы нужно. А выполняется две в Excel Нажмите кнопку преобразовать мастера текстов,Автор: Антон АндроновFurlan примере формула будет(НАЙТИ), можете взять какую-то в другое место,(Формат данных столбца). ставим галочку напротив столбцов на лист фильтра, сортировку илиamiraraya инструмент «Формат» (или

 узнаете о новых все это путем по горизонтали, тогдаГотово или нажмите кнопкуПримечание:Jr. извлекать левую частьSEARCH

часть данных, находящихся
просто перетащите вертикальную

На этом же шаге параметра Excel. Для этого для более детального: а подробнее? комбинация горячих клавиш возможностях заполнения и форматирования:

вам всего-навсего объединить
.

СправкаМы стараемся какИзвлекаем имя: текста из ячейки(ПОИСК) и в одном или линию мышью. На мастера Вы можетеTreat consecutive delimiters as выберите столбец

анализа.
amiraraya

 CTRL+1). форматирования данных вОткройте книгу и перейдите две ячейки, находящиесяОбъединение и отмена объединенияв мастере разделения

можно оперативнее обеспечивать
=LEFT(A2,FIND(" ",A2,1)-1)

A2LEN нескольких столбцах, и самом деле, все

выбрать, в какой
one

StatusРазбиваем ячейки при помощи: Выделяете столбец АНа закладке «Граница» выбираем рабочих листах. на лист, в от нее справа ячеек

Пример 2

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

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

первого найденного пробела.
этом разделе я
новый столбец. Думаю,
расписаны в верхней
данные. Для этого одним). Этот параметр
заголовку, и, удерживая столбцам»
Данные — группа
тип линии, толщину,
по горизонтали или

ячейку. этом случае инструкция или данных Мы стараемся как можно языке. Эта страница=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(«

=LEFT(A2,SEARCH(" ",A2)-1)
кратко объясню назначение

Вы лучше поймёте части диалогового окна кликните по иконке поможет избежать лишнего нажатой левую кнопкуКак разбить объединённые ячейки

Пример 3

Работа с данными цвет. по вертикали. ВВыделите ее.

	по выполнению будет	При работе в табличном	оперативнее обеспечивать вас	переведена автоматически, поэтому
«,A2,1)+1))	=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1)	каждой из этих	о чём я	Так как каждый ID
выбора диапазона (в	разбиения данных, например,	мыши, протащите указатель	в Excel	— там есть

Жмем ОК.
результате получается одна
Нажмите затем правую кнопку
такой же.
процессоре Excel у актуальными справочными материалами
ее текст может=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ("
LEN
функций и приведу
говорю из следующего

Пример 4

товара содержит 9 терминах Microsoft эта когда между словами вправо, чтобы выделитьРазделяем данные в Excel Текст по столбцамЕсли нужно провести диагональ

	ячейка, занимающая сразу	мыши.	Это был первый способ	пользователя возникают различные
на вашем языке.	содержать неточности и	«;A2;1)+1))	(ДЛСТР) – считает	примеры, которые Вы
примера.	символов, устанавливаем линию	иконка называется	есть 2 или	нужное количество столбцов

2013 при помощи
- открывается диалоговое в большой ячейке,
пару столбцов либоВ контекстном меню выберите
разделения ячейки на
задачи, однако даже
Эта страница переведена
грамматические ошибки. Для
Извлекаем суффикс:
длину строки, то

Пример 5

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

	автоматически, поэтому ее	нас важно, чтобы	=RIGHT(A2,LEN(A2)-FIND(» «,A2,FIND(» «,A2,1)+1))	есть количество символов
своих книгах Excel.	инструмент «Мгновенное заполнение»	это значение, как	) справа от поля	Настраиваем ограничитель строк
Затем кликните правой	Формулы для разбиения столбцов	С разделителями -	На вкладке «Иллюстрации» выбираем	в центре объединенной

Появится окно, в котором
необходимо сделать, например,
взгляд действие в
текст может содержать
эта статья была
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))
в заданной ячейке.
Самая распространённая ситуация, когда
включен. Вы найдёте

показано на рисункеDestination. Этот параметр может кнопкой мыши по (имен и других Далее — ставите

«Фигуры». Раздел «Линии». ячейки.
необходимо перейти на
шапку таблицы, то

нем выполнить не

office-guru.ru

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

 неточности и грамматические вам полезна. ПросимА вот формулы, позволяющие Синтаксис формулы: могут понадобится эти этот параметр на выше.(Поместить в) и понадобиться, если в выделенной области и текстовых данных) галочку на «точкаПроводим диагональ в нужномПорядок объединения ячеек в вкладку «Граница». он подойдет как так просто, как ошибки. Для нас вас уделить пару разбить имена с=LEN(text) формулы – это

вкладкеНа следующем шаге выберите выберите крайний левый столбце, который Вы в контекстном менюИнструмент « с запятой» - направлении. Excel:Здесь располагается интерфейс ячейки никогда кстати, но кажется. Так, многие важно, чтобы эта

 секунд и сообщить, фамилией, стоящей впереди

=ДЛСТР(текст) необходимость разделить именаFile формат данных и столбец из тех, разбиваете, содержатся какие-либо
выберите командуТекст по столбцамVlad999

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

Возьмем небольшую табличку, где с текстом «Надпись». для других целей испытывают проблемы при статья была вам помогла ли она и отделенной отСледующая формула считает количество из одного столбца(Файл) > укажите ячейки, куда в которые Вы значения, заключённые вInsert» действительно очень удобен,: сделал, белеберда получилась размер можно следующим несколько строк и В нижнем правом лучше воспользоваться другими попытке разделить ячейку

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

 в Excel на уделить пару секунд кнопок внизу страницы. отчеством, находящимся вA2 рисунке ниже показано,(Параметры) > это было сделано данные. К сожалению, апострофы, и ВыРезультат будет примерно таким,

support.office.com

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

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

 нажатия которых ячейка

А сейчас будет рассказано, статье будет рассказано ли она вам, приводим ссылку наA=LEN(A2) пытаемся достичь:(Дополнительно) > а затем нажмите данные на другой участки текста не на рисунке ниже в Excel 2013, а первая ничего Щелкаем правой кнопкой главной странице программы. разбивается линией по

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

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

как раз об с помощью кнопок оригинал (на английском

B=ДЛСТР(A2)Вы легко сможете разбитьAutomatically Flash FillFinish лист или в разбивались, а рассматривались
(новые столбцы вставлены 2010, 2007 или хорошего.. мыши по любойВыделяем ячейки, которые нужно диагонали справа налево в Excel на этом. внизу страницы. Для языке) .
CЕсли имена в Вашей такие имена на(Автоматически выполнять мгновенное(Готово). другую рабочую книгу, как цельные значения.
слева от выделенных 2003.amiraraya латинской букве вверху объединить. Нажимаем «Объединить или слева направо две по-настоящему. Конечно,Грубо говоря, специальных инструментов удобства также приводимМожно разделить содержимого ячейкиD
таблице содержат отчества два столбца при заполнение).Если Вы объединили несколько попытка сделать это Например, если Вы столбцов):«Текст по столбцам» позволяет: Выложите файл, оставьте столбцов. и поместить в

См. также

соответственно. для этого необходимо

для разделения ячеек ссылку на оригинал

support.office.com

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

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

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

(на английском языке). части нескольких смежныхПолное имя потребуются немного болееИзвлекаем имя (столбец First можно автоматически разбить Excel и теперь об ошибке выбора разделителя запятую, аЕсли у Вас отделённые разделителями, илиДа вроде получилось,Вводим тот показатель ширины,При объединении сохраняются только также выбрать тип к некоторым действиям, программе нет, поэтомуПоследнее обновление: 12.12.2015 ячеек. Например, если

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

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

Вам может потребоваться разделить лист содержитОтчество
использованием функции=LEFT(A2,SEARCH(» «,A2,1)-1) Итак, Вы включили
их по отдельным
Совет: строк – кавычки непосредственно за тем, ширины (когда все он первую строчку

Жмем ОК. содержатся в верхней цвет. точнее, то объединить Дальше по тексту определенную ячейку наПолное имяФамилияMID=ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)-1) инструмент «Мгновенное заполнение», столбцам, откройте вкладкуЕсли Вы не

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

два смежных столбца. будет предложено четыре две небольшие, расположенныестолбца, можно разделить столбец2(ПСТР).Извлекаем фамилию (столбец Last и начинаете вводитьHome хотите импортировать какой-то слова, заключённые в

разбить, то необходимость количество символов). Давайте информации. во всем листе. нужно сохранить все нажмите ОК. Для этого: способа, которые можно в одном столбце. на двух столбцовWhite, David Mark

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

Вот такие формулы нужно name): с клавиатуры данные,(Главная) и в столбец (столбцы), который кавычки (например, «California, в этом шаге рассмотрим эти вариантына третьем шаге Для этого нужно данные, то переносим

После этого ячейка будет
Запустите программу. разделить на две К сожалению, такая —David использовать, когда имена,
=RIGHT(A2,LEN(A2)-SEARCH(» «,A2,1)) которые нужно поместить группе команд показан в области
USA»), будут помещены отпадает и его подробнее:

можно выбрать формат выделить весь лист. их туда, нам разбита на две,Зажав левую кнопку мыши категории: те, что возможность в Excel

имениMark которые требуется разбить,=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(» «;A2;1)) в отдельные ячейки.Alignment
Data preview
в одну ячейку. можно пропустить. ГлавноеКак разбить текст с ячейки каждого столбца:
Нажмем левой кнопкой не нужно: и вы сможете

на наименованиях столбцов подразумевают разделение ячейки не поддерживается. ВместоиWhite содержат отчество илиДля тех, кому интересно, По мере ввода(Выравнивание) нажмите маленькую(Образец разбора данных), Если же в не упустите, что разделителями по столбцамв таблице в мыши на пересечениеТочно таким же образом

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

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

пустых столбцов должноКак выделить текстовые данные низу окна видим названий строк и
можно объединить несколько
свои значения. над первой строкой
по вертикали или создать новый столбец
столбцом.=MID(A2,SEARCH(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(« отчества посередине.
формулы, я попробую распознать шаблон в с кнопкой и выберите вариант установить значение быть не меньше, фиксированной величины получившиеся столбцы и столбцов (или комбинация вертикальных ячеек (столбец
Разбить ячейку по диагонали таблицы), выделите два горизонтали. Долго не рядом с тем,
Советы: «,A2,1)+1))

A объяснить более подробно. вводимых значениях, иMerge & CenterDo not import column

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

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

в котором расположена
=ПСТР(A2;ПОИСК(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«
BSEARCH как только он
(Объединить и поместить(Пропустить столбец) в(Нет), тогда слово на которое вы

приглашённых на конференцию написан формат вПодведите курсор к названиям

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

Можно объединить сразу группу помощью инструмента «Фигуры».Находясь на главной вкладке перейдем непосредственно к необходимая ячейка, аСведения об альтернативном способе «;A2;1)+1))C(ПОИСК) или его распознает, данные в центре). Далее разделе «California» будет помещено хотите разделить данные. или какое-то другое данном столбце, столбцов и добейтесь смежных ячеек по Однако стоит учитывать, приложения, нажмите по

методам того, как

fb.ru

Объединение и разбиение данных в ячейках в Excel с форматированием

затем разделить ее. распределения текста поИзвлекаем отчество:DFIND автоматически будут вставлены

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

Как объединить ячейки без потери данных Excel?

Кроме того, содержимое столбцам см. в=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))1(НАЙТИ) – это в остальные ячейки. выберите(Формат данных столбца). а «USA» –

разбить. Затем откройте ниже видно, что

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

В нижней части диалогового

DataParticipant выше выбираете какой мыши и протяните строки в выделенном ваша ячейка образована выберите строку «Объединить разделить ячейку на

ячеек.

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

помощью функций.=LEFT(A2,FIND(» «,A2,1)-2)Отчество позиции определенной текстовой на рисунок ниже:Таким образом объединение ячеек(Готово)!

окна находится область(Данные) >(Участник) перечислены имена

формат нужен. границу, устанавливая размер диапазоне, нажимаем на путем объединения нескольких. по строкам». две, необходимо прибегнутьПример разделения ячеек:Можно объединить ячейки, содержащие=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-2)Фамилия строки в заданнойКак видите, я ввёл

будет отменено, ноЕсли данные состоят изData previewData Tools участников, государство идля длинных цифровых столбца. Ячейки во запись «Объединить поВыделите нужную ячейку.

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

Теперь вместо двух столбцов к некоторым ухищрениям.Выделите одну или несколько

функцию СЦЕП илиКак Вы понимаете, эти2 ячейке. Синтаксис формулы: только пару имён
удовольствие от результата текстовых или числовых(Образец разбора данных).(Работа с данными)
ожидаемая дата прибытия:

значений лучше выбирать всем листе станут строкам».

На вкладку «Вставка» нажмите у вас один.

Это так, потому ячеек, которые хотите

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

функцию СЦЕПИТЬ. формулы работают неSally K. Brooke

=SEARCH(find_text,within_text,[start_num]) в столбец будет испорчено тем, значений с фиксированным Прежде чем нажать >
Необходимо разбить этот текст
формат «числовой», если одинаковыми.В результате получится:

по кнопке «Фигуры». Именно в нем что ячейка – разделить.Выполните следующие действия. только для разделенияSally=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])B

что все данные количеством символов, ВыNextText to Columns на отдельные столбцы, цифр больше 11штВ Excel можно сделатьЕсли хоть одна ячейкаИз выпадающего списка выберите вы можете разделить

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

первичный структурный элемент,Важно:Примечание: имён в Excel.K.

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

(Далее) будет разумным(Текст по столбцам). чтобы таблица имела

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

из категории «Линии» ячейку на две. и она не

exceltable.com

Разбить данные на несколько столбцов

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

 ячейки именами из догадались, что нужно следующим способом. и убедиться, чтоConvert Text to Columns

 направо): сразу сообразил в

 улицы в одну для объединения можетЗажав левую кнопку мыши, вам необходимо: надвое. Из-за этого данные из следующей может включать любое любых данных из=LEFT(A2,FIND(» «,A2,1)-1) нужно искать, а столбца снова использовать функциюК примеру, есть список

 Excel правильно распределил wizardFirst Name

чем дело, а строку. быть недоступна. Необходимо проведите линию от

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

количество строк, но одного столбца по=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1) также позицию символа,A
Text to Columns товаров с ID все данные по
(Мастер распределения текста(Имя), так сейчас ужеНам нужно сделать несколько заверить редактирование и одного угла к
которую желаете разделить объединить ячейки в достаточное пространство на он может содержать нескольким. Например, следующие
Извлекаем отчество: с которого следует. Если вы довольны(Текст по столбцам), и наименованием, причем столбцам. по столбцам). НаLast Name все сделал строк, чтобы каждая

CyberForum.ru

нажать «Ввод» для

Источник

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

Как выглядит склеенный текст в Excel

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

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

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

Выделите только первый столбец, не затрагивая остальные строки (они и так являются пустыми, ведь символы просто не помещаются в рамку одной ячейки и переходят на другие). Перейдите на вкладку «Данные».
Вызовите инструмент «Текст по столбцам».
В большинстве случаев используется формат данных «С разделителями», который и следует отметить маркером, а затем перейти к следующему этапу в Мастере.
Снимите галочку с пункта «Знак табуляции», если в ваших строках применяется другой разделитель для значений. В случае когда действительно разделителем выступает табуляция, оставьте значение по умолчанию и сразу жмите «Далее».
Если сняли галочку со знака табуляции, выберите другой разделитель, использующийся в вашем списке. Программа предлагает три стандартных варианта и возможность указать свой.
В моем случае разделителем выступает косая черта (слэш) – нужно отметить галочкой «Другой» и напечатать этот знак в соответствующем поле. Как только вы это сделаете, ниже сразу отобразится результат выполнения операции.
Если при использовании в качестве разделителя выступают последовательные знаки, например кавычки, отметьте маркером пункт «Считать последовательные разделители одним» и из раскрывающегося списка выберите подходящий для вас вариант.
После нажатия по «Далее» отобразится последний шаг настройки, где вам предстоит задать формат данных в столбце. Этот этап можно пропустить, оставив значение «Общий».
Вернитесь к листу в Excel и убедитесь в том, что разделение прошло успешно. По надобности поправьте размеры ячеек и перенесите то содержимое, которое было отмечено другим разделителем и не посчиталось автоматически.

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

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

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

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

Источник

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

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

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

Чтобы разбить текст в Excel, вы обычно используете функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) или ПСТР (MID) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH). На первый взгляд, некоторые рассмотренные ниже приёмы могут показаться сложными. Но на самом деле логика довольно проста, и следующие примеры помогут вам разобраться.

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

Как распределить ФИО по столбцам
Как использовать разделители в тексте
Разделяем текст по переносам строки
Как разделить длинный текст на множество столбцов
Как разбить «текст + число» по разным ячейкам
Как разбить ячейку вида «число + текст»
Разделение ячейки по маске (шаблону)
Использование инструмента Split Text

В зависимости от вашей задачи эту проблему можно решить с помощью функции ПОИСК (без учета регистра букв) или НАЙТИ (с учетом регистра).

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

Для лучшего понимания пошагово рассмотрим несколько примеров.

Делим текст вида ФИО по столбцам.

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

В столбце A нашей таблицы записаны Фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.

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

Кратко напомним:

На ленте «Данные» выбираем «Текст по столбцам» — с разделителями.

Далее в качестве разделителя выбираем пробел.

Обращаем внимание на то, как разделены наши данные в окне образца.

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

В итоге имеем следующую картину:

При желании можно дать заголовки новым столбцам B,C,D.

А теперь давайте тот же результат получим при помощи формул.

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

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

=ЛЕВСИМВ(A2; ПОИСК(» «;A2;1)-1)

В качестве разделителя мы используем пробел. Функция ПОИСК указывает нам, в какой позиции находится первый пробел. А затем именно это количество букв (за минусом 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего ФИО при помощи ЛЕВСИМВ.

Далее будет чуть сложнее.

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

=ПСТР(A2; ПОИСК(» «;A2) + 1; ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) — 1)

Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:

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

Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:

Начальная позиция — это позиция первого пробела плюс 1:

ПОИСК(» «;A2) + 1

Количество знаков для извлечения: разница между положением 2- ^го и 1- ^го пробелов, минус 1:

ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1

В итоге имя у нас теперь находится в C.

Осталось отчество. Для него используем выражение:

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(» «; A2; ПОИСК(» «; A2) + 1))

В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- ^го пробела. Получаем количество символов после 2- ^го пробела, и функция ПРАВСИМВ их и извлекает.

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

Распределение текста с разделителями на 3 столбца.

Предположим, у вас есть список одежды вида Наименование-Цвет-Размер, и вы хотите разделить его на 3 отдельных части. Здесь разделитель слов – дефис. С ним и будем работать.

Чтобы извлечь Наименование товара (все символы до 1-го дефиса), вставьте следующее выражение в B2, а затем скопируйте его вниз по столбцу:

=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)

Здесь функция мы сначала определяем позицию первого дефиса («-«) в строке, а ЛЕВСИМВ извлекает все нужные символы начиная с этой позиции. Вы вычитаете 1 из позиции дефиса, потому что вы не хотите извлекать сам дефис.

Чтобы извлечь цвет (это все буквы между 1-м и 2-м дефисами), запишите в C2, а затем скопируйте ниже:

=ПСТР(A2; ПОИСК(«-«;A2) + 1; ПОИСК(«-«;A2;ПОИСК(«-«;A2)+1) — ПОИСК(«-«;A2) — 1)

Логику работы ПСТР мы рассмотрели чуть выше.

Чтобы извлечь размер (все символы после 3-го дефиса), введите следующее выражение в D2:

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(«-«; A2; ПОИСК(«-«; A2) + 1))

Аналогичным образом вы можете в Excel разделить содержимое ячейки в разные ячейки любым другим разделителем. Все, что вам нужно сделать, это заменить «-» на требуемый символ, например пробел (« »), косую черту («/»), двоеточие («:»), точку с запятой («;») и т. д.

Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству знаков в разделителе. В нашем примере это дефис (то есть, 1 знак). Если ваш разделитель состоит из двух знаков, например, запятой и пробела, тогда укажите только запятую («,») в ваших выражениях и используйте +2 и -2 вместо +1 и -1.

Как разбить текст по переносам строки.

Чтобы разделить слова в ячейке по переносам строки, используйте подходы, аналогичные тем, которые были продемонстрированы в предыдущем примере. Единственное отличие состоит в том, что вам понадобится функция СИМВОЛ (CHAR) для передачи символа разрыва строки, поскольку вы не можете ввести его непосредственно в формулу с клавиатуры.

Предположим, ячейки, которые вы хотите разделить, выглядят примерно так:

Напомню, что перенести таким вот образом текст внутри ячейки можно при помощи комбинации клавиш ALT + ENTER.

Возьмите инструкции из предыдущего примера и замените дефис («-») на СИМВОЛ(10), где 10 — это код ASCII для перевода строки.

Чтобы извлечь наименование товара:

=ЛЕВСИМВ(A2; ПОИСК(СИМВОЛ(10);A2;1)-1)

Цвет:

=ПСТР(A2; ПОИСК(СИМВОЛ(10);A2) + 1; ПОИСК(СИМВОЛ(10);A2; ПОИСК(СИМВОЛ(10);A2)+1) — ПОИСК(СИМВОЛ(10);A2) — 1)

Размер:

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(СИМВОЛ(10); A2; ПОИСК(СИМВОЛ(10); A2) + 1))

Результат вы видите на скриншоте выше.

Таким же образом можно работать и с любым другим символом-разделителем. Достаточно знать его код.

Как распределить текст с разделителями на множество столбцов.

Изучив представленные выше примеры, у многих из вас, думаю, возник вопрос: «А что, если у меня не 3 слова, а больше? Если нужно разбить текст в ячейке на 5 столбцов?»

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

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

Для первого слова (наименования одежды) используем:

=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)

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

Для второго столбца и далее понадобится более сложное выражение:

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1);1)-1);»»)

Замысел здесь состоит в том, что при помощи функции ПОДСТАВИТЬ мы удаляем из исходного содержимого наименование, которое уже ранее извлекли (то есть, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-M-39-42-50». В нём мы снова ищем позицию первого дефиса, как это делали ранее. И при помощи ЛЕВСИМВ вновь выделяем первое слово (то есть, «Синий»).

А далее можно просто «протянуть» формулу из C2 по строке, то есть скопировать ее в остальные ячейки. В результате в D2 получим

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1);1)-1);»»)

Обратите внимание, жирным шрифтом выделены произошедшие при копировании изменения. То есть, теперь из исходного текста мы удаляем все, что было уже ранее найдено и извлечено – содержимое B2 и C2. И вновь в получившейся фразе берём первое слово — до дефиса.

Если же брать больше нечего, то функция ЕСЛИОШИБКА обработает это событие и вставит в виде результата пустое значение «».

Скопируйте формулы по строкам и столбцам, на сколько это необходимо. Результат вы видите на скриншоте.

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

Как разделить ячейку вида ‘текст + число’.

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

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

Метод 1. Подсчитайте цифры и извлеките это количество символов

Самый простой способ разбить выражение, в котором число идет после текста:

Чтобы извлечь числа, вы ищите в строке все возможные числа от 0 до 9, получаете общее их количество и отсекаете такое же количество символов от конца строки.

Если мы работаем с ячейкой A2:

=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

Чтобы извлечь буквы, вы вычисляете, сколько их у нас имеется. Для этого вычитаем количество извлеченных цифр (C2) из общей длины исходной ячейки A2. После этого при помощи ЛЕВСИМВ отрезаем это количество символов от начала ячейки.

=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))

здесь A2 – исходная ячейка, а C2 — извлеченное число, как показано на скриншоте:

Метод 2: узнать позицию 1- ^й цифры в строке

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

{=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))}

Как видите, мы последовательно ищем каждое число из массива {0,1,2,3,4,5,6,7,8,9}. Чтобы избежать появления ошибки если цифра не найдена, мы после содержимого ячейки A2 добавляем эти 10 цифр. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге получаем опять же массив из 10 цифр — номеров позиций, в которых они нашлись. И из них функция МИН выбирает наименьшее число. Это и будет та позиция, с которой начинается группа чисел, которую нужно отделить от основного содержимого.

Также обратите внимание, что это формула массива и ввод её нужно заканчивать не как обычно, а комбинацией клавиш CTRL + SHIFT + ENTER.

Как только позиция первой цифры найдена, вы можете разделить буквы и числа, используя очень простые формулы ЛЕВСИМВ и ПРАВСИМВ.

Чтобы получить текст:

=ЛЕВСИМВ(A2; B2-1)

Чтобы получить числа:

=ПРАВСИМВ(A2; ДЛСТР(A2)-B2+1)

Где A2 — исходная строка, а B2 — позиция первого числа.

Чтобы избавиться от вспомогательного столбца, в котором мы вычисляли позицию первой цифры, вы можете встроить МИН в функции ЛЕВСИМВ и ПРАВСИМВ:

Для вытаскивания текста:

=ЛЕВСИМВ(A2; МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))-1)

Для чисел:

=ПРАВСИМВ(A2; ДЛСТР(A2)-МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))+1)

Этого же результата можно достичь и чуть иначе.

Сначала мы извлекаем из ячейки числа при помощи вот такого выражения:

=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) -ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

А затем уже берём оставшееся:

=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))

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

Как разделить ячейку вида ‘число + текст’.

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

=ЛЕВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

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

=ПРАВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(B2))

Где A2 — исходная строка, а B2 — искомое число, как показано на снимке экрана ниже:

Как разбить текст по ячейкам по маске (шаблону).

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

Предположим, у вас есть список строк, извлеченных из некоторого файла журнала:

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

Решением является разбиение строки по следующей маске: * ERROR: * Exception: *

Здесь звездочка (*) представляет любое количество символов.

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

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

Итак, в начале ищем позицию первого разделителя.

=ПОИСК(«ERROR:»;A2;1)

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

=ПОИСК(«Exception:»;A2;1)

Итак, для ячейки A2 шаблон выглядит следующим образом:

С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.

Таким образом, в первый столбец мы поместим первые 20 знаков:

=—ЛЕВСИМВ(A2;ПОИСК(«ERROR:»;A2;1)-1)

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

Далее нужно получить код:

=ПСТР(A2;ПОИСК(«ERROR:»;A2;1)+6;ПОИСК(«Exception:»;A2;1)-(ПОИСК(«ERROR:»;A2;1)+6))

Думаю, вы понимаете, что 6 – это количество знаков в нашем слове-разделителе «ERROR:».

Ну и, наконец, выделяем из этой фразы пояснение:

=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«Exception:»;A2;1)+10))

Аналогично добавляем 10 к найденной позиции второго разделителя «Exception:», чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.

Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.

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

Как разделить ячейки в Excel с помощью функции разделения текста Split Text.

Альтернативный способ разбить столбец в Excel — использовать функцию разделения текста, включенную в надстройку Ultimate Suite for Excel. Она предоставляет следующие возможности:

Разделить ячейку по символу-разделителю.
Разделить ячейку по нескольким разделителям.
Разделить ячейку по маске (шаблону).

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

Разделить ячейку по символу-разделителю.

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

Для этого примера возьмем строки шаблона Товар-Цвет-Размер , который мы использовали в первой части этого руководства. Как вы помните, мы разделили их на 3 разных столбца, используя 3 разные формулы . А вот как добиться того же результата за 2 быстрых шага:

Предполагая, что у вас установлен Ultimate Suite , выберите ячейки, которые нужно разделить, и щелкните значок «Разделить текст (Split Text)» на вкладке «Ablebits Data».

Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
- Разверните группу «Разбить по символам (Split by Characters)» и выберите один из предопределенных разделителей или введите любой другой символ в поле «Пользовательский (Custom)» .
- Выберите, как именно разбивать ячейки: по столбцам или строкам.
- Нажмите кнопку «Разделить (Split)» .

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

Готово! Задача, которая требовала 3 формул и 5 различных функций, теперь занимает всего пару секунд и одно нажатие кнопки.

Разделить ячейку по нескольким разделителям.

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

Например, чтобы разделить предложение на части, используя запятые и союзы, активируйте инструмент «Разбить по строкам (Split by Strings)» и введите разделители, по одному в каждой строке:

В данном случае в качестве разделителей мы используем запятую и союз “или”.

В результате исходная фраза разделяется при появлении любого разделителя:

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

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

5.1.2021 12:20

9.8.2021 14:50

Этот формат не является обычным для Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Spilt by strings:

Точка (.) Для разделения дня, месяца и года
Двоеточие (:) для разделения часов и минут
Пробел для разграничения даты и времени

Нажмите кнопку Split, и вы сразу получите результат:

Разделить ячейки по маске (шаблону).

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

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

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

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

Решением является разбиение строки по следующей маске:

* ERROR:* Exception: *

Где звездочка (*) представляет любое количество символов.

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

А теперь нажмите кнопку «Разбить по маске (Split by Mask)» на панели «Split Text» , введите маску в соответствующее поле и нажмите «Split».
Результат будет примерно таким:

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

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

* * ERROR:* Exception: *

Проще говоря, маска указывает надстройке разделить исходные строки на 4 части:

Все символы перед 1-м пробелом в строке (дата)
Символы между 1-м пробелом и словом ERROR: (время)
Текст между ERROR: и Exception: (код ошибки)
Все, что идет после Exception: (текст описания)

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

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

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

Необходимо разделить ФИО по отдельным столбцам

Для первого примера возьмем таблицу с именами нескольких зарегистрированных в ней людей. Это делается с помощью инструмента «Текст по столбцам». После составления одного из документов обнаружилась ошибка: фамилии, имена и отчества прописаны в одну колонку, что создает некоторые неудобства при последующем заполнении документов. Чтобы получить качественный результат, нужно разбить название на отдельные столбцы. Как это сделать — рассмотрим далее. Описание действий:

Давайте откроем документ с ошибкой, которую мы допустили ранее.
Выделите текст, удерживая ЛКМ и растягивая выделение до самой нижней ячейки.
Найдите «Данные» в верхней ленте — вперед.

После открытия находим в группе «Работа с данными» «Текст по столбцам». Щелкните ЛКМ и перейдите к следующему диалоговому окну.

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

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

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

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

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

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

Этап №1. Переносим фамилии

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

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

Выберите ячейку, в которой будет записана информация о фамилии сотрудника. Активируйте его, нажав ЛКМ.

Нажмите на кнопку «Аргументы и функции», активация которой откроет окно редактирования формулы.

Здесь в заголовке «Категория» нужно прокрутить вниз и выбрать «Текст».

Далее находим продолжение формулы ЛЕВОЙ и нажимаем на эту строку. Соглашаемся с действиями, выполняемыми нажатием кнопки «ОК».
Появится новое окно, в котором нужно указать адресацию настраиваемой ячейки. Для этого нажмите на столбец «Текст» и активируйте нужную ячейку. Адресация вводится автоматически.
Чтобы указать необходимое количество символов, вы можете вручную подсчитать их и ввести данные в соответствующий столбец или использовать другую формулу: ПОИСК().
После этого формула отобразится в тексте ячейки. Щелкните по нему, чтобы открыть следующее окно.

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

Принимаем и нажимаем кнопку «ОК».

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

Этап №2. Переносим имена

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

В качестве основной формулы прописываем аналогично предыдущему способу = PSTR(.

Выделите ячейку и укажите, где написан основной текст.
Перейдите в столбец «Исходное положение» и введите формулу ПОИСК().
Перейдем к нему по предыдущей инструкции.

Укажите пробел в поле «Искать текст.
Нажав на «Текст для поиска», активируйте ячейку.

Вернемся к формуле = PSTR в верхней части экрана.
В строке «Исходное положение» мы присваиваем формуле +1. Отсчет начнется со следующего символа пробела.

Приступаем к определению количества символов: вставляем формулу ПОИСК().
Следуйте этой формуле выше и введите все данные так, как вы уже понимаете.
Теперь в строке «Исходная позиция» можно написать формулу поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, ничего не уточняя в «Начальной позиции».
Перейдите к предыдущей формуле ПОИСКА и добавьте +1 к «Исходной позиции.
Вернемся к формуле = PSTR и в строку «Количество символов» добавим выражение ПОИСК («»; A2) -1.

Этап №3. Ставим Отчество

После активации ячейки и передачи аргументов функции выберите ПРАВИЛЬНУЮ формулу. Щелкните «ОК».

В поле «Текст» введите адрес измененной ячейки.
Там, где необходимо указать количество знаков, пишется DLSTR (A2).

Примечание эксперта! Формула автоматически определит количество символов.

Чтобы точно определить количество символов в конце, нужно написать: -ПОИСК().
Перейти к изменению формулы. Введите пробел в поле «Поисковый текст». В «Текст для поиска» — адресация ячеек. В поле «Исходное положение» введите формулу ПОИСК (). Измените формулу с теми же значениями.
Перейти к предыдущему ПОИСКУ и добавить +1 в строку «Исходная позиция.

Перейдите к ПРАВИЛЬНОЙ формуле и убедитесь, что все сделано правильно.

Заключение

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

Источник

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

Инструкция.

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

Чтобы воспользоваться данной функцией потребуется перейти в «Данные» и нажать на кнопку «Текст по столбцам».

Далее выбираем «С разделителями».

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

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

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

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

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

Опять смотрим на образец и заканчиваем процедуру.

Все готово.

Источник

Проверьте, как это работает!

См. также

Нужна дополнительная помощь?

Вариант 1: Разделение столбцов с числами

Вариант 2: Разделение текста

Еще статьи по данной теме:

Помогла ли Вам статья?

Необходимо разделить ФИО по отдельным столбцам

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

Этап №1. Переносим фамилии

Этап №2. Переносим имена

Этап №3. Ставим Отчество

Заключение

Добавить комментарий

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

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

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

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

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

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

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

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

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

См. также

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

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

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

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

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

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

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

Объединение и разбиение данных в ячейках в Excel с форматированием

Как объединить ячейки без потери данных Excel?

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

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

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

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

Разбить данные на несколько столбцов

Как выглядит склеенный текст в Excel

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

Делим текст вида ФИО по столбцам.

Распределение текста с разделителями на 3 столбца.

Как разбить текст по переносам строки.

Как распределить текст с разделителями на множество столбцов.

Как разделить ячейку вида ‘текст + число’.

Метод 1. Подсчитайте цифры и извлеките это количество символов

Метод 2: узнать позицию 1- й цифры в строке

Как разделить ячейку вида ‘число + текст’.

Как разбить текст по ячейкам по маске (шаблону).

Как разделить ячейки в Excel с помощью функции разделения текста Split Text.

Разделить ячейку по символу-разделителю.

Разделить ячейку по нескольким разделителям.

Разделить ячейки по маске (шаблону).

Необходимо разделить ФИО по отдельным столбцам

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

Этап №1. Переносим фамилии

Этап №2. Переносим имена

Этап №3. Ставим Отчество

Заключение

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

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

Метод 2: узнать позицию 1- ^й цифры в строке