Главная » Разное » Как разделить фио в эксель на три столбца

Как разделить фио в эксель на три столбца


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

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

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

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

  2. На вкладке Данные нажмите кнопку Текст по столбцам.

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

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

  5. Нажмите кнопку Далее.

  6. В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.

  7. Нажмите кнопку Готово.

См. также

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

Разделить ФИО в Excel на ячейки

Как разделить ячейку с ФИО на части

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

Конечно можно написать формулу, которая извлечет нужные данные из ФИО, но даже опытному человеку потребуется 5-7 минут на это. А что если заранее не знаешь расположение фамилии в тексте? Гораздо проще и удобнее пользоваться готовыми функциями из надстройки VBA-Excel. 

Найти фамилию в ФИО

После установки надстройки будет доступна новая функция =ФАМИЛИЯ(ФИО), где 

  • ФИО - текст или ссылка на ячейку, которая содержит ФИО полностью

Задача данной функции - извлечь из ячейки только фамилию. Причем ей не важен порядок. Фамилия может идти как в начале, так в конце или середине текста. 

Найти имя в ФИО

Аналогичная функция для имени =ИМЯ(ФИО). Она извлекает имя из текста с ФИО, также не важно где расположенное.

Найти отчество в ФИО

Последняя функция из этой серии - функция =ОТЧЕСТВО(ФИО) для извлечения отчества.

Примеры работы с ФИО в Excel

Вывести фамилию и инициалы

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

=ФАМИЛИЯ(A1)&" "&ЛЕВСИМВ(ИМЯ(A1))&". "&ЛЕВСИМВ(ОТЧЕСТВО(A1))&"."

где A1 - ссылка на ячейку с фамилией именем и отчеством

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

Также плюс в том, что нам не важно расположение фамилии в тексте, функция определит все автоматически.

Как разбить данные из одного столбца на несколько?

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

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

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

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

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

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

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

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

Как в excel сделать разделение на столбцы?

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

Как делить текст с помощью формул смотрите в специальной статье

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

Наши данные для разбиения:

Данные для разбиения

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

Перейдем по ленте на вкладку «Данные» и выберем иконку «Текст по столбцам» — откроется диалоговое окно:

Мастер разбиения

Так как наши ФИО записаны через пробел, то укажем разделителем пробел:

Указываем разделитель

Кликаем «Готово» (в нашем примере можно не переходить далее по всем шагам мастера разбиения). Получили текст разбитый по столбцам:

Результат разбиения текста

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

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

Спасибо за внимание.

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

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

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

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

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

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

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

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

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

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

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

=ПСТР(A2;1;НАЙТИ(" ";A2;1)-1)

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

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

=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-НАЙТИ(" ";A2;1))

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

=ПСТР(A2;I2+1;50) получаем =ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)+1;50)

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

Пример. Текст по столбцам

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

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

=B2&" "&C2

Пример. Текст по столбцам

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

Поделитесь нашей статьей в ваших соцсетях:

(Visited 40 158 times, 4 visits today)

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

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

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

Как разделить текст в ячейке excel по столбцам по шагам

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

  1. Будем думать, что excel уже запущен, и нужный текст вставлен в ячейку.
  2. Далее переходим на вкладку, там нажимаем кнопку «Текст по столбцам».
  3. У вас откроется мастер: «Мастер текстов (разбор) — шаг 1 из 3», в котором необходимо выбрать формат исходных данных. С разделителями или фиксированная ширина. У нас есть разделитель, точка с запятой. Значит, нам необходимо выбрать форма с разделителями. Нажимаем Далее.
  4. На втором шаге, вам будет предложено выбрать разделитель. Так как у нас таким является точка с запятой, ставим галку напротив нее. Ниже сразу видно будущий результат.  Нажимаем Далее.
  5. На третьем шаге вам предлагается выбрать формат получаемых данных. Можно для каждого столбца выбрать свой формат. Выбираем нужный вам формат, в большинстве случаем подходит «Общий». Нажимаем готово.

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

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

