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

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

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

Пример разделения ячеек:

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

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

1. Выделите одну или несколько ячеек, которые хотите разделить.

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

На вкладке Данные в группе Работа с данными нажмите кнопку Текст по столбцам. Откроется Мастер распределения текста по столбцам.

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

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

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

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

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

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

"ФИО";"Дата рождения";"Пол";"Город проживания";"Профессия"
"Иванов Иван Иванович";"27.03.1985";"Мужской";"Воронеж";"Электрик"
"Петров Петр Петрович";"12.03.1976";"Мужской";"Москва";"Геолог"
"Кухтина Елизавета Федоровна";"11.12.1994";"Женский";"Казань";"Врач"

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

На экране будет отображено окно мастера распределения текста:

Весь процесс состоит из трех шагов.

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

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

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

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

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

Имейте в виду то, что символы-разделители и ограничители удаляются из содержимого разделенных данных.

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

Завершающим шагом является назначение форматирования новым колонкам и указания места для их расположения.

Из возможных настроек предоставлено только несколько форматов (типов данных), а поместить распределенный текст можно только на текущем листе.

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

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

Как разделить ячейку в Экселе? Жмите на необходимую секцию, перейдите в раздел «Работа с таблицами» и в пункте «Макет» в группе «Объединение» жмите на «Разделить ячейки». Выберите число столбцов / строк для разделения по вертикали / горизонтали соответственно. Ниже подробно рассмотрим, как правильно выполнять деление в Excel. Разберем наиболее удобные варианты, которые пользуются наибольшим спросом у пользователей программы.

Как разбить

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

Ячейку

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

1. Жмите на нужную секцию, которую необходимо разделить в Экселе.
2. Перейдите в раздел «Работа с таблицами».
3. Войдите во вкладку «Макет».
4. В группе «Объединение» кликните «Разделить …».
5. Выберите в поле Число столбцов / строк для деления по вертикали / горизонтали соответственно.
6. Если нужно разделить участок в Экселе одновременно по вертикали и горизонтали, введите требуемое значение в графы с количеством столбцов / строк.

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

1. Перед тем, как разбить ячейки в Excel, убедитесь, что «Мгновенное заполнение» включено. Для этого войдите в «Файл». Здесь найдите пункт «Параметры», а далее «Дополнительно» и «Автоматически выполнять мгновенное …».
2. Вставьте новый столбец рядом со столбцом, содержащим исходные данные.
3. Введите желаемую часть текста в 1-ю ячейку.
4. Введите данные еще в пару ячеек.
5. Дождитесь, пока Эксель поймет, по какому шаблону вводятся данные. В дальнейшем он предложит таким методом заполнить остальные.
6. Если вам подходит предложенный вариант, жмите «Ввод».

Если система не предлагает разделить ячейку в Экселе на две, запустите инструмент вручную. Для этого войдите во вкладку «Данные» или жмите на комбинацию Ctrl+E.

Способ №3 (по диагонали):

• Войдите в меню «Формат …» в Экселе.

• На вкладке «Шрифт» определите применяемый шрифт и размер.

• В разделе «Граница» выберите вид линии.
• Жмите на диагональную линию в окне «Отдельные».

• Красиво разместите текст, чтобы он находился красиво над / под линией.
• В разделе «Выравнивание» и в окне «Ориентация» измените наклон текста.

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

Способ №4 (если ранее была объединена):

1. Поставьте курсор на ранее объединенный участок в Экселе.
2. Выберите «Отменить объединение».

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

Столбец

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

Выше мы рассматривали, как разделить ячейку в Экселе на несколько, и для разделения можно использовать указанный в способе №1 вариант. Главное выбрать деление по вертикали.

Но можно использовать еще один вариант:

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

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

• Укажите один или более разделителей, чтобы задать место, где произойдет разделение. В окне «Образец разбора данных» посмотрите не результат.

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

• Кликните на кнопку «Готово».

Строку

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

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

1. Выберите одну строчку в Экселе и копируйте ее с помощью Ctrl+C.
2. Станьте на 1-ю ячейку диапазона и кликните правой кнопкой мышки.
3. Жмите на «Транспонировать» под «Параметры вставки».
4. Проверьте, что одна строка ставится как один столбец, но с несколькими строчками.

Еще один вариант — применение специальной утилиты:

