Распределение содержимого ячейки на соседние столбцы. Как в excel разбить текст на колонки

«Текст по столбцам», «Мгновенное заполнение» и формулы.

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

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

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

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

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

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

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

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

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

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

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

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

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

Как в excel 2007 разделить текст по столбцам на две, три и более ячеек. Обычно для примера используют разбивку ячеек с ФИО, чтоб данные были раздельно.

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

Мне по работе пришлось разделять время в ячейках таблицы excel , пример: 0:00-15:00. А между значениями разделитель точка с запятой 0:00-15:00;16:00-17:00;22:00-24:00. Эти данные в таблице по дням, в ячейках разное количество значений между разделителем. Необходимо было разделить время по каждому дню, в столбик под датой проставить время, разделить время на начало и конец, посчитать разницу и все сложить вместе...

Этап первый. Разделить время в ячейках

Выделяем диапазон ячеек, на ленте нажимаем Данные-Текст по столбцам, появится Мастер текстов.

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

Снимаем галку "знак табуляция", ставим галку "другой"

и в окошко вносим точку с запятой [ ; ]. Жмем либо далее, хотя там особо смотреть не чего, верней нажать готово. Будет задан вопрос: заменить содержимое ячеек? Ответ да!

Мы смогли разбить ячейку на столько значений, сколько их было между [ ; ]. Добавим пустые строки под ячейкой с датой, в количестве, ячеек правей столбца С.

Две ячейки-две строки. Выделяем ячейки, копируем,

и под первой ячейкой жмем правой кнопкой "Специальная вставка"- "Транспонировать".

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

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

Этап второй. Как в экселе разделить ячейку на время начала и конца

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

Вынесем столбец за нашу таблицу, скопировав и вставив ячейки рядом. Работаем с мастером: Данные-Текст по столбцам, шаг первый ничего не меняем, на втором шаге в окошке другой [ - ] ставим минус, нажимаем готово.

Этап третий

Ставим формулу: время конца минус время начала,

формат ячеек Все форматы [ч]:мм,

протянем формулу вниз до конца. Мы получили время в часах. В соседней ячейке напишем: =К4*60 Enter. Получим время в минутах.

Правда с секундами... Не нашел способа лучше, чем скопировать столбец, вставить его в блокнот, нажать CTRL+H (Найти и заменить), в найти написал:00

а заменить оставил пустым....

Нажал заменить все...Текст без:00 скопировал из блокнота и вставил в таблицу.

С количеством часов поступил проще, скопировал в блокнот и вставил в таблицу. Одна проблема: 24 часа стоят как 24:00:00.

Не проблема, раз мы знаем, как в экселе разделить ячейку , то тут можно и Copy&Paste применить, но с хитростью - в ячейке перед 24:00 поставить апостроф ‘ -одиночную кавычку "24:00

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

В моем примере нечто из 31 дня проработало 28 дней,из 744 часов -673 часа и из 44640 минут -40411 минут... Не важно что это, главное ч

comuedu.ru

Excel – Текст по столбцам, пример | Info-Comp.ru

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

Тем, кто решили заняться освоением такого программного продукта как Microsoft Excel, советую просмотреть следующие материалы, так как мы там уже затрагивали такие темы как программирование в Excel, функции Excel и другое, вот пару статей:

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

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

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

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

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

Текст по столбцам Excel 2003

Начнем с 2003 excel, и для того чтобы воспользоваться функционалом текст по столбцам, выделите необходимый столбец и нажимайте Данные->Текст по столбцам

После чего у Вас откроется мастер данной возможности, и на экране Вы увидите следующее окно, шаг 1

Вы здесь выбираете формат данных «с разделителем» и жмете далее.

А далее у Вас откроется окно с шагом 2:

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

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

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

Вам лишь останется задать имена столбцов и все.

Текст по столбцам Excel 2010

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

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

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

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

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

Было

Стало

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

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

ВАЖНО: Выделять можно любое количество строк, но не более одного столбца. Убедитесь, что справа имеется достаточное количество пустых столбцов и никакие данные не будут перезаписаны. Если пустых столбцов недостаточно, добавьте их.

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

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

Установите флажок пробел, а затем снимите остальные флажки или установите флажки запятая и пробел, если эти символы являются символами-разделителями текста (например: Иванов, Иван — с запятой и пробелом между фамилией и именем). Все данные можно увидеть в окне Образец разбора данных. Нажмите кнопку Далее.

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

Мы оставляем все по умолчанию и нажимаем на кнопку Свернуть диалоговое окно справа от поля Поместить в.

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

Нажимаем на кнопку Развернуть диалоговое окно

В появившемся окне Мастер текстов, нажимаем кнопку Готово

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

infedu.ru

InformatikUrok » Blog Archive » Как в Excel разбить текст по столбцам?

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

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

Мотивация: бухгалтера создают списки сотрудников для различной отчетности в Excel, и чаще всего

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

Поэтому сегодня мы рассмотрим вопрос, как в Excel 2010 разбить текст по столбцам.

Для того, чтобы разбить текст из одной ячейки Excel 2010 по столбцам, нужно:

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

Вот, что на данном этапе у вас получится в Excel 2010:

Дальше появится окошко, в котором нужно выбрать знак, с помощью которого разделены все слова, которые нужно разбить на столбцы. В нашем случае – это знак пробела, поэтому ЛКМ щелкаем напротив слова Пробел. Результат разбиения сразу же отобразится в этом же окошке, см. рис.:

Можно, конечно, на этом этапе нажать кнопку Готово. Тогда результат запишется в диапазоне А1:С10, удалив первоначальные данные.

Если нужно сохранить данные столбца А, то нажимаем в предыдущем окошке на кнопку Далее и в открывшемся окне указываем, что данные наши Текстовые и разместить их нужно в диапазоне =$B$1:$D$10, так как показано на рис.:

Сегодня на занятии узнали:

как разбить текст одного столбца на три столбца;
как разместить вновь созданные столбцы с данными на листе Excel.

informatikurok.ru

Текст по столбцам в Excel

15.12.2015
Просмотров: 3558
Excel
Видеоурок

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

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

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

Теперь перейдите во вкладку "Данные" и выберите пункт "Текст по столбцам".

Появится окно под названием "Мастер текстов", в котором необходимо выбрать "Формат исходных данных".

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

Выбираем пункт "С разделителями" и нажимаем кнопку "Далее".

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

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

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

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

Не забудьте поделиться ссылкой на статью ⇒

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

04.11.2015
Просмотров: 7055
Excel
Видеоурок

Читать полностью

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

27.11.2015
Просмотров: 2334
Excel

Читать полностью

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

21.11.2015
Просмотров: 3452
Excel
Видеоурок

Читать полностью

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

03.11.2015
Просмотров: 2388
Excel
Видеоурок

Читать полностью

15.12.2015
Просмотров: 3558
Excel
Видеоурок

Читать полностью

4upc.ru

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

Те, кто работает в продажах, в маркетинге или в любом другом направлении, которое использует

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

Уроки MS Excel

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

Уроки MS Excel

Если в Excel необходимо отобразить только записи, удовлетворяющие определённому критерию, то используйте фильтр. Для

Уроки MS Excel

В Excel можно сортировать данные по одному или нескольким столбцам. Сортировка может быть выполнена

Уроки MS Excel

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

office-guru.ru

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

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

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

Выполните следующие действия.

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и функциональные клавиши в Excel

Текстовые функции (справка)

Функции Excel (по алфавиту)

Функции Excel (по категориям)

support.office.com