(

оценок, среднее:

из 5)

Интересные статьи по теме:

  • Я автор данного блога, оказываю частную компьютерную помощь в Санкт-Петербурге. Позвоните мне по телефону — +7 (953) 174-82-46

    Компьютерная помощь в СПб

  • Важный опрос
  • Подписка без спама
    • Android
    • CRM
    • Excel
    • HP ALM
    • HP PC
    • HP Software
    • Microsoft
    • Office
    • Oracle
    • SQL
    • Windows
    • Железо
    • Интернет

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

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

  • Разбиваем ячейки при помощи инструмента «Текст по столбцам»
  • Как разбить объединённые ячейки в Excel
  • Разделяем данные в Excel 2013 при помощи инструмента «Мгновенное заполнение»
  • Формулы для разбиения столбцов (имен и других текстовых данных)

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

Инструмент «Текст по столбцам» действительно очень удобен, когда нужно разделить данные из одного столбца по нескольким в Excel 2013, 2010, 2007 или 2003.

«Текст по столбцам» позволяет разбивать значения ячеек, отделённые разделителями, или выделять данные фиксированной ширины (когда все значения содержат определённое количество символов). Давайте рассмотрим эти варианты подробнее:

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

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

Предположим, есть список участников, приглашённых на конференцию или какое-то другое мероприятие. На рисунке ниже видно, что в столбце Participant (Участник) перечислены имена участников, государство и ожидаемая дата прибытия:

Необходимо разбить этот текст на отдельные столбцы, чтобы таблица имела следующие данные (слева направо): First Name (Имя), Last Name (Фамилия), Country (Страна), Arrival Date (Ожидаемая дата прибытия) и Status (Статус).

  1. Если в таблице есть хотя бы один столбец справа от столбца, который необходимо разбить, тогда первым делом создайте новые пустые столбцы, в которые будут помещены полученные данные. Этот шаг необходим для того, чтобы результаты не были записаны поверх уже существующих данных.В нашем примере сразу после столбца Participant находится столбец Status, и мы собираемся добавить между ними новые столбцы Last Name, Country и Arrival Date.Если кто-то забыл, я напомню быстрый способ вставить сразу несколько столбцов на лист Excel. Для этого выберите столбец Status, кликнув по его заголовку, и, удерживая нажатой левую кнопку мыши, протащите указатель вправо, чтобы выделить нужное количество столбцов (сколько хотите вставить). Затем кликните правой кнопкой мыши по выделенной области и в контекстном меню выберите команду Insert (Вставить).

    Результат будет примерно таким, что Вы видите на рисунке ниже (новые столбцы вставлены слева от выделенных столбцов):

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

  1. Выделите столбец, который требуется разбить. Затем откройте вкладку Data (Данные) > Data Tools (Работа с данными) > Text to Columns (Текст по столбцам).
  2. Откроется диалоговое окно Convert Text to Columns wizard (Мастер распределения текста по столбцам). На первом шаге мастера Вы выбираете формат данных. Так как записи разделены пробелами и запятыми, мы выбираем формат Delimited (С разделителями). Вариант Fixed width (Фиксированной ширины) будет рассмотрен чуть позже. Если все готово, жмите Next (Далее), чтобы продолжить.
  3. На следующем шаге определяем разделители, которые содержатся в данных, и ограничитель строк.
    • Настраиваем разделители. Если данные разделены одним или несколькими разделителями, то нужно выбрать все подходящие варианты в разделе Delimiters (Символом-разделителем является) или ввести свой вариант разделителя в поле Other (Другой).В нашем примере мы выбираем Space (Пробел) и Comma (Запятая), а также ставим галочку напротив параметра Treat consecutive delimiters as one (Считать последовательные разделители одним). Этот параметр поможет избежать лишнего разбиения данных, например, когда между словами есть 2 или более последовательных пробела.
    • Настраиваем ограничитель строк. Этот параметр может понадобиться, если в столбце, который Вы разбиваете, содержатся какие-либо значения, заключённые в кавычки или в апострофы, и Вы хотите, чтобы такие участки текста не разбивались, а рассматривались как цельные значения. Например, если Вы выберите в качестве разделителя запятую, а в качестве ограничителя строк – кавычки («), тогда любые слова, заключённые в кавычки (например, «California, USA»), будут помещены в одну ячейку. Если же в качестве ограничителя строк установить значение None (Нет), тогда слово «California» будет помещено в один столбец, а «USA» – в другой.

    В нижней части диалогового окна находится область Data preview (Образец разбора данных). Прежде чем нажать Next (Далее) будет разумным пролистать это поле и убедиться, что Excel правильно распределил все данные по столбцам.

  4. Осталось сделать всего две вещи – выбрать формат данных и указать, куда поместить разделённые ячейки.В разделе Column data format (Формат данных столбца) Вы можете выбрать формат данных отдельно для каждого столбца, в которые будут помещены разделённые данные. По умолчанию для всех столбцов задан формат General (Общий). Мы оставим его без изменений для первых трёх столбцов, а для четвёртого столбца установим формат Data (Дата), что логично, ведь в этот столбец попадут даты прибытия.Чтобы изменить формат данных для каждого конкретного столбца, выделите его, кликнув по нему в области Data preview (Образец разбора данных), а затем установите желаемый формат в разделе Column data format (Формат данных столбца).

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

