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

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

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

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

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

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

Пример имени

Описание

Имя

Отчество

Фамилия

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

1

Джефф Смит

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

Евгений

Климов

2

От а до Куржан

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

Эрик

Месяца.

куржан

3

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

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

жанаина

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

буено

4

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

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

Ольга

Бетт

Канн

5

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

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

Мария Кай

Област.

Волкова

6

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

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

Паула

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

7

Джеймс Van Итон

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

Петр

Van Итон

8

Бэкон мл.

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

Дэн

Звонить.

Бэкон

Мл.

9

Гэри Алтман III

С суффиксом

Гэри

Алтман

3

10

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

С префиксом

Райан

ихриг

11

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

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

Покровская

Тафт-Редер

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

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

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

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

Пример имени

Описание

Джефф Смит

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

От а до Куржан

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Отчество

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

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

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

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

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

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

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

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

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

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

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

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

  8. Фамилия

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

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

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

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

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

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Отчество

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  10. Фамилия

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

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

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

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

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

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

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

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

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

  6. Отчество

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

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

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

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

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

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

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

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

  10. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

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

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

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

  4. Отчество

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

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

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

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

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

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

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

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

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

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

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

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

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

  11. Фамилия

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

Джеймс Van Итон

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

Пример имени

Описание

Бэкон мл.

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

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

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

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

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

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

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

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

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

  8. Отчество

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

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

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

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

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

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

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

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

  14. Фамилия

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример имени

Описание

Гэри Алтман III

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

Формула

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

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

  2. Фамилия

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример имени

Описание

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

С префиксом

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

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

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

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

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

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

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

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

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

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

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

  8. Фамилия

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

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

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

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

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

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

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

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

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

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

Пример имени

Описание

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

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

Формула

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

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

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

Формула

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

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

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

  1. Имя

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

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

    Используйте функцию Поиск, чтобы найти значение num_chars.

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

  2. Фамилия

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

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

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

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

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

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

См. также

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

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

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

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

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

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

×