Для управления текстовыми строками данных можно использовать текстовые функции LEFT, MID, RIGHT, SEARCH и LEN. Например, можно распределить имя, отчество и фамилию из одной ячейки в три отдельных столбца.
Ключом к распределению компонентов имен с текстовыми функциями является положение каждого символа в текстовой строке. Позиции пробелов в текстовой строке также важны, поскольку они указывают начало или конец компонентов name в строке.
Например, в ячейке, содержащей только имя и фамилию, фамилия начинается после первого вхождения пробела. Некоторые имена в списке могут содержать отчество, и в этом случае фамилия начинается после второго вхождения пробела.
В этой статье показано, как извлекать компоненты из имен разных форматов с помощью этих удобных функций. Мастер распределения текста по столбцам также позволяет разделить текст на столбцы
| Пример имени | Описание | Имя | Отчество | Фамилия | Суффикс | |
|---|---|---|---|---|---|---|
| 1 | Джефф Смит | Нет отчества | Евгений | Климов | ||
| 2 | Эрик С. Курьян | Один средний инициал | Эрик (Eric) | S. | Курджан | |
| 3 | Джанайна Б. Г. Буэно | Два средних инициала | Джанайна | Б. Ж. | Буэно | |
| 4 | Кан, Венди Бет | Фамилия в начате, через запятую | Венди | Бет | Кан | |
| 5 | Мэри Кэй Д. Андерсен | Имя из двух частей | Mary Kay® | Г. | Волкова | |
| 6 | Паула Баррето де Маттос | Фамилия из трех частей | Паула (Paula) | Баррето де Маттос | ||
| 7 | Джеймс ван Итон | Фамилия, состоящая из двух частей | Илья | ван Итон | ||
| 8 | Бэкон-младший, Дэн К. | Фамилия и суффикс после запятой | Дэн | Л. | Бекон | Младший |
| 9 | Гэри Альтман III | С суффиксом | Гэри | Альтман | III | |
| 10 | Г-н Райан Ириг | С префиксом | Райан (Ryan) | Ириг | ||
| 11 | Джули Тафт-Райдер | Фамилия через дефис | Покровская | Taft-Rider |
Примечание
На рисунке в приведенных ниже примерах выделение в полном имени показывает символ, который ищет соответствующая формула ПОИСК.
Пример 1. Разделение Джеффа и Смита
В этом примере разделены два компонента: имя и фамилия. Названия разделены одним пробелом.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Джефф Смит | Нет отчества |
| Формула | Результат (имя) |
| '=ЛЕВЫЙ(A2; ПОИСК(" ";A2;1)) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1)) |
| Формула | Результат (фамилия) |
| '=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) | =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) |
Имя
Имя начинается с первого знака в строке (Ю) и заканчивается пятым знаком (пробелом). Формула возвращает пять знаков в ячейке A2, начиная слева.
Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.
Найдите позицию пробела в A2, начиная слева.Фамилия
Фамилия отделена от имени пробелом, начинается с пятого знака справа и заканчивается первым знаком справа (а). Формула извлекает в A2 пять знаков справа.
Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК и ДЛСТР.
Найдите позицию пробела в A2, начиная слева. (5)Подсчитайте общую длину текстовой строки, а затем вычтите количество знаков слева до первого пробела, найденное в пункте 1.
Пример 2. Эрик С. Курьян: извлечь имя и фамилию, а также инициалы отчества
В этом примере используются имя, отчество, инициал и фамилия. Компоненты имен разделяются пробелом.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Эрик С. Курьян | Один средний инициал |
| Формула | Результат (имя) |
| '=ЛЕВЫЙ(A2; ПОИСК(" ";A2;1)) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1)) |
| Формула | Результат (средний инициал) |
| '=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1)) | =MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1)) |
| Формула | Оперативный результат (фамилия) |
| '=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)) | =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)) |
Имя
Имя начинается с первого символа слева (E) и заканчивается пятым символом (первый пробел). Формула извлекает первые пять символов в ячейке A2, начиная слева.
Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.
Найдите позицию пробела в A2, начиная слева. (5)Отчество
Отческое имя начинается с шестой позиции (S) и заканчивается восьмой (второй пробел). Эта формула включает вложение функций SEARCH для поиска второго экземпляра пробела.
Формула извлекает три знака, начиная с шестой позиции.
Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (5).Прибавьте 1, чтобы получить положение знака после первого пробела (S). Эта числовая позиция является начальной позицией отчества. (5 + 1 = 6)
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (5)Прибавьте 1, чтобы получить положение знака после первого пробела (S). Результат — это номер символа, с которого нужно начать поиск второго экземпляра пробела. (5 + 1 = 6)
Найдите второй пробел в ячейке A2, начиная с шестой позиции (S), обнаруженной на шаге 4. Этот номер является конечной позицией отчества. (8)
Найдите цифровую позицию пробела в ячейке A2, начиная с первого знака слева. (5)
Возьмите номер знака второго пробела, найденного в действии 5, и вычтите номер знака из первого пробела, найденного в действии 6. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (8 – 5 = 3)
Фамилия
Фамилия начинается на шесть знаков справа (K) и заканчивается первым символом справа (n). Эта формула включает вложение функций SEARCH для поиска второго и третьего вхождений пробела (которые находятся в пятом и восьмом местах слева).
Формула извлекает шесть знаков в ячейке А2, начиная справа.
Используйте функции ДЛСТР и вложенный ПОИСК, чтобы найти значение num_chars:
Найдите цифровую позицию пробела в ячейке A2, начиная с первого знака слева. (5)Прибавьте 1, чтобы получить положение знака после первого пробела (S). Результат — это номер символа, с которого нужно начать поиск второго экземпляра пробела. (5 + 1 = 6)
Найдите второй пробел в ячейке A2, начиная с шестой позиции (S), обнаруженной на шаге 2. Этот номер является конечной позицией отчества. (8)
Подсчитайте общую длину текстовой строки в ячейке A2 и вычтите количество знаков слева до второго пробела, найденного в действии 3. Результатом является количество символов, которые нужно извлечь справа от полного имени. (14 – 8 = 6).
Пример 3: Джанайна Б. Г. Буэно
Вот пример извлечения двух средних инициалов. Компоненты имен разделяются первым и третьим вхождениями пробела.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а 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)) |
Имя
Имя начинается с первого символа слева (J) и заканчивается восьмым символом (первый пробел). Формула извлекает первые восемь знаков в ячейке А2, начиная слева.
Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (8)Отчество
Второе имя начинается с девятой позиции (В) и заканчивается четырнадцатой (третий пробел). Эта формула включает вложение функции ПОИСК, чтобы найти первый, второй и третий экземпляры пробела в восьмой, одиннадцатой и четырнадцатой позициях.
Формула извлекает пять знаков, начиная с девятой позиции.
Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (8)Прибавьте 1, чтобы получить положение знака после первого пробела (B). Эта числовая позиция является начальной позицией отчества. (8 + 1 = 9)
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (8)Прибавьте 1, чтобы получить положение знака после первого пробела (B). Результат — это номер символа, с которого нужно начать поиск второго экземпляра пробела. (8 + 1 = 9)
Найдите второй пробел в ячейке A2, начиная с девятой позиции (B), обнаруженной в шаге 4. (11).
Прибавьте 1, чтобы получить положение знака после второго пробела (G). Этот номер символа является начальной позицией, с которой требуется начать поиск третьего пробела. (11 + 1 = 12)
Найдите третий пробел в ячейке A2, начиная с двенадцатой позиции, обнаруженной в действии 6. (14)
Найдите цифровую позицию первого пробела в ячейке A2. (8)
Возьмите номер знака третьего пробела, найденного в действии 7, и вычтите номер знака из первого пробела, найденного в шаге 6. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с девятой позиции, найденной на шаге 2.
Фамилия
Фамилия начинается на пять знаков справа (B) и заканчивается первым символом справа (o). Эта формула включает вложение ПОИСК, чтобы найти первый, второй и третий экземпляры пробела.
Формула извлекает пять знаков в ячейке А2, начиная справа от полного имени.
Используйте вложенный ПОИСК и функции ДЛСТР для поиска значения num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (8)Прибавьте 1, чтобы получить положение знака после первого пробела (B). Результат — это номер символа, с которого нужно начать поиск второго экземпляра пробела. (8 + 1 = 9)
Найдите второй пробел в ячейке A2, начиная с девятой позиции (B), обнаруженной в шаге 2. (11)
Прибавьте 1, чтобы получить положение знака после второго пробела (G). Этот номер символа является начальной позицией, с которой требуется начать поиск третьего экземпляра пробела. (11 + 1 = 12)
Найдите третий пробел в ячейке A2, начиная с двенадцатой позиции (G), найденной в шаге 6. (14)
Подсчитайте общую длину текстовой строки в ячейке А2 и вычтите количество знаков слева до третьего пробела, найденного в действии 5. Результатом является количество символов, которые нужно извлечь справа от полного имени. (19 - 14 = 5)
Пример 4. Кан, Венди Бет
В этом примере фамилия стоит перед именем и отчеством. Конец фамилии обозначается запятой, а все компоненты имен разделяются пробелом.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Кан, Венди Бет | Фамилия в начате, через запятую |
| Формула | Результат (имя) |
| '=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1)) | =MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1)) |
| Формула | Результат (отчество) |
| '=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)) | =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)) |
| Формула | Оперативный результат (фамилия) |
| '=ЛЕВЫЙ(A2; ПОИСК(" ";A2;1)-2) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1)-2) |
Имя
Имя начинается с седьмого знака слева (В) и заканчивается двенадцатым (второй пробел). Поскольку имя оказывается в середине полного имени, для его извлечения необходимо воспользоваться функцией ПСТР.
Формула извлекает шесть знаков, начиная с седьмой позиции.
Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (6)Прибавьте 1, чтобы получить положение знака после первого пробела (клавиша W). Эта числовая позиция является начальной позицией имени. (6 + 1 = 7)
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (6)Прибавьте 1, чтобы получить положение знака после первого пробела (клавиша W). Результат — это номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)
Найдите второй пробел в ячейке A2, начиная с седьмой позиции (W), обнаруженной в шаге 4. (12)Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (6)
Возьмите номер знака второго пробела, найденного в действии 5, и вычтите номер знака из первого пробела, найденного в действии 6. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с седьмой позиции, найденной на шаге 2. (12 - 6 = 6)
Отчество
Отчество начинается с четвертого знака справа (Б) и заканчивается первым знаком справа (т). Для поиска первого и второго пробелов на шестой и двенадцатой позициях слева эта формула включает вложенную функцию ПОИСК.
Формула извлекает четыре знака, начиная справа.
Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК и ДЛСТР:
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (6)Прибавьте 1, чтобы получить положение знака после первого пробела (клавиша W). Результат — это номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)
Найдите второй пробел в ячейке A2, начиная с седьмой позиции (W), обнаруженной в шаге 2. (12)
Посчитайте общую длину текстовой строки в ячейке A2 и вычтите количество знаков слева до второго пробела, найденного в действии 3. Результатом является количество символов, которые нужно извлечь справа от полного имени. (16 - 12 = 4)
Фамилия
Фамилия начинается с первого знака слева (К) и заканчивается четвертым знаком (н). Эта формула извлекает четыре знака, начиная слева.
Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (6)Вычтите 2, чтобы получить цифровую позицию конечного знака фамилии (n). В результате будет найдено количество символов, извлекаемых функцией ЛЕВСИМВ. (6 - 2 =4)
Пример 5. Мэри Кэй Д. Андерсен
В этом примере используется имя Мэри Кэй, состоящее из двух частей. Второй и третий пробелы разделяют каждый компонент имени.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Мэри Кэй Д. Андерсен | Имя из двух частей |
| Формула | Результат (имя) |
| ЛЕВСИМВ(A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)) |
| Формула | Результат (средний инициал) |
| '=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)) | =MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)+1)-(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)) |
| Формула | Оперативный результат (фамилия) |
| '=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)+1)) | =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)+1)) |
Имя
Имя начинается с первого знака слева и заканчивается девятым символом (вторым пробелом). Эта формула включает вложение ПОИСК, чтобы найти второй экземпляр пробела слева.
Формула получает девять знаков, начиная слева.
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (5)Прибавьте 1, чтобы получить положение знака после первого пробела (K). Результат — это номер символа, с которого нужно начать поиск второго экземпляра пробела. (5 + 1 = 6)
Найдите второй пробел в ячейке A2, начиная с шестой позиции (K), обнаруженной в шаге 2. В результате количество символов, извлекаемых функцией ЛЕВСИМВ из текстовой строки. (9)
Отчество
Второе имя начинается на десятой позиции (D) и заканчивается на двенадцатой позиции (третий пробел). Эта формула включает вложение ПОИСК, чтобы найти первый, второй и третий экземпляры пробела.
Формула извлекает два знака из середины, начиная с десятой позиции.
Используйте вложенные функции ПОИСК, чтобы найти значение start_num:
Найдите цифровую позицию первого пробела в ячейке A2, начиная с первого знака слева. (5)Прибавьте 1, чтобы получить символ после первого пробела (K). Результат — это номер знака, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
Найдите позицию второго экземпляра пробела в ячейке A2, начиная с шестой позиции (K), обнаруженной на шаге 2. В результате количество символов, извлекаемых функцией ЛЕВСИМВ из функции ЛЕВСИМВ. (9)
Прибавьте 1, чтобы получить символ после второго пробела (D). Результатом является начальная позиция отчества. (9 + 1 = 10)
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Выполните поиск цифровой позиции знака после второго пробела (D). Результат — это номер знака, с которого нужно начать поиск третьего пробела. (10)Найдите цифровую позицию третьего пробела в ячейке A2, начиная слева. Результатом является конечная позиция отчества. (12)
Выполните поиск цифровой позиции знака после второго пробела (D). Результатом является начальная позиция отчества. (10)
Возьмите номер знака третьего пробела, найденный в шаге 6, и вычтите номер знака "D", найденный в шаге 7. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с десятой позиции, найденной на шаге 4. (12 - 10 = 2)
Фамилия
Фамилия начинается с восьми знаков справа. Эта формула включает вложение ПОИСК, чтобы найти первый, второй и третий экземпляры пробела в пятой, девятой и двенадцатой позициях.
Формула извлекает восемь знаков справа.
Используйте вложенный ПОИСК и функции ДЛСТР для поиска значения num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (5)Прибавьте 1, чтобы получить символ после первого пробела (K). Результат — это номер символа, с которого нужно начать поиск пространства. (5 + 1 = 6)
Найдите второй пробел в ячейке A2, начиная с шестой позиции (K), обнаруженной в шаге 2. (9)
Прибавьте 1, чтобы получить положение знака после второго пробела (D). Результатом является начальная позиция отчества. (9 + 1 = 10)
Найдите цифровую позицию третьего пробела в ячейке A2, начиная слева. Результатом является конечная позиция отчества. (12)
Подсчитайте общую длину текстовой строки в ячейке А2 и вычтите количество знаков слева до третьего пробела, найденного в действии 5. Результатом является количество символов, которые нужно извлечь справа от полного имени. (20 - 12 = 8)
Пример 6: Паула Баррето де Маттос
В этом примере используется фамилия, состоящая из трех частей: Баррето де Маттос. Первым пробелом обозначают конец имени и начало фамилии.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Паула Баррето де Маттос | Фамилия из трех частей |
| Формула | Результат (имя) |
| '=ЛЕВЫЙ(A2; ПОИСК(" ";A2;1)) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1)) |
| Формула | Результат (фамилия) |
| ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) | =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) |
Имя
Имя начинается с первого символа слева (P) и заканчивается шестым символом (первый пробел). Формула извлекает шесть знаков слева.
Чтобы найти значение num_chars, используйте функцию поиска:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Фамилия
Фамилия начинается с семнадцати знаков справа (B) и заканчивается первым символом справа (s). Формула извлекает семнадцать знаков справа.
Используйте функции ДЛСТР и ПОИСК, чтобы найти значение num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Подсчитайте общую длину текстовой строки в ячейке A2 и вычтите количество символов слева до первого пробела, найденного в действии 1. Результатом является количество символов, которые нужно извлечь справа от полного имени. (23 - 6 = 17)
Пример 7. Джеймс ван Итон
В этом примере используется фамилия из двух частей: van Eaton. Первым пробелом обозначают конец имени и начало фамилии.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Джеймс ван Итон | Фамилия, состоящая из двух частей |
| Формула | Результат (имя) |
| '=ЛЕВЫЙ(A2; ПОИСК(" ";A2;1)) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1)) |
| Формула | Результат (фамилия) |
| '=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) | =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) |
Имя
Имя начинается с первого символа слева (J) и заканчивается восьмым символом (первый пробел). Формула извлекает шесть знаков слева.
Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Фамилия
Фамилия начинается с девятого знака справа (v) и заканчивается первым символом справа (n). Формула извлекает девять знаков справа от полного имени.
Используйте функции ДЛСТР и ПОИСК, чтобы найти значение num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Подсчитайте общую длину текстовой строки в ячейке A2 и вычтите количество символов слева до первого пробела, найденного в действии 1. Результатом является количество символов, которые нужно извлечь справа от полного имени. (15 - 6 = 9)
Пример 8: Бэкон-младший, Дэн К.
В этом примере сначала указывается фамилия, а затем суффикс. Фамилия и суффикс отделены от имени и среднего инициала.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Бэкон-младший, Дэн К. | Фамилия и суффикс после запятой |
| Формула | Результат (имя) |
| '=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1)) | =ПСТР(A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)+1;ПОИСК(" ";A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)+1)-ПОИСК(" ";A2;ПОИСК(" ";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)) |
| Формула | Результат (суффикс) |
| '=MID(A2;SEARCH(" ";A2;1)+1;(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-2)-SEARCH(" ";A2;1)) | =ПСТР(A2;ПОИСК(" ";A2;1)+1;(ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)-2)-ПОИСК(" ";A2;1)) |
Имя
Имя начинается с двенадцатого знака (Г) и заканчивается пятнадцатым символом (третий пробел). Формула получает три знака, начиная с двенадцатой позиции.
Используйте вложенные функции ПОИСК, чтобы найти значение start_num:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Прибавьте 1, чтобы получить символ после первого пробела (J). Результат — это номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)
Найдите второй пробел в ячейке A2, начиная с седьмой позиции (J), обнаруженной в шаге 2. (11)
Прибавьте 1, чтобы получить символ после второго пробела (D). Результатом является начальная позиция имени. (11 + 1 = 12)
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Выполните поиск цифровой позиции знака после второго пробела (D). Результат — это номер знака, с которого нужно начать поиск третьего пробела. (12)Найдите цифровую позицию третьего пробела в ячейке A2, начиная слева. Результатом является конечная позиция имени. (15)
Выполните поиск цифровой позиции знака после второго пробела (D). Результатом является начальная позиция имени. (12)
Возьмите номер знака третьего пробела, найденный в шаге 6, и вычтите номер знака "D", найденный в шаге 7. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с двенадцатой позиции, найденной на шаге 4. (15 - 12 = 3)
Отчество
Второе имя начинается со второго знака справа (K). Формула извлекает два знака справа.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)
Прибавьте 1, чтобы получить символ после первого пробела (J). Результат — это номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)
Найдите второй пробел в ячейке A2, начиная с седьмой позиции (J), обнаруженной в шаге 2. (11)
Прибавьте 1, чтобы получить символ после второго пробела (D). Результатом является начальная позиция имени. (11 + 1 = 12)
Найдите цифровую позицию третьего пробела в ячейке A2, начиная слева. Результатом является конечная позиция отчества. (15)
Подсчитайте общую длину текстовой строки в ячейке А2 и вычтите количество знаков слева до третьего пробела, найденного в действии 5. Результатом является количество символов, которые нужно извлечь справа от полного имени. (17 - 15 = 2)
Фамилия
Фамилия начинается с первого знака слева (B) и заканчивается шестым (первый пробел). Поэтому формула извлекает шесть знаков слева.
Для поиска значения "число_знаков" следует воспользоваться функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Суффикс
Суффикс начинается с седьмого знака слева (J) и заканчивается девятым символом слева (.). Формула извлекает три символа, начиная с седьмого символа.
Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Прибавьте 1, чтобы получить символ после первого пробела (J). Результатом является начальная позиция суффикса. (6 + 1 = 7)
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Прибавьте 1, чтобы получить цифровую позицию знака после первого пробела (J). Результат — это номер знака, с которого нужно начать поиск второго пробела. (7)
Найдите цифровую позицию второго пробела в ячейке A2, начиная с седьмого знака, найденного в шаге 4. (11)
Вычтите 1 из номера знака второго пробела, найденного в действии 4, чтобы получить номер символа ",". Результатом является конечная позиция суффикса. (11 - 1 = 10)
Найдите цифровую позицию первого пробела. (6)
Найдя первый пробел, добавьте 1, чтобы найти следующий символ (J), который также находится в шагах 3 и 4. (7)
Возьмите номер символа ",", найденный в шаге 6, и вычтите номер символа "J", найденный в шагах 3 и 4. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с седьмой позиции, найденной на шаге 2. (10 - 7 = 3)
Пример 9. Гэри Альтман III
В этом примере имя стоит в начале строки, а суффикс — в конце, поэтому можно использовать формулы, аналогичные формуле из примера 2: используйте функцию ЛЕВСИМВ для извлечения имени, функцию ПСТР для извлечения фамилии и функцию ПРАВСИМВ для извлечения суффикса.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Гэри Альтман III | Имя и фамилия с суффиксом |
| Формула | Результат (имя) |
| '=ЛЕВЫЙ(A2; ПОИСК(" ";A2;1)) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1)) |
| Формула | Результат (фамилия) |
| '=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ";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)) |
Имя
Имя начинается с первого символа слева (G) и заканчивается пятым символом (первый пробел). Поэтому формула извлекает пять знаков слева от полного имени.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (5)
Фамилия
Фамилия начинается с шестого знака слева (А) и заканчивается одиннадцатым (вторым пробелом). Эта формула включает вложение ПОИСК, чтобы найти положение пробелов.
Формула извлекает шесть символов из середины, начиная с шестого.
Для поиска значения "начальная_позиция" следует воспользоваться функцией ПОИСК:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (5)Прибавьте 1, чтобы получить положение знака после первого пробела (A). Результатом является начальная позиция фамилии. (5 + 1 = 6)
Для поиска значения "число_знаков" следует воспользоваться вложенной функцией ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (5)Прибавьте 1, чтобы получить положение знака после первого пробела (A). Результат — это номер знака, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
Найдите цифровую позицию второго пробела в ячейке A2, начиная с шестого знака, найденного в шаге 4. Этот номер является конечной позицией фамилии. (12)
Найдите цифровую позицию первого пробела. (5)
Прибавьте 1, чтобы найти цифровую позицию знака после первого пробела (A), который также находится в шагах 3 и 4. (6)
Возьмите номер знака второго пробела, найденный в действии 5, и вычтите номер знака "А", найденный в шагах 6 и 7. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (12 - 6 = 6)
Суффикс
Суффикс начинается на три буквы справа. Эта формула включает вложение ПОИСК, чтобы найти положение пробелов.
Используйте вложенный ПОИСК и функции ДЛСТР для поиска значения num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (5)Прибавьте 1, чтобы получить символ после первого пробела (A). Результат — это номер знака, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
Найдите второй пробел в ячейке A2, начиная с шестой позиции (A), обнаруженной в шаге 2. (12)
Подсчитайте общую длину текстовой строки в ячейке A2 и вычтите количество символов слева до второго пробела, найденного в действии 3. Результатом является количество символов, которые нужно извлечь справа от полного имени. (15 - 12 = 3)
Пример 10: г-н Райан Ириг
В этом примере полному имени предшествует префикс, и используются формулы, аналогичные примеру 2: функция ПСТР — для извлечения имени, функция ПРАВСИМВ — для извлечения фамилии.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Г-н Райан Ириг | С префиксом |
| Формула | Результат (имя) |
| '=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ";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)) |
Имя
Имя начинается с пятого знака слева (R) и заканчивается девятым символом (второй пробел). Формула вкладывает ПОИСК, чтобы найти позиции пробелов. Извлекает четыре символа, начиная с пятой позиции.
Чтобы найти значение start_num, используйте функцию ПОИСК.
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (4)Прибавьте 1, чтобы получить положение знака после первого пробела (R). Результатом является начальная позиция имени. (4 + 1 = 5)
Используйте вложенную функцию ПОИСК, чтобы найти значение num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (4)Прибавьте 1, чтобы получить положение знака после первого пробела (R). Результат — это номер знака, с которого нужно начать поиск второго пробела. (4 + 1 = 5)
Выполните поиск цифровой позиции второго пробела в ячейке A2, начиная с пятого знака, в шагах 3 и 4. Этот номер символа является конечной позицией имени. (9)
Поиск первого пробела. (4)
Прибавьте 1, чтобы найти цифровую позицию знака после первого пробела (R), которая также находится в шагах 3 и 4. (5)
Возьмите номер знака второго пробела, найденный в действии 5, и вычтите номер знака "R", найденный в шагах 6 и 7. Результатом является количество знаков, извлекаемых функцией ПСТР из текстовой строки, начиная с пятой позиции, найденной на шаге 2. (9 - 5 = 4)
Фамилия
Фамилия начинается на пять знаков справа. Эта формула включает вложение ПОИСК, чтобы найти положение пробелов.
Используйте вложенный ПОИСК и функции ДЛСТР для поиска значения num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (4)Прибавьте 1, чтобы получить положение знака после первого пробела (R). Результат — это номер знака, с которого нужно начать поиск второго пробела. (4 + 1 = 5)
Найдите второй пробел в ячейке A2, начиная с пятой позиции (R), найденной в шаге 2. (9)
Подсчитайте общую длину текстовой строки в ячейке A2 и вычтите количество символов слева до второго пробела, найденного в действии 3. Результатом является количество символов, которые нужно извлечь справа от полного имени. (14 - 9 = 5)
Пример 11. Джулия Taft-Rider
В этом примере фамилия указывается через дефис. Компоненты имен разделяются пробелом.
Скопируйте ячейки из таблицы и вставьте их в ячейку A1 на листе Excel. Формула, отображаемая слева, будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Подсказка Перед вставкой данных на лист задайте ширину столбцов A и B равной 250.
| Пример имени | Описание |
|---|---|
| Джули Taft-Rider | Фамилия через дефис |
| Формула | Результат (имя) |
| '=ЛЕВЫЙ(A2; ПОИСК(" ";A2;1)) | =ЛЕВСИМВ(A2; ПОИСК(" ";A2;1)) |
| Формула | Результат (фамилия) |
| '=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) | =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1)) |
Имя
Имя начинается с первого символа слева и заканчивается шестой позицией (первым пробелом). Формула извлекает шесть знаков слева.
Используйте функцию ПОИСК, чтобы найти значение num_chars:
Найдите цифровую позицию первого пробела в ячейке A2, начиная слева. (6)Фамилия
Название фамилии целиком начинается на десять знаков справа (T) и заканчивается первым символом справа (r).
Используйте функции ДЛСТР и ПОИСК, чтобы найти значение num_chars:
Найдите цифровую позицию пробела в ячейке A2, начиная с первого знака слева. (6)Подсчитайте общую длину текстовой строки, которую нужно извлечь, а затем вычтите количество символов слева до первого пробела, найденного в действии 1. (16 - 6 = 10)
См. также
Разделение текста на столбцы с помощью мастера распределения текста по столбцам