Совет: Если Вы не хотите импортировать какой-то столбец (столбцы), который показан в области Data preview (Образец разбора данных), то выделите его и выберите вариант Do not import column (Пропустить столбец) в разделе Column data format (Формат данных столбца).

  1. Нажмите Finish (Готово)!

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

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

К примеру, есть список товаров с ID и наименованием, причем идентификатор товара – это 9 символов, которые стоят перед наименованием этого товара:

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

  1. Запустите инструмент Text to Columns (Текст по столбцам), как мы это делали в предыдущем примере. На первом шаге мастера выберите параметр Fixed width (Фиксированной ширины) и нажмите Next (Далее).
  2. В разделе Data preview (Образец разбора данных) настройте ширину столбцов. Как видно на рисунке ниже, край столбца символизирует вертикальная линия, и чтобы задать край следующего столбца, просто кликните в нужном месте. Двойной щелчок по вертикальной линии удалит край столбца, а если Вам нужно переместить границу столбца в другое место, просто перетащите вертикальную линию мышью. На самом деле, все эти инструкции подробно расписаны в верхней части диалогового окна Так как каждый ID товара содержит 9 символов, устанавливаем линию границы столбца на это значение, как показано на рисунке выше.
  3. На следующем шаге выберите формат данных и укажите ячейки, куда поместить результат, как это было сделано в предыдущем примере, а затем нажмите Finish (Готово).

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

Если Вы объединили несколько ячеек на листе Excel и теперь хотите вновь разбить их по отдельным столбцам, откройте вкладку Home (Главная) и в группе команд Alignment (Выравнивание) нажмите маленькую чёрную стрелку рядом с кнопкой Merge & Center (Объединить и поместить в центре). Далее из выпадающего списка выберите Unmerge Cells (Отменить объединение ячеек).

Таким образом объединение ячеек будет отменено, но удовольствие от результата будет испорчено тем, что все данные останутся в левом столбце. Думаю, Вы догадались, что нужно снова использовать функцию Text to Columns (Текст по столбцам), чтобы разбить данные из одного столбца на два или более столбцов.

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

Если Вы уже обновились до Excel 2013, то можете воспользоваться преимуществами нового инструмента «Мгновенное заполнение» и заставить Excel автоматически заполнять (в нашем случае – разбивать) данные, при обнаружении определенной закономерности.

Если Вы ещё не знакомы с этой функцией, я попробую кратко объяснить её суть. Этот инструмент анализирует данные, которые Вы вводите на рабочий лист, и пытается выяснить, откуда они взялись и существует ли в них какая-либо закономерность. Как только «Мгновенное заполнение» распознает Ваши действия и вычислит закономерность, Excel предложит вариант, и последовательность записей в новом столбце появится буквально за мгновение.

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

