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

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

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

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

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

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

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

Пример имени

Описание

Имя

Отчество

Фамилия

Мнимая_единица

1

Джефф Смит

Имя не является отчеством

Евгений

Климов

2

От а до Куржан

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

Эрик

Месяца.

Куржан

3

Жанаина B. ж. Буено

Два инициала (отчества)

Жанаина

Байт. Финансовой.

Буено

4

Канн, Ольга Бетт

Фамилия и имя с запятыми

Ольга

Бетт

Канн

5

Мария Кай г. Ивановой

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

Мария Кай

Област.

Волкова

6

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

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

Паула

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

7

Джеймс Van Итон

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

Петр

Van Итон

8

Бэкон мл.

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

Дэн

Звонить.

Бэкон

Мл.

9

Гэри Алтман III

С суффиксом

Гэри

Алтман

3

10

Mr. Райан Ихриг

С префиксом

Райан

Ихриг

11

Юлия Тафт — Редер

Фамилия с переносом

Покровская

Тафт-Редер

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

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

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

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

Пример имени

Описание

Джефф Смит

Имя не является отчеством

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

От а до Куржан

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

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

' = ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + "1" — ПОИСК (""; A2; 1))

= ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1)-ПОИСК (""; A2; 1))

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Отчество

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

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

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

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

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

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

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

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

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

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

  6. Поиск числового места в ячейке A2, начиная с первого знака слева. 5

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

  8. Фамилия

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

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

    Вторая функция поиска в формуле для разделения имени, отчество и фамилии
  9. Используйте функции LEN и Nesting для поиска значения Количество_знаков:

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

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

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

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

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

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

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

Пример имени

Описание

Жанаина B. ж. Буено

Два инициала (отчества)

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

' = ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1) + 1)-ПОИСК (""; A2; 1))

= ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1) + 1)-ПОИСК (""; A2; 1))

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Отчество

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

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

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

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

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

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

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

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

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

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

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

  7. Найдите третий пробел в ячейке A2, начиная с двенадцати положения, которое обнаружено на этапе 6. дюймов

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

  9. Изменяйте номер третьего места, найденного на этапе 7, и вычтите номер первого из пробелов, найденного на этапе 6. Результат — число символов MID извлекается из текстовой строки, начиная с девятой точки, обнаруженной на этапе 2.

  10. Фамилия

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

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

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

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

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

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

  12. Найдите второй пробел в ячейке A2, начиная с девятого положения (B), которое найдено на этапе 2. 11

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

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

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

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

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

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

Пример имени

Описание

Канн, Ольга Бетт

Фамилия и имя с запятыми

Формула

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

' = ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + "1" — ПОИСК (""; A2; 1))

= ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1)-ПОИСК (""; A2; 1))

Формула

Результат (отчеическое имя)

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

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

Формула

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

"= LEFT (A2; ПОИСК (" "; A2; 1)-2)

= LEFT (A2; ПОИСК (""; A2; 1)-2)

  1. Имя

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

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

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

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

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

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

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

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

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

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

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

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

  6. Отчество

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

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

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

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

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

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

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

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

  10. Фамилия

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

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

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

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

  11. Чтобы получить числовое значение завершающего знака фамилии (n), вычтите 2. Результат — количество знаков, которые вы хотите извлечь. (6-2 = 4)

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

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

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

Пример имени

Описание

Мария Кай г. Ивановой

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

Формула

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

LEFT (A2; ПОИСК (""; A2; ПОИСК ("", A2; 1) + 1))

= LEFT (A2; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1))

Формула

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

' = ПСТР (A2; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1) + 1; ПОИСК (""; A2; ПОИСК) + 1) + 1)-(ПОИСК) (""; A2; ПОИСК (""; A2; ""; "

