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

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

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

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

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

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

Пример имени

Описание

Имя

Отчество

Фамилия

Суффикс

1

Юлия Ильина

Без отчества

Юлия

Ильина

2

Эрик С. Курджан

Имя и один инициал

Эрик

С.

Курджан

3

Bueno Janaina B. ж.

Имя и два инициала

Ханаина

Б. Г.

Буэно

4

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

Сначала фамилия, с запятой

Венди

Бет

Кан

5

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

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

Мэри Кей

Д.

Андерсен

6

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

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

Паула

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

7

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

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

Джеймс

ван Итон

8

Бэкон мл., Дэн К.

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

Дэн

К.

Бэкон

мл.

9

Гэри Алтман III

С суффиксом

Гэри

Алтман

III

10

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

C префиксом

Райан

Ириг

11

Джули Тафт-Райдер

Двойная фамилия через дефис

Джули

Тафт-Райдер

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

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

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

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

Пример имени

Описание

Юлия Ильина

Без отчества

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

Эрик С. Курджан

Имя и один инициал

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

=ЛЕВСИМВ(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. Имя

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

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

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

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

  2. Отчество

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

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

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

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

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

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

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

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

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

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

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

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

  8. Фамилия

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

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

    Вторая функция поиска в формуле для разеделения имени, среднего имени и фамилии
  9. Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК и ДЛСТР:

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

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

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

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

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

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

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

Пример имени

Описание

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

Имя и два инициала

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Отчество

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

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

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

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

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

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

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

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

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

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

  6. Для получения позиции знака после второго пробела (Г) добавьте 1. Результатом будет начальная позиция с которой начинается поиск третьего пробела (11 + 1 = 12).

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

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

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

  10. Фамилия

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

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

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

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

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

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

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

  13. Для получения позиции знака после второго пробела (Г) добавьте 1. Результатом будет начальная позиция с которой начинается поиск третьего пробела (11 + 1 = 12).

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

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

В этом примере фамилия ставится перед именем и вторым именем. Фамилия отделяется запятой, а имя и второе имя — пробелом.

Скопируйте ячейки из таблицы и вставьте на лист Excel, начиная с ячейки A1. Формула слева будет отображаться для справки, а формулу справа 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))

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1)-2)

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

  1. Имя

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

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

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

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

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

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

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

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

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

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

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

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

  6. Отчество

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

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

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

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

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

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

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

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

  10. Фамилия

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

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

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

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

  11. Для получения позиции последнего знака фамилии (н) вычтите 2. Результатом будет количество знаков, необходимых для извлечения функцией ЛЕВСИМВ (5 - 2 =3).

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

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

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

Пример имени

Описание

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

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

Формула

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

ЛЕВСИМВ(A2, ПОИСК(" ",A2,ПОИСК(" ",A2,1)+1))

=ЛЕВСИМВ(A2, ПОИСК(" ",A2,ПОИСК(" ",A2,1)+1))

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

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

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

  3. Найдите второй пробел в ячейке A2, начиная с шестой позиции (К), найденной на шаге 2. Результатом будет количество знаков, которое функция ЛЕВСИМВ извлекает из текстовой строки (9).

  4. Отчество

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

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

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

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

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

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

  6. Найдите позицию второго пробела в A2, начиная с восьмой позиции (К), найденной на шаге 2. Результатом будет количество знаков, которое функция ЛЕВСИМВ извлекает из текстовой строки (9).

  7. Для получения позиции знака после второго пробела (Д) добавьте 1. Результатом будет начальная позиция для отчества (9 + 1 = 10).

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

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

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

  9. Найдите позицию знака после второго пробела (Д). Результатом будет начальная позиция отчества (10).

  10. Возьмите численное значение третьего пробела, найденное на шаге 6, и вычтите значение (Д), найденное на шаге 7. Результатом будет число знаков, которое функция ПСТР извлекает из текстовой строки, начиная с десятой позиции, найденной на шаге 4 (12 - 10 = 2).

  11. Фамилия

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

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

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

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

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

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

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

  14. Для получения позиции знака после второго пробела (Д) добавьте 1. Эта позиция будет начальной позицией для отчества (9 + 1 = 10).

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

Бэкон мл., Дэн К.

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

Формула

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

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

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

Формула

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

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

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

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

=ЛЕВСИМВ(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. Имя

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

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

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

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

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

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

  4. Для получения знака после второго пробела (Д) добавьте 1. Результатом будет начальная позиция для имени (11 + 1 = 12).

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

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

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

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

  7. Возьмите численное значение третьего пробела, найденное на шаге 6, и вычтите значение (Д), найденное на шаге 7. Результатом будет число знаков, которое функция ПСТР извлекает из текстовой строки, начиная с двенадцатой позиции, найденной на шаге 4 (15 - 12 = 3).

  8. Отчество

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

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

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

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

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

  11. Для получения знака после второго пробела (Д) добавьте 1. Результатом будет начальная позиция для имени (11 + 1 = 12).

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

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

  14. Фамилия

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

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

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

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

  15. Суффикс

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

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

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

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

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

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

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

  17. Для получения позиции знака после первого пробела (м) добавьте 1. Результатом будет позиция, с которой начинается поиск второго пробела (7).

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

  19. Для получения позиции знака запятой (,) вычтите 1 из численного значения второго пробела, найденного на шаге 4. Результатом будет конечная позиция суффикса (11 - 1 = 10).

  20. Найдите позицию первого пробела (6).

  21. После нахождения первого пробела добавьте 1 для поиска позиции следующего символа (м), которая также находилась в пунктах 3 и 4 (7).

  22. Возьмите численное значение знака запятой (,), найденное на шаге 6, и вычтите численное значение знака (м), найденное на шагах 3 и 4. Результатом будет количество знаков, которое функция ПСТР извлекает из текстовой строки, начиная с седьмой позиции, найденное на шаге 2 (10 - 7 = 3).

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

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

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

Пример имени

Описание

Гэри Алтман III

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

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

=ЛЕВСИМВ(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. Имя

    Имя начинается с первого знака слева (Г) и заканчивается пятым знаком (первый пробел). Поэтому формула извлекает пять знаков слева от полного имени.

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

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

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

  6. Найдите позицию первого пробела (5).

  7. Для получения позиции знака после первого пробела (А), также найденной на шагах 3 и 4, добавьте 1 (6).

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

  9. Суффикс

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

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

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

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

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

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

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

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

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

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

Пример имени

Описание

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

C префиксом

Формула

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

'=ПСТР(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. Имя

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

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

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

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

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

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

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

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

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

  5. Найдите первый пробел (4).

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

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

  8. Фамилия

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

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

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

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

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

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

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

В этом примере фамилия пишется через дефис. Компоненты имени разделены пробелом.

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

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

Пример имени

Описание

Джули Тафт-Райдер

Двойная фамилия через дефис

Формула

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

'=ЛЕВСИМВ(A2, ПОИСК(" ",A2,1))

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

    Полная фамилия начинается с одиннадцатого знака справа (Т) и заканчивается первым знаком справа (р).

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

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

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

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

Примечания : 

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

  • Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала. Вместе с другими участниками сообщества Майкрософт вы можете улучшить эту статью с помощью Community Translation Framework (CTF). Просто наведите указатель мыши на предложение в статье и выберите команду "УЛУЧШИТЬ ПЕРЕВОД" в мини-приложении CTF. Для получения дополнительных сведений о CTF щелкните здесь. Используя CTF, вы соглашаетесь с нашими условиями предоставления услуг.

См. также

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

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

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

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

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

×