Первым делом, убедитесь, что инструмент «Мгновенное заполнение» включен. Вы найдёте этот параметр на вкладке File (Файл) > Options (Параметры) > Advanced (Дополнительно) > Automatically Flash Fill (Автоматически выполнять мгновенное заполнение).

Теперь давайте посмотрим, как можно автоматически разбить данные по ячейкам. Итак, Вы включили инструмент «Мгновенное заполнение», и начинаете вводить с клавиатуры данные, которые нужно поместить в отдельные ячейки. По мере ввода Excel будет пытаться распознать шаблон в вводимых значениях, и как только он его распознает, данные автоматически будут вставлены в остальные ячейки. Чтобы понять, как это работает, посмотрите на рисунок ниже:

Как видите, я ввёл только пару имён в столбец B, и «Мгновенное заполнение» автоматически заполнило остальные ячейки именами из столбца A. Если вы довольны результатом, просто нажмите Enter, и весь столбец будет заполнен именами. Очень умный инструмент, не правда ли?

Если «Мгновенное заполнение» включено, но не предлагает никаких вариантов, которые соответствуют определённому шаблону, Вы можете запустить этот инструмент вручную на вкладке Data (Данные) > Flash Fill (Мгновенное заполнение) или нажав сочетание клавиш Ctrl+E.

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

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

Пример 1

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

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

  • Извлекаем имя (столбец First name):

    =LEFT(A2,SEARCH(" ",A2,1)-1)
    =ЛЕВСИМВ(A2;ПОИСК(" ";A2;1)-1)

  • Извлекаем фамилию (столбец Last name):

    =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1))

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

SEARCH (ПОИСК) или FIND (НАЙТИ) – это абсолютно идентичные функции, которые выполняют поиск позиции определенной текстовой строки в заданной ячейке. Синтаксис формулы:

=SEARCH(find_text,within_text,)
=ПОИСК(искомый_текст;текст_для_поиска;)

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

Замечание: Если поиск начинается с первого символа, Вы можете вообще пропустить аргумент start_num (нач_позиция) в формуле и упростить её до такого вида:

=LEFT(A2,SEARCH(" ",A2)-1)
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1)

LEFT (ЛЕВСИМВ) и RIGHT (ПРАВСИМВ) – возвращает левую или правую часть текста из заданной ячейки соответственно. Синтаксис формулы:

=LEFT(text,)
=ЛЕВСИМВ(текст;)

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

=LEFT(A2,SEARCH(" ",A2)-1)
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1)

LEN (ДЛСТР) – считает длину строки, то есть количество символов в заданной ячейке. Синтаксис формулы:

=LEN(text)
=ДЛСТР(текст)

Следующая формула считает количество символов в ячейке A2:

=LEN(A2)
=ДЛСТР(A2)

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

Пример 2

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

A B C D
1 Полное имя Имя Отчество Фамилия
2 Sally K. Brooke Sally K. Brooke
  • Извлекаем имя:

    =LEFT(A2,FIND(" ",A2,1)-1)
    =ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-1)

  • Извлекаем отчество:

    =MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
    =ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1))

  • Извлекаем фамилию:

    =RIGHT(A2,LEN(A2)- FIND(" ",A2,FIND(" ",A2,1)+1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))

Функция MID (ПСТР) – извлекает часть текстовой строки (то есть заданное количество символов). Синтаксис:

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

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

Пример 3

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

A B C D
1 Полное имя Имя Фамилия Суффикс
2 Robert Furlan Jr. Robert Furlan Jr.
  • Извлекаем имя:

    =LEFT(A2,FIND(" ",A2,1)-1)
    =ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-1)

  • Извлекаем фамилию:

    =MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
    =ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1))

  • Извлекаем суффикс:

    =RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))

Пример 4

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

