Разделение текста по столбцам с помощью функций
Applies ToExcel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel Web App

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

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

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

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

Пример имени

Описание

Имя

Отчество

Фамилия

Суффикс

1

Джефф Смит

Нет отчества

Евгений

Климов

2

Эрик С. Курьян

Один средний инициал

Эрик

S.

Курджан

3

Янаина Б. Г. Буэно

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

Янаина

Б. Ж.

Буэно

4

Кан, Венди Бет

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

Венди

Бет

Кан

5

Мэри Кей Д. Андерсен

Имя из двух частей

Мэри Кей

Г.

Волкова

6

Паула Баррето де Маттос

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

Паула

Баррето де Маттос

7

Джеймс ван Итон

Фамилия из двух частей

Илья

ван Итон

8

Бейкон-младший, Дэн К.

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

Дан

Л.

Бекон

Младший.

9

Гэри Альтман III

С суффиксом

Гэри

Альтман

III

10

Г-н Райан Ириг

С префиксом

Райан

Ириг

11

Джули Taft-Rider

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

Покровская

Taft-Rider

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

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

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

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

Пример имени

Описание

Джефф Смит

Нет отчества

Формула

Result (имя)

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

  1. Имя

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

    Формула для извлечения имени

    Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.

    Найдите позицию пробела в A2, начиная слева.

  2. Фамилия

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

    Формула для извлечения фамилии

    Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК и ДЛСТР.

    Найдите позицию пробела в A2, начиная слева. (5)

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

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

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

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

Пример имени

Описание

Эрик С. Курьян

Один средний инициал

Формула

Result (имя)

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

'=MID(A2;SEARCH(" ",A2;1)+1;SEARCH(" ",A2;SEARCH(" ";A2;1)+1)-SEARCH(" ",A2;1))

=MID(A2,SEARCH(" ",A2;1)+1;SEARCH(" ",A2;SEARCH(" ",A2;1)+1)-SEARCH(" ",A2;1))

Формула

Динамический результат (фамилия)

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

  1. Имя

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

    Формула для разделения имени и фамилии плюс средний начальный

    Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.

    Найдите позицию пробела в A2, начиная слева. (5)

  2. Отчество

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

    Формула извлекает три символа, начиная с шестой позиции.

    Подробности формулы для разделения имени, среднего имени и фамилии

    Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (5).

  3. Добавьте 1, чтобы получить позицию символа после первого пробела (S). Эта числовая позиция является начальной позицией отчества. (5 + 1 = 6)

    Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (5)

  4. Добавьте 1, чтобы получить позицию символа после первого пробела (S). Результатом будет номер символа, с которого вы хотите начать поиск второго экземпляра пробела. (5 + 1 = 6)

  5. Выполните поиск второго экземпляра пробела в A2, начиная с шестой позиции (S), найденной на шаге 4. Этот номер символа является конечной позицией отчества. (8)

  6. Найдите числовую позицию пробела в A2, начиная с первого символа слева. (5)

  7. Возьмите номер символа второго пробела, найденного на шаге 5, и вычесть номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых mid из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (8 –5 = 3)

  8. Фамилия

    Фамилия начинается с шести символов справа (K) и заканчивается первым символом справа (n). Эта формула включает вложенные функции SEARCH для поиска второго и третьего экземпляров пространства (которые находятся в пятой и восьмой позициях слева).

    Формула извлекает шесть символов в A2, начиная с правой стороны.

    Вторая функция SEARCH в формуле для разделения первых, средних и фамилий
  9. Используйте функции LEN и вложенные функции SEARCH, чтобы найти значение для num_chars:

    Найдите числовую позицию пробела в A2, начиная с первого символа слева. (5)

  10. Добавьте 1, чтобы получить позицию символа после первого пробела (S). Результатом будет номер символа, с которого вы хотите начать поиск второго экземпляра пробела. (5 + 1 = 6)

  11. Выполните поиск второго экземпляра пробела в A2, начиная с шестой позиции (S), найденной на шаге 2. Этот номер символа является конечной позицией отчества. (8)

  12. Подсчитайте общую длину текстовой строки в A2, а затем вычесть количество символов слева до второго экземпляра пробела, найденного на шаге 3. Результатом является количество символов, извлекаемых справа от полного имени. (14 – 8 = 6).

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

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

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

