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

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

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

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

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

Имя примера

Описание

Имя

От имени

Фамилия

Суффикс

1

Иван Иванов

От имени нет

Евгений

Климов

2

Сергей Куймина

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

Эрик

S.

Керуаля

3

Янина Б. Г. Bueno

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

Янина

B. Г.

Bueno

4

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

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

Венди

Бет

Кан

5

Mary Kay D. Andersen

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

Mary Kay

D.

Волкова

6

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

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

Паула

Ермно-де-Матто

7

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

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

Джеймс

van Eaton

8

Бейон мл., Дэн К.

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

Дэн

K.

Бекон

Младший.

9

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

С суффиксом

Гэри

Альтман

Iii

10

Г-н Артем Ихигов

С префиксом

Райан

Ihrig

11

Джулия Taft-Rider

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

Покровская

Taft-Rider

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

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

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

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

Имя примера

Описание

Иван Иванов

От имени нет

Формула

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

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

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

Формула

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

'=ПРАВАЯ(A2;LEN(A2)-SEARCH(" ";A2;1))

=ПРАВБ(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Имя примера

Описание

Сергей Куймина

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

Формула

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

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

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

Формула

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

'=MID(A2;ПОИСК(" ";A2;1)+1;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)-SEARCH(" ";A2;1))

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

Формула

Live Result (фамилия)

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

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

  1. Имя

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

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

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

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

  2. От имени

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

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

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

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

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

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

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

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

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

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

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

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

  8. Фамилия

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

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

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

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

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

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

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

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

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

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

Имя примера

Описание

Янина Б. Г. Bueno

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

Формула

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

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

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

Формула

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

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

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

Формула

Live Result (фамилия)

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

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

  1. Имя

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

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

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

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

  2. От имени

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

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

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

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

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

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

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

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

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

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

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

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

  8. Поищите позицию первого пробела в A2. (8)

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

  10. Фамилия

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

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

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

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

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

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

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

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

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

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

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

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

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

Имя примера

Описание

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

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

Формула

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

'=MID(A2;ПОИСК(" ";A2;1)+1;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)-SEARCH(" ";A2;1))

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

Формула

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

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

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

Формула

Live Result (фамилия)

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

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

  1. Имя

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

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

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

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

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

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

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

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

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

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

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

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

  6. От имени

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

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

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

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

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

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

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

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

  10. Фамилия

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

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

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

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

  11. Вычитать 2, чтобы получить позицию последнего знака фамилии (n). Результатом будет количество символов, извлекаемых с левой стороной. (6 – 2 =4)

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

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

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

Имя примера

Описание

Mary Kay D. Andersen

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

Формула

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

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

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

Формула

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

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

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

Формула

Live Result (фамилия)

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

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

  1. Имя

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

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

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

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

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

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

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

  4. От имени

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

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

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

    Используйте вложенные функции ПОИСК, чтобы найти значение для start_num:

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

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

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

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

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

    Поищите позицию знака после второго пробела (D). Результатом будет номер знака, с которого вы хотите начать поиск третьего пробела. (10)

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

  9. Поищите позицию знака после второго пробела (D). Результат — начальная позиция от имени. (10)

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

  11. Фамилия

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

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

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

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

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

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

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

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

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

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

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

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

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

Имя примера

Описание

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

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

Формула

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

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

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

Формула

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

ПРАВБ(A2;LEN(A2)-ПОИСК(" ";A2;1))

=ПРАВБ(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Имя примера

Описание

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

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

Формула

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

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

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

Формула

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

'=ПРАВАЯ(A2;LEN(A2)-SEARCH(" ";A2;1))

=ПРАВБ(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Имя примера

Описание

Бейон мл., Дэн К.

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

Формула

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

'=MID(A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)+1;ПОИСК(" ";A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)+1)-SEARCH(" ";A2;ПОИСК(" ";A2;1)+1))

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

    Используйте вложенные функции ПОИСК, чтобы найти значение для start_num:

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

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

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

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

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

    Поищите позицию знака после второго пробела (D). Результатом будет номер знака, с которого вы хотите начать поиск третьего пробела. (12)

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

  6. Поищите позицию знака после второго пробела (D). Результат — начальная позиция имени. (12)

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

  8. От имени

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

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

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

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

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

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

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

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

  14. Фамилия

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

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

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

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

  15. Суффикс

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

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

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

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

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

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

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

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

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

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

  20. Поищите позицию первого пробела в числе. (6)

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

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

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

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

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

Имя примера

Описание

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

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

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

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

  6. Поищите позицию первого пробела в числе. (5)

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

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

  9. Суффикс

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

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

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

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

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

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

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

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

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

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

Имя примера

Описание

Г-н Артем Ихигов

С префиксом

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

    Чтобы найти значение для запроса, воспользуйтесь функцией START_NUM.

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

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

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

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

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

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

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

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

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

  8. Фамилия

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

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

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

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

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

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

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

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

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

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

Имя примера

Описание

Джулия Taft-Rider

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

Формула

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

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

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

Формула

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

'=ПРАВАЯ(A2;LEN(A2)-SEARCH(" ";A2;1))

=ПРАВБ(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Имя

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

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

    Функция ПОИСК используется для поиска значения num_chars:

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

  2. Фамилия

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

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

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

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

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

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

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

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

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

Насколько вы удовлетворены качеством перевода?

Что повлияло на вашу оценку?

Добавите что-нибудь? Это необязательно

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

×