1. Установите Kutools For Excel по ссылке extendoffice.com/product/kutools-for-excel.html.
2. Выберите нужную строчку в Экселе и жмите на название инструмента Kutools.
3. Войдите в «Диапазон», а далее «Диапазон преобразования».
4. В разделе «Тип трансформации» проверьте «От одной строки до диапазона» выберите «Столбцов на запись» и «Фиксированная стоимость» введите число в правое поле.
5. Жмите на «ОК».

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

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Для управления строками текста в данных можно использовать текстовые функции LEFT, MID, RIGHT, SEARCH и LEN. Например, можно распределить имя, середину и фамилию из одной ячейки в три отдельных столбца.

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

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

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

Имя примера

Один средний начальный

Два средних инициала

Фамилия с запятой

Имя, в которое входит две части

Фамилия из трех части

Фамилия с двумя частьми

Фамилия и суффикс с запятой

Фамилия с дефисами

Примечание: На рисунках в следующих примерах выделение в полном имени показывает символ, который ищется в формуле ПОИСК.

Пример 1. Разделение Ивана и Кузнецова

В этом примере разделяется два компонента: имя и фамилия. Два имени отделены одним пробелом.

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Результат (имя)

‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

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

Результат (фамилия)

1. Имя Имя начинается с первого знака в строке (Ю) и заканчивается пятым знаком (пробелом). Формула возвращает пять знаков в ячейке A2, начиная слева. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК. Найдите позицию пробела в A2, начиная слева.
2. Фамилия Фамилия отделена от имени пробелом, начинается с пятого знака справа и заканчивается первым знаком справа (а). Формула извлекает в A2 пять знаков справа. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК и ДЛСТР. Найдите позицию пробела в A2, начиная слева. (5)
3. Подсчитайте общую длину текстовой строки, а затем вычтите количество знаков слева до первого пробела, найденное в пункте 1.

Пример 2. Сергей Климов: извлечение имени и фамилии, а также начального имени и среднего имени

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

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Один средний начальный

Результат (имя)

‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

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

Результат (средний начальный)

‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-SEARCH(» «;A2;1))

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

Live Result (фамилия)

‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

1. Имя Имя начинается с первого знака слева (E) и заканчивается пятым (первым пробелом). Формула извлекает первые пять знаков в A2, начиная слева. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК. Найдите позицию пробела в A2, начиная слева. (5)
2. От имени От второе имя начинается с шестой позиции (S) и заканчивается в 1-й позиции (второй пробел). Эта формула включает вложенные функции ПОИСК для поиска второго экземпляра пробела. Формула извлекает три знака, начиная с шестой позиции. Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК: Поищите позицию первого пробела в A2, начиная с первого знака слева. (5).
3. Чтобы получить позицию знака после первого пробела (S), добавьте 1. Эта позиция является начальной позицией от имени. (5 + 1 = 6) Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК. Поищите позицию первого пробела в A2, начиная с первого знака слева. (5)
4. Чтобы получить позицию знака после первого пробела (S), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
5. Поиск второго пробела в A2, начиная с шестой позиции (S), найденной в шаге 4. Этот номер знака является конечной позицией от имени. (8)
6. Поищите позицию пробела в A2, начиная с первого знака слева. (5)
7. Возьмите номер знака второго пробела, найденного на шаге 5, и вычитайте номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (8 –5 = 3)
8. Фамилия Фамилия начинается с шести знаков справа (K) и заканчивается первым знаком справа (n). Эта формула включает вложенные функции ПОИСК для поиска второго и третьего экземпляров пробела (которые находятся на пятой и пятой позициях слева). Формула извлекает шесть знаков в A2, начиная с правого.
9. Используйте функции LEN и вложенные функции ПОИСК, чтобы найти значение для num_chars: Поищите позицию пробела в A2, начиная с первого знака слева. (5)
10. Чтобы получить позицию знака после первого пробела (S), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
11. Поиск второго пробела в A2, начиная с шестой позиции (S), найденной в шаге 2. Этот номер знака является конечной позицией от имени. (8)
12. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до второго пробела, найденного на шаге 3. Результат — количество символов, извлекаемого справа от полного имени. (14 – 8 = 6).

Пример 3. Янина Б. Г. Bueno

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

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Янина Б. Г. Bueno

Два средних инициала

Результат (имя)

‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

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

Результат (средние инициалы)

‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1)-ПОИСК(» «;A2;1))

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

Live Result (фамилия)

‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