Пример имени

Описание

Янаина Б. Г. Буэно

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

Формула

Result (имя)

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

'=MID(A2,SEARCH(" ",A2,1)+1;SEARCH(" ",A2,SEARCH(",A2;SEARCH(" ",A2;1)+1)+1)+1)-SEARCH(" ",A2;1))

=MID(A2,SEARCH(" ",A2;1)+1;SEARCH(" ",A2,SEARCH(" ",A2;1)+1)+1)-SEARCH(" ",A2;1)+1)-SEARCH(" ",A2;1))

Формула

Динамический результат (фамилия)

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2,SEARCH(" ";A2;1)+1)+1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;SEARCH(" ",A2;1)+1)+1))

  1. Имя

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

    Формула для разделения имени, фамилии и двух инициалов

    Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.

    Найдите числовое положение первого пробела в A2, начиная с левого. (8)

  2. Отчество

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

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

    Формула для разделения имени, фамилии и двух инициалов

    Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (8)

  3. Добавьте 1, чтобы получить позицию символа после первого пробела (B). Эта числовая позиция является начальной позицией отчества. (8 + 1 = 9)

    Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (8)

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

  5. Выполните поиск второго пробела в A2, начиная с девятой позиции (B), найденной на шаге 4. (11).

  6. Добавьте 1, чтобы получить положение символа после второго пробела (G). Этот номер символа является начальной позицией, с которой вы хотите начать поиск третьего пробела. (11 + 1 = 12)

  7. Выполните поиск третьего пробела в A2, начиная с двенадцатой позиции, найденной на шаге 6. (14)

  8. Найдите числовое положение первого пробела в A2. (8)

  9. Возьмите номер третьего пробела, найденного на шаге 7, и вычесть номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлеченных MID из текстовой строки, начиная с девятой позиции, найденной на шаге 2.

  10. Фамилия

    Фамилия начинается с пяти символов справа (B) и заканчивается на первом символе справа (o). Эта формула включает вложение поиска для поиска первого, второго и третьего экземпляров пространства.

    Формула извлекает пять символов в A2, начиная с правой части полного имени.

    Формула для разделения имени, фамилии и двух инициалов

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

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (8)

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

  12. Выполните поиск второго пробела в A2, начиная с девятой позиции (B), найденной на шаге 2. (11)

  13. Добавьте 1, чтобы получить положение символа после второго пробела (G). Этот номер символа является начальной позицией, с которой вы хотите начать поиск третьего экземпляра пробела. (11 + 1 = 12)

  14. Выполните поиск третьего пробела в A2, начиная с двенадцатой позиции (G), найденной на шаге 6. (14)

  15. Подсчитайте общую длину текстовой строки в A2, а затем вычесть количество символов слева до третьего пробела, найденного на шаге 5. Результатом является количество символов, извлекаемых справа от полного имени. (19 - 14 = 5)

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

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

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

Пример имени

Описание

Кан, Венди Бет

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

Формула

Result (имя)

'=MID(A2;SEARCH(" ",A2;1)+1;SEARCH(" ",A2;SEARCH(" ";A2;1)+1)-SEARCH(" ",A2;1))

=MID(A2,SEARCH(" ",A2;1)+1;SEARCH(" ",A2;SEARCH(" ",A2;1)+1)-SEARCH(" ",A2;1))

Формула

Результат (отчество)

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

Формула

Динамический результат (фамилия)

'=LEFT(A2, SEARCH(" ";A2;1)-2)

