Notice: Use of undefined constant REQUEST_URI - assumed 'REQUEST_URI' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 2

Notice: Use of undefined constant DOCUMENT_ROOT - assumed 'DOCUMENT_ROOT' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 5

Notice: Use of undefined constant REQUEST_URI - assumed 'REQUEST_URI' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 5

Notice: Use of undefined constant DOCUMENT_ROOT - assumed 'DOCUMENT_ROOT' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 11

Notice: Use of undefined constant REQUEST_URI - assumed 'REQUEST_URI' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 11

Notice: Use of undefined constant REQUEST_URI - assumed 'REQUEST_URI' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 28

Notice: Use of undefined constant REQUEST_URI - assumed 'REQUEST_URI' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 28

Notice: Use of undefined constant REQUEST_URI - assumed 'REQUEST_URI' in /var/www/www-root/data/www/sound-talk.ru/index.php on line 28

Notice: Undefined variable: flag in /var/www/www-root/data/www/sound-talk.ru/index.php on line 28

Notice: Undefined variable: adsense7 in /var/www/www-root/data/www/sound-talk.ru/index.php on line 39

Notice: Undefined variable: adsense6 in /var/www/www-root/data/www/sound-talk.ru/index.php on line 40
Как в excel разделить фио на три колонки. Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы

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


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

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

Было

Текст в таблице Excel

Стало

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

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

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

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

Выделенный столбец с текстом в Excel

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

Кнопка текст по столбцам

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

Мастер распределения текста по столбцам

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

Флажок пробел и образец разбора данных

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

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

Кнопка Свернуть диалоговое окно

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

Выделяем три столбца

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

Кнопка развернуть диалоговое окно

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

разделить текст в ячейках

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

infedu.ru

Делим слипшийся текст на части

Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:

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

Поехали..

Способ 1. Текст по столбцам

Выделите ячейки, которые будем делить и выберите в меню Данные - Текст по столбцам (Data - Text to columns). Появится окно Мастера разбора текстов:

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

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) - необходимо указать какой именно символ является разделителем:

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании "Иванов, Манн и Фарбер") не делился по запятой внутри названия.

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

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

Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

  • в Excel 2003 и старше - меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor)
  • в Excel 2007 и новее - вкладка Разработчик - Редактор Visual Basic (Developer - Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert - Module) и копируем туда текст вот этой пользовательской функции:

Function Substring(Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) If n > 0 And n - 1 <= UBound(x) Then Substring = x(n - 1) Else Substring = "" End If End Function

Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

где

  • Txt - адрес ячейки с текстом, который делим
  • Delimeter - символ-разделитель (пробел, запятая и т.д.)
  • n - порядковый номер извлекаемого фрагмента

Например:

Способ 3. Разделение слипшегося текста без пробелов

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО "ИвановИванИванович"), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:

Function CutWords(Txt As Range) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len(Txt) If Mid(Txt, i, 1) Like "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then Out = Out & Mid(Txt, i, 1) & " " Else Out = Out & Mid(Txt, i, 1) End If Next i CutWords = Out End Function

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

 Ссылки по теме

 

www.planetaexcel.ru

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

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

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

Разбиваем ячейки в 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,[start_num])=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])

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

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

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

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

=LEFT(text,[num_chars])=ЛЕВСИМВ(текст;[количество_знаков])

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ниже представлено видео проделанных изменений.

msoffice-prowork.com

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

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

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

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

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

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

master tekstov shag 1

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

master tekstov shag 2

Нажатие на кнопку далее приведет к отображению следующего экрана мастера текстов.

master tekstov shag 3

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

Другие материалы по теме:

macros-vba.ru

Функция СЦЕПИТЬ в Excel. Объединить текст из разных столбцов в одну ячейку Эксель

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

У вас есть таблица с несколькими столбцами текстовых данных, а вам нужно объединить такие данные в одну строку, а если таких строк тысячи? Конечно не стоит объединять текст в Excel вручную, даже если у вас маленькие объемы. Ведь есть специальная функция СЦЕПИТЬ в Excel, она сделает это за вас, почти моментально. Чаще всего требуется объединить ФИО в одну ячейку.

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

Функция СЦЕПИТЬ в Excel. Как объединить Фамилию, Имя и Отчество в одну строку

Итак, нам дан список сотрудников, нужно объединить каждое ФИО в отдельную ячейку.

Сцепить2

Сцепить2 В столбце D запишем формулу =СЦЕПИТЬ(A:A;" ";B:B;" ";C:C)

Где каждый аргумент между точкой и запятой это текст который скрепляется. Поскольку нам нужно, чтобы ФИО выглядели красиво между словами мы вставляем пробелы, которые обозначаются как » «.

Кстати, чтобы вставить любые текстовые данные заключите их в кавычки!

В итоге получается:

объединять текст в Excel

объединять текст в Excel
Альтернатива функции СЦЕПИТЬ. Объединять текст в Excel при помощи &

Того же результата можно добиться, если объединять текст в Excel при помощи специального символа — амперсанд, он же &.