1. Имя Первое имя начинается с первого знака слева (J) и заканчивается символом слева (первый пробел). Формула извлекает первые восемь знаков в A2, начиная слева. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК. Наищите позицию первого пробела в A2, начиная слева. (8)
2. От имени От второе имя начинается с позиций ва (B) и заканчивается 17-й позицией (третий пробел). Эта формула включает вложенную формулу ПОИСК для поиска первого, второго и третьего пробелов в первой, 11-й и 17-й позициях. Формула извлекает пять знаков, начиная с позиций неавтетной позиции. Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК: Поищите позицию первого пробела в A2, начиная с первого знака слева. (8)
3. Чтобы получить позицию знака после первого пробела (B), добавьте 1. Эта позиция является начальной позицией от имени. (8 + 1 = 9) Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК. Поищите позицию первого пробела в A2, начиная с первого знака слева. (8)
4. Чтобы получить позицию знака после первого пробела (B), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (8 + 1 = 9)
5. Найщите вторую позицию в A2, начиная с позиции в направлении «в» (B), найденной в шаге 4. (11).
6. Чтобы получить позицию знака после второго пробела (G), добавьте 1. Этот номер знака является начальной позицией, с которой нужно начать поиск третьего пробела. (11 + 1 = 12)
7. Поиск третьего пробела в A2, начиная с двенадцатой позиции, найденной в шаге 6. (14)
8. Поищите позицию первого пробела в A2. (8)
9. Возьмите номер третьего пробела, найденного на шаге 7, и вычитайте номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с позиций, найденной на шаге 2.
10. Фамилия Фамилия начинается с пяти знаков справа (B) и заканчивается первым знаком справа (o). Эта формула включает вложенную формулу ПОИСК для поиска первого, второго и третьего пробелов. Формула извлекает пять знаков в A2, начиная справа от полного имени. Используйте вложенные функции ПОИСК и LEN, чтобы найти значение для num_chars: Поищите позицию первого пробела в A2, начиная с первого знака слева. (8)
11. Чтобы получить позицию знака после первого пробела (B), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (8 + 1 = 9)
12. Найщите вторую позицию в A2, начиная с позиции в направлении «в» (B), найденной на шаге 2. (11)
13. Чтобы получить позицию знака после второго пробела (G), добавьте 1. Этот номер знака является начальной позицией, с которой нужно начать поиск третьего пробела. (11 + 1 = 12)
14. Поиск третьего пробела в A2, начиная с двенадцатой позиции (G), найденной в шаге 6. (14)
15. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до третьего пробела, найденного на шаге 5. Результат — количество символов, извлекаемого справа от полного имени. (19 – 14 = 5)

Пример 4. Кана, Венди Йет

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

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Фамилия с запятой

Результат (имя)

‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-SEARCH(» «;A2;1))

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

Результат (от имени)

‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

Live Result (фамилия)

‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1)-2)

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

1. Имя Имя начинается с седьмого знака слева (В) и заканчивается двенадцатым (второй пробел). Поскольку имя оказывается в середине полного имени, для его извлечения необходимо воспользоваться функцией ПСТР. Формула извлекает шесть знаков, начиная с седьмого знака. Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК: Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)
2. Чтобы получить позицию знака после первого пробела (О), добавьте 1. Эта позиция является начальной позицией имени. (6 + 1 = 7) Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК. Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)
3. Чтобы получить позицию знака после первого пробела (О), добавьте 1. Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7) Поиск второго пробела в A2, начиная с седьмой позиции (W), найденной в шаге 4. (12)
4. Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)
5. Возьмите номер знака второго пробела, найденного на шаге 5, и вычитайте номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых из текстовой строки, начиная с седьмой позиции, найденной на шаге 2. (12 – 6 = 6)
6. От имени Отчество начинается с четвертого знака справа (Б) и заканчивается первым знаком справа (т). Для поиска первого и второго пробелов на шестой и двенадцатой позициях слева эта формула включает вложенную функцию ПОИСК. Формула извлекает четыре знака, начиная справа. Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК и ДЛСТР: Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)
7. Чтобы получить позицию знака после первого пробела (О), добавьте 1. Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)
8. Поиск второго пробела в A2, начиная с седьмой позиции (W), найденной на шаге 2. (12)
9. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до второго пробела, найденного на шаге 3. Результат — количество символов, извлекаемого справа от полного имени. (16 – 12 = 4)
10. Фамилия Фамилия начинается с первого знака слева (К) и заканчивается четвертым знаком (н). Эта формула извлекает четыре знака, начиная слева. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК. Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)
11. Вычитать 2, чтобы получить позицию последнего знака фамилии (n). Результатом будет количество символов, извлекаемых с левой стороной. (6 – 2 =4)

Пример 5. Mary Kay D. Andersen