=LEFT(A2, SEARCH(" ",A2;1)-2)

  1. Имя

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

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

    Формула для разделения фамилии, имени и отчества

    Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (6)

  2. Добавьте 1, чтобы получить позицию символа после первого пробела (W). Эта числовая позиция является начальной позицией имени. (6 + 1 = 7)

    Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (6)

  3. Добавьте 1, чтобы получить позицию символа после первого пробела (W). Результатом будет символьный номер, с которого вы хотите начать поиск второго пробела. (6 + 1 = 7)

    Найдите второе место в A2, начиная с седьмой позиции (W), найденной на шаге 4. (12)

  4. Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (6)

  5. Возьмите номер символа второго пробела, найденного на шаге 5, и вычесть номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых MID из текстовой строки, начиная с седьмой позиции, найденной на шаге 2. (12 - 6 = 6)

  6. Отчество

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

    Формула извлекает четыре символа, начиная с правой стороны.

    Формула для разделения фамилии, имени и отчества

    Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК и ДЛСТР:

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (6)

  7. Добавьте 1, чтобы получить позицию символа после первого пробела (W). Результатом будет символьный номер, с которого вы хотите начать поиск второго пробела. (6 + 1 = 7)

  8. Найдите второй экземпляр пробела в A2, начиная с седьмой позиции (W), найденной на шаге 2. (12)

  9. Подсчитайте общую длину текстовой строки в A2, а затем вычесть количество символов слева до второго пробела, найденного на шаге 3. Результатом является количество символов, извлекаемых справа от полного имени. (16 - 12 = 4)

  10. Фамилия

    Фамилия начинается с первого знака слева (К) и заканчивается четвертым знаком (н). Эта формула извлекает четыре знака, начиная слева.

    Формула для разделения фамилии, имени и отчества

    Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (6)

  11. Вычитайте 2, чтобы получить числовое положение конечного символа фамилии (n). Результатом является количество символов, которые требуется извлечь LEFT. (6 - 2 =4)

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

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

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

Пример имени

Описание

Мэри Кей Д. Андерсен

Имя из двух частей

Формула

Result (имя)

LEFT(A2, SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

=LEFT(A2, SEARCH(" ",A2;SEARCH(" ";A2;1)+1))

Формула

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

'=MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-(SEARCH(",A2,SEARCH(" ",A2,1)+1)+1))

=MID(A2,SEARCH(" ",A2;SEARCH(" ",A2;1)+1)+1;SEARCH(" ",A2;SEARCH(",A2;SEARCH(" ",A2;1)+1)+1)-(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

Формула

Динамический результат (фамилия)

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2,SEARCH(" ";A2;1)+1)+1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;SEARCH(" ",A2;1)+1)+1))

  1. Имя

    Первое имя начинается с первого символа слева и заканчивается девятым символом (второй пробел). Эта формула включает вложение поиска для поиска второго экземпляра пространства слева.

    Формула извлекает девять символов, начиная с левого.

    Формула для разделения имени, среднего имени, инициала и фамилии

    Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.

    Найдите числовую позицию первого пробела в A2, начиная с первого символа слева. (5)

  2. Добавьте 1, чтобы получить позицию символа после первого пробела (K). Результатом будет номер символа, с которого вы хотите начать поиск второго экземпляра пробела. (5 + 1 = 6)

  3. Найдите второй экземпляр пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. Результатом является количество символов LEFT, извлекаемых из текстовой строки. (9)

  4. Отчество

    Второе имя начинается с десятой позиции (D) и заканчивается на двенадцатой позиции (третье пространство). Эта формула включает вложение поиска для поиска первого, второго и третьего экземпляров пространства.

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

    Формула для разделения имени, среднего имени, инициала и фамилии

    Используйте вложенные функции SEARCH, чтобы найти значение для 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. Результатом является количество символов, извлеченных MID из текстовой строки, начиная с десятой позиции, найденной на шаге 4. (12 - 10 = 2)

  11. Фамилия

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

    Формула извлекает восемь символов справа.

    Формула для разделения имени, среднего имени, инициала и фамилии

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (5)

  12. Добавьте 1, чтобы получить символ после первого пробела (K). Результатом будет символьный номер, с которого вы хотите начать поиск пробела. (5 + 1 = 6)

  13. Выполните поиск второго пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. (9)

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

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

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

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

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

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

Пример имени

Описание

Паула Баррето де Маттос

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

Формула

Result (имя)

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

  1. Имя

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

    Формула для разделения имени и фамилии из трех частей

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (6)

  2. Фамилия

    Фамилия начинается на семнадцать символов справа (B) и заканчивается первым символом справа (s). Формула извлекает семнадцать символов справа.

    Формула для разделения имени и фамилии из трех частей

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (6)

  3. Подсчитайте общую длину текстовой строки в A2, а затем вычесть количество символов слева до первого пробела, найденного на шаге 1. Результатом является количество символов, извлекаемых справа от полного имени. (23 - 6 = 17)

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

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

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

