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