A B C D
1 Полное имя Имя Отчество Фамилия
2 White, David Mark David Mark White
  • Извлекаем имя:

    =MID(A2,SEARCH(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
    =ПСТР(A2;ПОИСК(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1))

  • Извлекаем отчество:

    =RIGHT(A2,LEN(A2)- FIND(" ",A2,FIND(" ",A2,1)+1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))

  • Извлекаем фамилию:

    =LEFT(A2,FIND(" ",A2,1)-2)
    =ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-2)

Пример 5

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

A B C D
1 Полное обозначение Штат Аббревиатура Столица
2 Alabama, AL, Montgomery Alabama AL Montgomery
  • Извлекаем название штата:

    =LEFT(A2,SEARCH(",",A2)-1)
    =ЛЕВСИМВ(A2;ПОИСК(",";A2)-1)

  • Извлекаем аббревиатуру штата:

    =MID(A2,SEARCH(",",A2)+2,SEARCH(",",A2,SEARCH(",",A2)+2)-SEARCH(",",A2)-2)
    =ПСТР(A2;ПОИСК(",";A2)+2;ПОИСК(",";A2;ПОИСК(",";A2)+2)-ПОИСК(",";A2)-2)

  • Извлекаем столицу штата:

    =RIGHT(A2,LEN(A2)-(SEARCH(",",A2,SEARCH(",",A2)+1)+1))
    =ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(",";A2;ПОИСК(",";A2)+1)+1))

А вот пример реальных данных из Excel 2010. Данные из первого столбца разбиты на три отдельных столбца:

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

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

Как разделить текст на столбцы в Microsoft Excel

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

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

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

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

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

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

Подписаться

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

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

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

  2. Вызовите инструмент «Текст по столбцам».

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

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

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

  6. В моем случае разделителем выступает косая черта (слэш) – нужно отметить галочкой «Другой» и напечатать этот знак в соответствующем поле. Как только вы это сделаете, ниже сразу отобразится результат выполнения операции.

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

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

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

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

  1. Выделите столбцы и строки, перейдите на вкладку «Вставка» и нажмите «Таблица».

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

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

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

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

Как разделить текст в MS Excel

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

Смотрите также видеоверсию статьи «Как разделить текст в MS Excel».

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

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

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

Первый способ – супер быстрый

На самом деле в MS Excel существует встроенная возможность быстрого разделения текста в ячейке, если там присутствует или присутствуют разделитель/разделители (например, простой пробел или запятая). Причем таких разделителей может быть несколько, т.е. текст будет разделятся если в строке присутствует или пробел, или точка с запятой или запятая и т.д.

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

В англоязычной версии MS Excel данная команда звучит как «Text to Columns» вкладки «DATA».

После несложных подсказок мастера (на самом деле, в нашем примере после выбора разделителя – пробела можно смело жать «Готово»)

А вот и, собственно, результат.

Второй способ – с использованием формул

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

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

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

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

Поскольку информация о количестве необходимых символах получена, следующим этапом будет использование функции ПСТР (MID).

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

Теперь попробуем соединить промежуточные расчеты в одну формулу.

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

на две части, по диагонали


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

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

Содержание

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

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

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

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

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

=НАЙТИ(» «-A2-1)

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

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

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

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

=НАЙТИ(» «-A2-h3+1)

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

Первый способ: с предварительным объединением ячеек

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

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

  1. Мы видим, что ячейки соединились, продолжаем форматирование. Теперь построим таблицу. Для этого выделим весь диапазон и поставим границы. Чтобы это сделать, перейдите в блок «Шрифт» на «Главной», затем в инструменте «Границы» кликните на пункте «Все границы».

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


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

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

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

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

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

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