Запишем в столбец D такие формулы:

=A:A&" "&B:B&" "&C:C

Сцепить3

Сцепить3

По мне так этот способ даже удобнее.

Сцепить текст и число

Если к объединенному ФИО нужно добавить например число, то это можно сделать аналогично тексту, добавьте столбец с числом

Но здесь есть 1 нюанс — в Эксель вы зачастую округляете число форматом числа. Т.е. само число остается в неизменном виде, например с четырьмя знаками после запятой.СЦЕПИТЬ в Excel

СЦЕПИТЬ в Excel Добавляем в формулу нужное округление =A:A&" "&B:B&" "&C:C&" "&ОКРУГЛ(D:D;0)

Теперь все красиво!

Сцепить текст и дату

Неплохо было бы добавить дату рождения к сотрудникам. Но формула посчитает дату как число. Используем вот такую хитрость — форматируем числовое значение даты в вид Дата функцией ТЕКСТ

=A:A&" "&B:B&" "&C:C&", "&ОКРУГЛ(D:D;0)&", "&ТЕКСТ(F:F;"ДД.ММ.ГГГГ")

Добавим еще запятые для красоты:Сцепить5

Сцепить5
Разделить текст по столбцам

Если у вас даны уже скрепленные ФИО, и надо наоборот разделить текст по столбцам, то у нас есть отдельная статья специально для вас.

 

Пример как сцепить в Excel по всем формулам прикладываю

 

Как сцепить диапазон ячеек? Функция СЦЕП()

В Excel 2016 появилась возможность сцепить несколько ячеек выделив диапазон, а не каждую ячейку по отдельности. Это функция СЦЕП — у нее только один аргумент, просто выберите диапазон ячеек, которые надо объединить.

 

Поиск повторений в больших таблицах данных при помощи объединения текста

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

В отдельном столбце запишем формулу

=СЖПРОБЕЛЫ(A:A)&СЖПРОБЕЛЫ(B:B)&СЖПРОБЕЛЫ(C:C)&СЖПРОБЕЛЫ(D:D)&СЖПРОБЕЛЫ(E:E)

Как видите мы не используем никакие доп. символы, ни запятые, ни пробелы. А функции СЖПРОБЕЛЫ удаляют все пробелы в начале и конце значения каждой ячейки. Так мы исключим человеческий фактор при наборе данных — люди часто случайно ставят пробел в конце слова.

Теперь подсветим все дубликаты при помощи условного форматирования.

Сцепить6

Сцепить6

Красным выделились все повторяющиеся строки:

Сцепить7

Сцепить7

Бывает очень полезно!

 

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

Похожие статьи

(Visited 13 191 times, 18 visits today)

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

excelworks.ru

РАЗДЕЛИТЬ СТОЛБЕЦ НА НЕСКОЛЬКО В EXCEL, Как разделить имя и фамилию в 2 столбца

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

Совет 1: Как в Excel разделить ячейку

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

Выбор разделителя для данных

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

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

Открыть вкладку «Главная», в области «Ячейки» кликнуть «Формат». Одна или несколько выбранных ячеек получит разметку, которую вы указали. Ввести текст в «верхнюю часть» ячейки (она разделена только визуально), которая будет соответствовать строке, например, «название»).

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

Указание формата данных

Будем думать, что excel уже запущен, и нужный текст вставлен в ячейку. У вас откроется мастер: “Мастер текстов (разбор) – шаг 1 из 3″, в котором необходимо выбрать формат исходных данных.

Черта разделяет не одну ячейку, а показывает границы двух ячеек. Ячейки выше «разделенной» и ниже объединены по строкам. Можно изменить ширину ячеек во всем листе. Для этого нужно выделить весь лист. Нажмем левой кнопкой мыши на пересечение названий строк и столбцов (или комбинация горячих клавиш CTRL+A). Подведите курсор к названиям столбцов и добейтесь того, чтобы он принял вид крестика. Ячейки во всем листе станут одинаковыми. Нам нужно сделать несколько строк, чтобы каждая улица была написана в одной строке.

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

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

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

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

Рекомендую:

  • Символы и бонусы бесплатного аппарата Золото ПартииСимволы и бонусы бесплатного аппарата Золото ПартииПонравится игрокам и иконка с Запорожцем, на те времена безумно желанного автомобиля каждым гражданином, но доступным далеко не каждому. В игре Золото Партии играют пять барабанов и 21 […]
  • Рецидив рака простатыРецидив рака простатыТакже необходимо ежегодно проводить пальцевое ректальное исследование, если во время лечения применялась лучевая терапия. Нужна ли какая-то особая диета, чтобы снизить риск рецидива? […]
  • Мезотерапия мезороллеромМезотерапия мезороллеромВ последнее время в продаже появились омолаживающие сыворотки с гиалуроновой кислотой, которые применяют с обязательным использованием мезороллера. Сверхтонкие титановые иглы (примерное их […]

kwartelonak.ru