Пример имени

Описание

Джеймс ван Итон

Фамилия из двух частей

Формула

Result (имя)

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

  1. Имя

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

    Формула для разделения имени и фамилии из двух частей

    Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.

    Найдите числовое положение первого пробела в A2, начиная с левого. (6)

  2. Фамилия

    Фамилия начинается с девятого символа справа (v) и заканчивается первым символом справа (n). Формула извлекает девять символов справа от полного имени.

    Формула для разделения имени и фамилии из двух частей

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (6)

  3. Подсчитайте общую длину текстовой строки в A2, а затем вычесть количество символов слева до первого пробела, найденного на шаге 1. Результатом является количество символов, извлекаемых справа от полного имени. (15 - 6 = 9)

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

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

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

Пример имени

Описание

Бейкон-младший, Дэн К.

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

Формула

Result (имя)

'=MID(A2,SEARCH(" ",A2;SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2;SEARCH(",A2;SEARCH(" ",A2;1)+1)+1)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

=MID(A2,SEARCH(" ",A2;SEARCH(" ",A2;1)+1)+1;SEARCH(" ",A2;A2;SEARCH(" ",A2;1)+1)+1)+1)-SEARCH(" ",A2,SEARCH(" ",A2;1)+1))

Формула

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

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2,SEARCH(" ";A2;1)+1)+1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;SEARCH(" ",A2;1)+1)+1))

Формула

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

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

'=MID(A2;SEARCH(" ", A2;1)+1;(SEARCH(" ",A2;SEARCH(" ";A2;1)+1)-2)-SEARCH(" ",A2;1))

=MID(A2;SEARCH(" ", A2;1)+1;(SEARCH(" ",A2;SEARCH(" ";A2;1)+1)-2)-SEARCH(" ",A2;1))

  1. Имя

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

    Формула для разделения фамилии и суффикса с запятой

    Используйте вложенные функции SEARCH, чтобы найти значение для 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. Результатом является количество символов, извлеченных MID из текстовой строки, начиная с двенадцатой позиции, найденной на шаге 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. Результатом является количество символов, извлекаемых MID из текстовой строки, начиная с седьмой позиции, найденной на шаге 2. (10 - 7 = 3)

В этом примере имя находится в начале строки, а суффикс — в конце, поэтому можно использовать формулы, аналогичные примеру 2. Используйте функцию LEFT для извлечения имени, функцию MID — для извлечения фамилии, а функцию RIGHT — для извлечения суффикса.

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

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

Пример имени

Описание

Гэри Альтман III

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

Формула

Result (имя)

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ",A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ",A2;1)+1))

=MID(A2,SEARCH(" ",A2;1)+1;SEARCH(" ",A2;SEARCH(" ",A2;1)+1)-(SEARCH(" ",A2;1)+1))

Формула

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

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

  1. Имя

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

    Формула для разделения имени и фамилии, за которой следует суффикс

    Найдите числовое положение первого пробела в A2, начиная с левого. (5)

  2. Фамилия

    Фамилия начинается с шестого символа слева (A) и заканчивается одиннадцатым символом (второй пробел). Эта формула включает в себя вложение поиска для поиска позиций пробелов.

    Формула извлекает шесть символов из середины, начиная с шестого символа.

    Формула для разделения имени и фамилии, за которой следует суффикс

    Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:

    Найдите числовое положение первого пробела в A2, начиная с левого. (5)

  3. Добавьте 1, чтобы получить позицию символа после первого пробела (A). Результатом является начальная позиция фамилии. (5 + 1 = 6)

    Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.

    Найдите числовое положение первого пробела в A2, начиная с левого. (5)

  4. Добавьте 1, чтобы получить позицию символа после первого пробела (A). Результатом будет символьный номер, с которого вы хотите начать поиск второго пробела. (5 + 1 = 6)

  5. Найдите числовое положение второго пробела в A2, начиная с шестого символа, найденного на шаге 4. Этот номер символа является конечной позицией фамилии. (12)

  6. Найдите числовое положение первого пробела. (5)

  7. Добавьте 1, чтобы найти числовое положение символа после первого пробела (A), также найденного на шагах 3 и 4. (6)

  8. Возьмите номер символа второго пробела, найденного на шаге 5, а затем вычесть номер символа "A", найденный на шагах 6 и 7. Результатом является количество символов, извлекаемых MID из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (12 - 6 = 6)

  9. Суффикс

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

    Формула для разделения имени и фамилии, за которой следует суффикс

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (5)

  10. Добавьте 1, чтобы получить символ после первого пробела (A). Результатом будет символьный номер, с которого вы хотите начать поиск второго пробела. (5 + 1 = 6)

  11. Выполните поиск второго пробела в A2, начиная с шестой позиции (A), найденной на шаге 2. (12)

  12. Подсчитайте общую длину текстовой строки в A2, а затем вычесть количество символов слева до второго пробела, найденного на шаге 3. Результатом является количество символов, извлекаемых справа от полного имени. (15 - 12 = 3)