В этом примере используется имя из двух части — Mary Kay. Каждый компонент имен разделяется вторым и третьим пробелами.

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Mary Kay D. Andersen

Имя, в которое входит две части

Результат (имя)

ЛЕВ ЛЕВЫЙ(A2; ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1))

=ЛЕВЫЙ(A2; ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1))

Результат (средний начальный)

‘=MID(A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1)-(ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1))

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

Live Result (фамилия)

‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

1. Имя Первое имя начинается с первого знака слева и заканчивается знаком слева (второй пробел). Эта формула включает вложенный поиск, чтобы найти второй пробел слева. Формула извлекает девять знаков, начиная слева. Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК. Поищите позицию первого пробела в A2, начиная с первого знака слева. (5)
2. Чтобы получить позицию знака после первого пробела (К), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
3. Поиск второго пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. Результат — количество символов, извлекаемых левеем из текстовой строки. (9)
4. От имени От второе имя начинается с десятой позиции (D) и заканчивается двенадцатой (третий пробел). Эта формула включает вложенную формулу ПОИСК для поиска первого, второго и третьего пробелов. Формула извлекает два знака из середины, начиная с десятой позиции. Используйте вложенные функции ПОИСК, чтобы найти значение для start_num: Поищите позицию первого пробела в A2, начиная с первого знака слева. (5)
5. Добавьте 1, чтобы получить символ после первого пробела (K). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
6. Поищите позицию второго пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. Результат — количество символов, извлекаемых левее. (9)
7. Добавьте 1, чтобы получить символ после второго пробела (D). Результат — начальная позиция от имени. (9 + 1 = 10) Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК. Поищите позицию знака после второго пробела (D). Результатом будет номер знака, с которого вы хотите начать поиск третьего пробела. (10)
8. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция от имени. (12)
9. Поищите позицию знака после второго пробела (D). Результат — начальная позиция от имени. (10)
10. Возьмите номер третьего пробела, найденного на шаге 6, и вычитайте номер знака «D», найденный на шаге 7. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с десятой позиции, найденной в шаге 4. (12 – 10 = 2)
11. Фамилия Фамилия начинается с восьми знаков справа. Эта формула включает вложенный поиск для поиска первого, второго и третьего пробелов на пятой, четвертой, четвертой и двенадцатой позициях. Формула извлекает восемь знаков справа. Используйте вложенные функции ПОИСК и LEN, чтобы найти значение для num_chars: Наищите позицию первого пробела в A2, начиная слева. (5)
12. Добавьте 1, чтобы получить символ после первого пробела (K). Результат — это номер символа, с которого нужно начать поиск пробела. (5 + 1 = 6)
13. Поиск второго пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. (9)
14. Чтобы получить позицию знака после второго пробела (D), добавьте 1. Результат — начальная позиция от имени. (9 + 1 = 10)
15. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция от имени. (12)
16. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до третьего пробела, найденного на шаге 5. Результат — количество символов, извлекаемого справа от полного имени. (20 – 12 = 8)

Пример 6. Павел Ермоносова де Матто

В этом примере используется фамилия из трех части: Ермолето де Матто. Первый пробел пометит конец имени и начало фамилии.

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

ПолуА Бэрето де Матто

Фамилия из трех части

Результат (имя)

‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

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

Результат (фамилия)

1. Имя Имя начинается с первого знака слева (P) и заканчивается шестым (первый пробел). Формула извлекает шесть знаков слева. Чтобы найти значение для num_chars, используйте функцию Num_chars. Наищите позицию первого пробела в A2, начиная слева. (6)
2. Фамилия Фамилия начинает символы справа (B) и заканчивается первым знаком справа (s). Формула извлекает символы справа. Чтобы найти значение для num_chars, используйте функции LEN и NUM_CHARS. Наищите позицию первого пробела в A2, начиная слева. (6)
3. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до первого пробела, найденного на шаге 1. Результат — количество символов, извлекаемого справа от полного имени. (23 – 6 = 17)

Пример 7. James van Eaton

В этом примере используется фамилия из двух видов: van Eaton. Первый пробел пометит конец имени и начало фамилии.

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Г-н Муксон (James van Eaton)

Фамилия с двумя частьми

Результат (имя)

‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

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

Результат (фамилия)