= ПСТР (A2; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1) + 1; ПОИСК (""; A2; ПОИСК) + 1) + 1)-(ПОИСК) (""; A2; ПОИСК (""; A2; ""; "

Формула

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

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

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

  1. Имя

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

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

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

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

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

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

  3. Найдите второй экземпляр места в ячейке A2, начиная с шестого положения (K), которое найдено на этапе 2. Результат — число знаков слева извлекается из текстовой строки. @

  4. Отчество

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

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

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

    Использование вложенных функций поиска для поиска значения "начальная_позиция".

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

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

  6. Найдите позицию второго экземпляра Space в ячейке A2, начиная с шестого положения (K), которое найдено на этапе 2. Результат — число знаков слева, которое извлекается слева. @

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

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

    Найдите числовое значение знака после второго пробела (г). Результат — это номер знака, с которого вы хотите начать поиск по третьему пространству. 5-10

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

  9. Найдите числовое значение знака после второго пробела (г). Результат — начальное расположение отчества. 5-10

  10. Заполните номер третьего символа, найденный в шаге 6, и вычтите число знаков "D", найденное на шаге 7. Результат — число символов MID извлекается из текстовой строки, начиная с десятой точки, обнаруженной на этапе 4. (12-10 = 2)

  11. Фамилия

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

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

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

    Для поиска значения Количество_знаков используются функции поиска с вложенными функциями и ДЛСТР.

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

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

  13. Найдите второй пробел в ячейке A2, начиная с шестого положения (K), которое найдено на этапе 2. @

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

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

ПРАВСИМВ (A2; ДЛСТР (A2)-ПОИСК (""; A2; 1))

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

  1. Имя

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

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

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

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

  2. Фамилия

    Фамилия начинается с Севентин символов справа (B) и заканчивается первым символом справа (-ов). Формула извлекает Севентин знаки справа.

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

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

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

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

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

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

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

Пример имени

Описание

Джеймс Van Итон

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

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

Бэкон мл.

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

Формула

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

' = ПСТР (A2; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1) + 1)-ПОИСК (""; A2; ПОИСК (""; А2; НАЙТИ) + 1); ""; "НАЙТИ"...)

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

Формула

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

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

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

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

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

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

  1. Имя

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

    Формула для разделения последнего имени и суффикса с помощью запятых

    Использование вложенных функций поиска для поиска значения "начальная_позиция".

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

  2. Добавьте 1, чтобы получить символ после первого пробела (J). Результат — это номер знака, с которого вы хотите начать поиск по второму пробелу. (6 + 1 = 7)

  3. Найдите второй пробел в ячейке A2, начиная с седьмой точки (J), которая находится на шаге 2. 11

  4. Добавьте 1, чтобы получить символ после второго пробела (D). Результат — начальное расположение первого имени. (11 + 1 = 12)

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

    Найдите числовое значение знака после второго пробела (г). Результат — это номер знака, с которого вы хотите начать поиск по третьему пространству. 12

  5. Найдите числовое значение третьего места в ячейке A2, начиная слева. Результат — это конечная позиция первого имени. 10-15

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

  7. Заполните номер третьего символа, найденный в шаге 6, и вычтите число знаков "D", найденное на шаге 7. Результат — число символов MID извлекается из текстовой строки, начиная с двенадцати положения, которая находится в действии 4. (15-12 = 3)

  8. Отчество

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

    Формула для разделения последнего имени и суффикса с помощью запятых

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

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

  10. Найдите второй пробел в ячейке A2, начиная с седьмой точки (J), которая находится на шаге 2. 11

  11. Добавьте 1, чтобы получить символ после второго пробела (D). Результат — начальное расположение первого имени. (11 + 1 = 12)

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

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

  14. Фамилия

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

    Формула для разделения последнего имени и суффикса с помощью запятых

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

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

  15. Мнимая_единица

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

    Формула для разделения последнего имени и суффикса с помощью запятых

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

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

  16. Добавьте 1, чтобы получить символ после первого пробела (J). Результат — начальное расположение суффикса. (6 + 1 = 7)

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

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

  17. Добавьте 1, чтобы получить числовое значение знака после первого пробела (J). Результат — это номер знака, с которого вы хотите начать поиск по второму пробелу. 5-7

  18. Найдите числовое значение второго пробела в ячейке A2, начиная с седьмого символа, найденного на этапе 4. 11

  19. Вычтите 1 из числового символа второго места, найденного на этапе 4, чтобы получить число знаков ",". Результат — конечное расположение суффикса. (11-1 = 10)

  20. Найдите числовое значение первого пробела. 152

  21. После того как вы найдете первый пробел, добавьте 1, чтобы найти следующий символ (J), а также в шагах 3 и 4. 5-7

  22. Заполните знак ",", найденный на шаге 6, и вычтите число "J", найденное в шагах 3 и 4. Результат — число символов MID извлекается из текстовой строки, начиная с седьмой точки, которая находится на этапе 2. (10-7 = 3)

В этом примере первое имя находится в начале строки, а суффикс — в конце, поэтому вы можете использовать формулы, похожие на пример 2: используйте функцию LEFT для извлечения имени, а функция ПСТР — для извлечения фамилии. , а Функция ПРАВСИМВ — для извлечения суффикса.

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

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

Пример имени

Описание

Гэри Алтман III

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

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

' = ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1); + 1)-(ПОИСК (""; A2; ""; + 1) + 1))

= ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1)-(ПОИСК (""; A2; ""; + 1) + 1))

Формула

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

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

= ПРАВСИМВ (A2; ДЛСТР (A2)-ПОИСК (""; A2; ПОИСК (""; 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. 152

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

  9. Мнимая_единица

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

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

    Для поиска значения Количество_знаков используются функции поиска с вложенными функциями и ДЛСТР.

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

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

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

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

В этом примере полное имя предшествует префиксу, и вы используете формулы, аналогичные примеру 2: функция ПСТР используется для извлечения первого имени, а Функция ПРАВСИМВ — для извлечения последнего имени.

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

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

Пример имени

Описание

Mr. Райан Ихриг

С префиксом

Формула

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

' = ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1); + 1)-(ПОИСК (""; A2; ""; + 1) + 1))

= ПСТР (A2; ПОИСК (""; A2; 1) + 1; ПОИСК (""; A2; ПОИСК (""; A2; 1) + 1)-(ПОИСК (""; A2; ""; + 1) + 1))

Формула

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

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

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

  1. Имя

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

    Формула для разделения имени и фамилии, предшествующей префиксу

    Используйте функцию Поиск для поиска значения нач_позиция.

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

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

    Используйте вложенную функцию поиска для поиска значения Количество_знаков:

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

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

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

  5. Выполните поиск первого пробела. четырехпроцессорном

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

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

  8. Фамилия

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

    Формула для разделения имени и фамилии, предшествующей префиксу

    Для поиска значения Количество_знаков используются функции поиска с вложенными функциями и ДЛСТР.

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

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

  10. Найдите второй пробел в ячейке A2, начиная с пятого положения (R), которое находится в действии 2. @

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

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

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

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

Пример имени

Описание

Юлия Тафт — Редер

Фамилия с переносом

Формула

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

' = LEFT (A2; ПОИСК (""; A2; 1))

= LEFT (A2; ПОИСК (""; A2; 1))

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

См. также

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

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×