В этом примере полному имени предшествует префикс, и для извлечения имени используются формулы, аналогичные примеру 2: функция MID, а функция RIGHT — для извлечения фамилии.

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

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

Пример имени

Описание

Г-н Райан Ириг

С префиксом

Формула

Result (имя)

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ",A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ",A2;1)+1))

=MID(A2,SEARCH(" ",A2;1)+1;SEARCH(" ",A2;SEARCH(" ",A2;1)+1)-(SEARCH(" ",A2;1)+1))

Формула

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

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;SEARCH(" ",A2;1)+1))

  1. Имя

    Первое имя начинается с пятого символа слева (R) и заканчивается на девятом символе (второй пробел). Формула вложена в поле ПОИСК для поиска позиций пробелов. Он извлекает четыре символа, начиная с пятой позиции.

    Формула для разделения имени, перед которым стоит префикс

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (4)

  2. Добавьте 1, чтобы получить позицию символа после первого пробела (R). Результатом является начальная позиция первого имени. (4 + 1 = 5)

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (4)

  3. Добавьте 1, чтобы получить позицию символа после первого пробела (R). Результатом будет символьный номер, с которого вы хотите начать поиск второго пробела. (4 + 1 = 5)

  4. Найдите числовое положение второго пробела в A2, начиная с пятого символа, найденного на шагах 3 и 4. Этот номер символа является конечной позицией первого имени. (9)

  5. Найдите первое пространство. (4)

  6. Добавьте 1, чтобы найти числовое положение символа после первого пробела (R), также найденного на шагах 3 и 4. (5)

  7. Возьмите номер символа второго пробела, найденного на шаге 5, а затем вычесть номер символа "R", найденный на шагах 6 и 7. Результатом является количество символов, извлекаемых mid из текстовой строки, начиная с пятой позиции, найденной на шаге 2. (9 - 5 = 4)

  8. Фамилия

    Фамилия начинается с пяти символов справа. Эта формула включает в себя вложение поиска для поиска позиций пробелов.

    Формула для разделения имени, перед которым стоит префикс

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (4)

  9. Добавьте 1, чтобы получить позицию символа после первого пробела (R). Результатом будет символьный номер, с которого вы хотите начать поиск второго пробела. (4 + 1 = 5)

  10. Выполните поиск второго пробела в A2, начиная с пятой позиции (R), найденной на шаге 2. (9)

  11. Подсчитайте общую длину текстовой строки в A2, а затем вычесть количество символов слева до второго пробела, найденного на шаге 3. Результатом является количество символов, извлекаемых справа от полного имени. (14 - 9 = 5)

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

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

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

Пример имени

Описание

Джули Taft-Rider

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

Формула

Result (имя)

'=LEFT(A2, SEARCH(" ",A2;1))

=LEFT(A2, SEARCH(" ",A2;1))

Формула

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

'=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ",A2;1))

  1. Имя

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

    Формула для разделения первой и дефисной фамилии

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

    Найдите числовое положение первого пробела в A2, начиная с левого. (6)

  2. Фамилия

    Вся фамилия начинается в десять символов справа (T) и заканчивается на первом символе справа (r).

    Формула для разделения первой и дефисной фамилии

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

    Найдите числовое положение пробела в A2, начиная с первого символа слева. (6)

  3. Подсчитайте общую длину извлекаемой текстовой строки, а затем вычесть количество символов слева до первого пробела, найденного на шаге 1. (16 - 6 = 10)

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

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

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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