1. Имя Первое имя начинается с первого знака слева (J) и заканчивается символом слева (первый пробел). Формула извлекает шесть знаков слева. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК. Наищите позицию первого пробела в A2, начиная слева. (6)
2. Фамилия Фамилия начинается с символа справа (v) и заканчивается первым знаком справа (n). Формула извлекает девять знаков справа от полного имени. Чтобы найти значение для num_chars, используйте функции LEN и NUM_CHARS. Наищите позицию первого пробела в A2, начиная слева. (6)
3. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до первого пробела, найденного на шаге 1. Результат — количество символов, извлекаемого справа от полного имени. (15 – 6 = 9)

Пример 8. Бейон мл., Dan K.

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

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Фамилия и суффикс с запятой

Результат (имя)

‘=MID(A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

=MID(A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-SEARCH(» «;A2;1)-SEARCH(» «;A2;1)))

Результат (средний начальный)

‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

Результат (фамилия)

‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

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

Результат (суффикс)

‘=MID(A2;ПОИСК(» «; A2;1)+1;(ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-2)-SEARCH(» «;A2;1))

=MID(A2;ПОИСК(» «; A2;1)+1;(ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-2)-SEARCH(» «;A2;1))

1. Имя Имя начинается с двенадцатого знака (D) и заканчивается 15-м (третий пробел). Формула извлекает три знака, начиная с двенадцатой позиции. Используйте вложенные функции ПОИСК, чтобы найти значение для start_num: Наищите позицию первого пробела в A2, начиная слева. (6)
2. Добавьте 1, чтобы получить символ после первого пробела (J). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)
3. Найщите вторую позицию в A2, начиная с седьмой позиции (J), найденной на шаге 2. (11)
4. Добавьте 1, чтобы получить символ после второго пробела (D). Результатом является начальная позиция имени. (11 + 1 = 12) Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК. Поищите позицию знака после второго пробела (D). Результатом будет номер знака, с которого вы хотите начать поиск третьего пробела. (12)
5. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция имени. (15)
6. Поищите позицию знака после второго пробела (D). Результат — начальная позиция имени. (12)
7. Возьмите номер третьего пробела, найденного на шаге 6, и вычитайте номер знака «D», найденный на шаге 7. Результатом является количество символов, извлекаемого с позиции, которая начинается с двенадцатой строки, найденной в шаге 4. (15 – 12 = 3)
8. От имени От второе имя начинается со второго знака справа (K). Формула извлекает два знака справа. Наищите позицию первого пробела в A2, начиная слева. (6)
9. Добавьте 1, чтобы получить символ после первого пробела (J). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)
10. Найщите вторую позицию в A2, начиная с седьмой позиции (J), найденной на шаге 2. (11)
11. Добавьте 1, чтобы получить символ после второго пробела (D). Результатом является начальная позиция имени. (11 + 1 = 12)
12. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция от имени. (15)
13. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до третьего пробела, найденного на шаге 5. Результат — количество символов, извлекаемого справа от полного имени. (17 – 15 = 2)
14. Фамилия Фамилия начинается с первого знака слева (B) и заканчивается шестым (первый пробел). Поэтому формула извлекает шесть знаков слева. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК. Наищите позицию первого пробела в A2, начиная слева. (6)
15. Суффикс Суффикс начинается с седьмого знака слева (J) и заканчивается символом слева (.). Формула извлекает три знака, начиная с седьмого. Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК: Наищите позицию первого пробела в A2, начиная слева. (6)
16. Добавьте 1, чтобы получить символ после первого пробела (J). Результат — начальная позиция суффикса. (6 + 1 = 7) Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК. Наищите позицию первого пробела в A2, начиная слева. (6)
17. Добавьте 1, чтобы получить позицию символа после первого пробела (J). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (7)
18. Поищите позицию второго пробела в A2, начиная с седьмого знака, найденного в шаге 4. (11)
19. Вычитать 1 из номера знака второго пробела, найденного в шаге 4, чтобы получить номер знака «,». Результат — конечная позиция суффикса. (11 — 1 = 10)
20. Поищите позицию первого пробела в числе. (6)
21. Найдя первый пробел, добавьте 1, чтобы найти следующий символ (J), который также находится в шагах 3 и 4. (7)
22. Возьмите номер знака «», найденный на шаге 6, и вычитайте номер символа «J», найденный в шагах 3 и 4. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с седьмой позиции, найденной в шаге 2. (10 — 7 = 3)

Пример 9. Иван Заметьев III

В этом примере первое имя находится в начале строки, а суффикс — в конце, поэтому можно использовать формулы, похожие на пример 2. Для извлечения имени используйте функцию ЛЕВША, для извлечения фамилии — функцию MID, а для извлечения суффикса — функцию ПРАВБ.

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

Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

Имя примера

Борис Заметьев III

Имя и фамилия с суффиксом