=ПСТР(A2-1-h3-1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как объединить ячейки в таблице Word

Функция объединения ячеек в таблице объединяет две или более соседние ячейки в одну большую ячейку.

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

Или они могут быть смежными ячейками, которые охватывают несколько строк и столбцов.

Когда вы выбрали свои ячейки, щелкните правой кнопкой мыши любую из выбранных ячеек, а затем выберите команду «Объединить ячейки» (в английской версии это «Merge Cells») в контекстном меню.

Если вы предпочитаете использовать меню Word, вы также можете перейти на вкладку «Макет», а затем нажать кнопку «Объединить ячейки».

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

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

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

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


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

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

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

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

​ выбираем функцию «Текст​:​ ячейку и заполнить​ строк.​ в параметрах функций:​

​ с помощью функции​​ квадратных скобок. Каким​ по ячейках другие​Чтобы выделить список или​ их содержимого может​ она большая, или​. Или же, набираем​Выделяем два соседних столбца.​. Жмем на неё.​ Microsoft Excel являются​ Excel» тут.​ них сотрется и​ по столбцам». В​_Boroda_​ каждую разделенную ячейку​А именно в ячейке​ ​ будут это квадратные​ НАЙТИ(«]»;A3)+2. Она возвращает​ способом можно быстро​

​ ищут по содержимому​​ таблицу, щелкните любую​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как объединить две таблице в одну в Word

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

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

Когда вы отпускаете кнопку мыши, Word объединяет две таблицы.

Теперь вы знаете, как легко слить и разбить таблицы, ячейки таблиц в Microsoft Word. Можете задавать вопросы в комментариях или присоединяться к нашим группам в соц. сетях: Facebook, Вконтакте.

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

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

Попробуйте!

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

  2. Выберите Данные > Текст как столбца.

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

  4. Выберите разделителя данных. Например, Запятая и Пробел . Вы можете проверить предварительный просмотр данных в окне Data Preview .

  5. Выбрать элемент Далее .

  6. Нажмите кнопку Destination на листе, где вы хотите просмотреть разделенные данные.

  7. Выберите Выход .

Хотите узнать больше?

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

.

Используйте функцию, чтобы разделить текст на разные столбцы

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

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

Совет Установите ширину столбцов A и B на 250 перед вставкой данных на лист.

  • Имя

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

    Используйте вложенные функции SEARCH, чтобы найти значение для start_number:

    Поиск числовой позиции первого пробела в A2, начиная слева.(8)

  • Добавьте 1, чтобы получить символ после первого пробела (J). Результатом является номер символа, с которого вы хотите начать поиск второго пробела. (8 + 1 = 9)

  • Поиск второго пробела в ячейке A2, начиная с девятой позиции (J), найденной на шаге 2. (14)

  • Добавьте 1, чтобы получить символ после второго пробела (P).Результатом является начальная позиция имени. (14 + 1 = 15)

    Используйте вложенный ПОИСК, чтобы найти значение для num_chars:

    Поиск числовой позиции символа после второго пробела (P). Результатом является номер символа, с которого вы хотите начать поиск третьего пробела. (15)

  • Поиск числовой позиции третьего пробела в A2, начиная слева.Результатом является конечная позиция имени. (20)

  • Поиск числовой позиции символа после второго пробела (P). Результатом является начальная позиция имени. (15)

  • Используйте номер третьего пробела, найденный на шаге 6, и вычтите номер символа "P", найденный на шаге 7. Результатом является количество символов, извлеченных функцией FRAGMENT.ТЕКСТ из текстовой строки, начинающейся с пятнадцатой позиции, найденной на шаге 4 (20 - 15 = 5)

  • Отчество

    Отчество начинается со второго символа справа (К). Формула извлекает два символа справа.

    Поиск числовой позиции первого пробела в A2, начиная слева.(8)

  • Добавьте 1, чтобы получить символ после первого пробела (J). Результатом является номер символа, с которого вы хотите начать поиск второго пробела. (8 + 1 = 9)

  • Поиск второго пробела в ячейке A2, начиная с девятой позиции (J), найденной на шаге 2. (14)

  • Добавьте 1, чтобы получить символ после второго пробела (P).Результатом является начальная позиция имени. (14 + 1 = 15)

  • Поиск числовой позиции третьего пробела в A2, начиная слева. Результатом является конечное положение отчества. (20)

  • Подсчитайте общую длину текстовой строки в формате A2, а затем вычтите количество символов слева до третьего пробела, найденное на шаге 5.Результатом является количество символов полного имени, которое нужно извлечь справа. (22 - 20 = 2)

  • Фамилия

    Фамилия начинается с первого символа слева (С) и заканчивается восьмым символом (первым пробелом). Затем формула извлекает восемь символов слева.

    Используйте функцию ПОИСК, чтобы найти значение для num_chars:

    Поиск числовой позиции первого пробела в A2, начиная слева.(8)

  • Суффикс

    Суффикс начинается с девятого символа слева (J) и заканчивается двенадцатым символом слева (.). Формула извлекает четыре символа, начиная с девятого символа.

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

    Поиск числовой позиции первого пробела в A2, начиная слева.(8)

  • Добавьте 1, чтобы получить символ после первого пробела (J). Результатом является начальная позиция суффикса. (8 + 1 = 9)

    Используйте вложенный ПОИСК, чтобы найти значение для num_chars:

    Поиск числовой позиции первого пробела в A2, начиная слева. (8)

  • Добавьте 1, чтобы получить числовое положение символа после первого пробела (J).Результатом является номер символа, с которого вы хотите начать поиск второго пробела. (9)

  • Поиск числовой позиции второго пробела в A2, начиная с девятого символа, найденного на шаге 4. (14)

  • Вычтите 1 из второго номера пробела, найденного на шаге 4, чтобы получить номер символа ",". Результатом является конечное положение суффикса.(14 - 1 = 13)

  • Поиск числовой позиции первого пробела. (6)

  • Найдя первый пробел, добавьте 1, чтобы найти следующий символ (J), который также был найден в шагах 3 и 4. (9)

  • Используйте номер символа ",", найденный на шаге 6, и вычтите номер символа "J", найденный на шагах 3 и 4.Результатом является количество символов, которое функция FRAGMENT.TEX извлекает из текстовой строки, начиная с девятой позиции, найденной на шаге 2. (13 - 9 = 4)

    • .

    Учебное пособие по Excel: Разбиение текста на столбцы

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

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

    Чтобы разбить текст на столбцы, выберите столбцы данных.


    Затем на карточке Данные выбираем команду Текст столбцами .

    Мы выбираем вариант Разделить и переходим Далее , появляются дополнительные параметры выбора текста. Например, данные с двойным пробелом описываются зеленым цветом. Чтобы Excel не создавал дополнительные столбцы, выберите параметр Пробел и Последующие разделители как один текст .

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

    Расстановка переносов — полезная функция, особенно если вы часто копируете данные из других источников в электронные таблицы.

    Для улучшения навыков работы с Excel мы рекомендуем:

    Автор: Эвелина Липка


    Разделение текста на столбцы — Excel Cognity находится под лицензией Creative Commons Attribution-NonCommercial 4.0 International License.

    .

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

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

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

    Раздел с функцией

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

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

    Чтобы "взять" имя из ячейки, нам нужна функция, которая будет брать символы из начала ячейки в пробел, который является символом, разделяющим оба слова. Так что мы будем использовать функцию ЛЕВЫЙ, которая будет брать символы с левой стороны ячейки, и НАЙТИ, которая будет определять, где в нашем тексте есть пробел. Структура нашей функции следующая:

    • ЛЕВЫЙ
      • Текст - указываем ячейку, из которой хотим получить данные, т.е. потому что функция НАЙТИ возвращает в результате номер пробела, в котором находится пробел, и мы хотим взять столько символов, сколько есть перед пробелом.
    • Найти
        • Search_Text - это наше место, введенное в кавычки,
        • W_Text
        • - укажите клетку, в которой мы ищем пространство,
        • [Orning_Number] - аргумент, указывающий, с какого символа мы хотим искать искомый текст - наш аргумент был опущен.

      Конечная функция выглядит следующим образом: = ВЛЕВО (A2, НАЙТИ (""; A2) -1)

      Теперь осталось только "вынуть" фамилию, то есть второе слово.Для этого воспользуемся функцией ФРАГМЕНТ.ТЕКСТ и снова НАЙТИ. Структура наших функций следующая:

      • FRAGMENT.TEXT
        • Text - указываем ячейку из которой хотим получить данные, т.е. A2,
        • Start_number - здесь указываем вложенный1 в его функцию НАЙТИ, добавляем так как функция НАЙТИ возвращает в результате номер места где стоит пробел, и мы хотим начать загрузку символов с места после пробела,
        • Number_zars - мы определяем сколько символов мы хотим скачать .Достаточно ввести число, превышающее длину самой длинной фамилии. Мы ввели 20.
      • НАЙТИ - функция выполнена так же, как и в предыдущем случае.

      Конечная функция выглядит следующим образом: = ФРАГМЕНТ.ТЕКСТ (A2, НАЙТИ (""; A2) +1; 20)

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

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

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

      Шаг 1 из 3:

      На первом шаге мы можем прочитать, что наши данные содержат разделитель - это правда - это символ косой черты (/). Однако у нас все еще есть возможность определить, как выглядят наши данные. Тип файла:

      • Delimited — то есть содержит определенный разделитель, который делит данные на части,
      • Constant width — где мы можем определить какой символ (например,каждые 5) мы должны обмениваться данными.

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

      Шаг 2 из 3:

      На втором шаге мы можем прежде всего выбрать ограничитель, который является нашим разделителем. По умолчанию выбрана Tab, но в нашем случае это косая черта. Чтобы все выставить правильно, нам нужно снять галочку напротив пункта Табулятор, и выбрать следующий пункт Другое и вручную ввести косую черту (/) в текстовое поле.На данный момент мы можем видеть предварительный просмотр в самом низу окна, в котором теперь отображается разбивка по столбцам. Нажмите Далее .

      Шаг 3 из 3:

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

      Чтобы пропустить столбец, щелкните его в области предварительного просмотра, чтобы выбрать его, а затем выберите формат данных Не импортировать столбец (пропустить) .

      Вторым важным моментом является место назначения, которое указывает Excel, в каких ячейках возвращать новые разделенные данные. По умолчанию мастер попросит нас вставить данные в то же место, из которого мы выбрали данные. Если оставить этот выбор, Excel заменит наш столбец данными и другими столбцами справа, поэтому мы не рекомендуем это решение.В примере мы подготовили место для новых, разделенных данных, это столбцы C, D, E, поэтому достаточно ввести адрес ячейки C2 в качестве пункта назначения. Данные будут вставлены вниз и вправо, начиная с этой ячейки.

      Нажмите Готово .

      Может оказаться, что Excel выдаст нам сообщение о существующих данных (как показано ниже).

      Щелкните OK , чтобы принять предупреждение. Простое отображение сообщения связано с настройкой границы ячеек в столбцах C, D, E, поэтому Excel обнаруживает «некоторые данные» в месте назначения, то есть просто формат ячеек.

      Окончательные данные смотрите ниже:

      Инструмент мгновенного заполнения

      Последний метод — использовать инструмент «Мгновенная заливка», хотя не все могут использовать этот инструмент. В Excel он доступен только с версии 2013, и его работа основана на обнаружении шаблона заливки и применении его на последующих строках.

      Чтобы использовать инструмент, введите значение в пустую ячейку, которая должна появиться в результате, при этом в ячейке ниже необходимо запустить инструмент, нажав вкладку Instant Fill на вкладке Data в разделе Data tools или сочетание клавиш Ctrl + E .

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

      Что лучше выбрать?

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

      Самый быстрый метод — использовать Instant Fill , но и самый ненадежный.Часто инструмент не может определить образец заливки и поэтому не выполняет никаких операций.

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

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

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

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

      См. наш учебник: [Excel] Как разделить текст в столбце?

      См. также

      Обучение:

      .

      Смотрите также

    Только новые статьи

    Введите свой e-mail

    Видео-курс

    Blender для новичков

    Ваше имя:Ваш E-Mail:

    Содержание, карта.