Розділення тексту на кілька стовпців за допомогою функцій
Applies ToExcel для Microsoft 365 Excel для Microsoft 365 для Mac Вебпрограма Excel Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel Web App

За допомогою текстових функцій LEFT, MID, RIGHT, SEARCH і LEN можна керувати рядками тексту в даних. Наприклад, можна розподілити ім'я, середину та прізвища з однієї клітинки на три окремі стовпці.

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

Наприклад, у клітинці, яка містить лише ім'я та прізвище, прізвище починається після першого екземпляра пробілу. Деякі імена в списку можуть містити по батькові, у такому разі прізвище починається після другого екземпляра пробілу.

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

Приклад імені

Опис

Ім’я

По батькові

Прізвище

суфікс

1

Джефф Сміт

Немає імені по батькові

Валерій

Коваль

2

Ерік С. Куржан

Один середній початковий

Ерік

S.

Куржан (Курсжан)

3

Янаіна Б. Г. Буено

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

Janaina

Б. Е.

Буено

4

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

Прізвище з комою

Ольга

Бет

Кан

5

Мері Кей Д. Андерсен

Ім'я двох частин

Мері Кей

Г.

Andersen

6

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

Прізвище з трьох частин

Паула

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

7

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

Прізвище із двома частинами

Джеймс

ван Ітон

8

Бекон молодший, Ден К.

Прізвище та суфікс спочатку з комою

Ден

К.

Бекон

Молодший

9

Гері Альтман III

З суфіксом

Гері

Альтман

III (iii)

10

Пан Райан Іріг

З префіксом

Райан

Ihrig (Ihrig)

11

Джулі Taft-Rider

Прізвище з переносами

Юлія

Taft-Rider

Примітка.: У наведених нижче прикладах під час виділення в повному імені відображається символ, який шукає відповідна формула SEARCH.

У цьому прикладі розділено два компоненти: ім'я та прізвище. Один пробіл відокремлює два імена.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Джефф Сміт

Немає імені по батькові

Формула

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

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Результат (прізвище)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Ім’я

    Ім’я починається з першого символу в рядку (J) і закінчується п’ятим символом (пробілом). Формула повертає п’ять символів у клітинці А2, починаючи зліва.

    Формула для видобування імені

    Скористайтеся функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення пробілу в A2, починаючи зліва.

  2. Прізвище

    Прізвище починається з пробілу, п’ять символів справа, і закінчується символом справа (h). Формула видобуває п’ять символів у клітинці А2, починаючи справа.

    Формула для видобування прізвища

    Скористайтеся функцією SEARCH і LEN для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення пробілу в A2, починаючи зліва. (5)

  3. Підрахуйте загальну довжину текстового рядка, а потім відніміть кількість символів зліва від першого пробілу, знайдену на 1-му кроці.

У цьому прикладі використовуються ім'я, по батькові та прізвище. Пробіл відокремлює кожен компонент імені.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Ерік С. Куржан

Один середній початковий

Формула

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

'=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))

  1. Ім’я

    Ім'я починається з першого символу зліва (E) і закінчується п'ятим символом (перший пробіл). Формула видобуває перші п'ять символів у клітинках A2, починаючи зліва.

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

    Скористайтеся функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення пробілу в A2, починаючи зліва. (5)

  2. По батькові

    По батькові ім'я починається з шостої позиції символу (S) і закінчується на восьмій позиції (другий пробіл). Ця формула передбачає вкладення функцій SEARCH для пошуку другого екземпляра пробілу.

    Формула видобуває три символи, починаючи з шостої позиції.

    Подробиці формули для відокремлення імені, середнього імені та прізвища

    Скористайтеся функцією SEARCH для пошуку значення для змінної start_num:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (5).

  3. Додайте 1, щоб отримати положення символу після першого пробілу (S). Це числове розташування – початкове положення середнього імені. (5 + 1 = 6)

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (5)

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

  5. Знайдіть другий екземпляр пробілу в клітинці A2, починаючи з шостого положення (S), знайденого на кроці 4. Це номер символу є кінцевою позицією середнього імені. (8)

  6. Знайдіть числове положення пробілу в A2, починаючи з першого символу зліва. (5)

  7. Візьміть номер символу, що відповідає другому пробілу, знайденому на кроці 5, і відніміть від нього номер символу, що відповідає першому пробілу, знайденому на кроці 6. Результатом буде кількість символів, які функція MID видобуває з текстового рядка, починаючи з шостого положення, знайденого на кроці 2. (8 – 5 = 3)

  8. Прізвище

    Прізвище починається з шести символів справа (K) і закінчується першим символом справа (n). Ця формула передбачає вкладення функцій SEARCH для пошуку другого та третього екземплярів пробілу (які розташовані на п'ятій і восьмій позиціях зліва).

    Формула видобуває шість символів у клітинках A2, починаючи справа.

    Друга функція SEARCH у формулі для відокремлення імені, середнього й прізвища
  9. Використовуйте функції LEN і вкладені функції SEARCH, щоб знайти значення для num_chars:

    Знайдіть числове положення пробілу в A2, починаючи з першого символу зліва. (5)

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

  11. Знайдіть другий екземпляр пробілу в клітинці A2, починаючи з шостого положення (S), знайденого на кроці 2. Це номер символу є кінцевою позицією середнього імені. (8)

  12. Підрахуйте загальну довжину текстового рядка в клітинці A2, а потім відніміть кількість символів зліва до другого екземпляра пробілу, знайденого на кроці 3. Результатом буде кількість символів, які видобуватимуться справа від повного імені. (14 – 8 = 6).

Ось приклад того, як видобути два середні ініціали. Перший і третій екземпляри простору відокремлюють компоненти імен.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Янаіна Б. Г. Буено

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

Формула

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

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

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

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1))

=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;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))

  1. Ім’я

    Ім'я починається з першого символу зліва (J) і закінчується восьмим символом (перший пробіл). Формула видобуває перші вісім символів у клітинках A2, починаючи зліва.

    Формула для відокремлення імені, прізвища та двох ініціалів середнього імені

    Скористайтеся функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (8)

  2. По батькові

    Ім'я по батькові починається з дев'ятої позиції (B) і закінчується чотирнадцятою позицією (третій пробіл). Для цієї формули потрібно вкласти функцію SEARCH, щоб знайти перший, другий і третій екземпляри простору у восьмому, одинадцятому та чотирнадцятому позиціях.

    Формула видобуває п'ять символів, починаючи з дев'ятої позиції.

    Формула для відокремлення імені, прізвища та двох ініціалів середнього імені

    Скористайтеся функцією SEARCH для пошуку значення для змінної start_num:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (8)

  3. Додайте 1, щоб отримати положення символу після першого пробілу (B). Це числове розташування – початкове положення середнього імені. (8 + 1 = 9)

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (8)

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

  5. Знайдіть другий пробіл у клітинці A2, починаючи з дев'ятого положення (B), знайденого на кроці 4. (11).

  6. Додайте 1, щоб отримати положення символу після другого пробілу (G). Цей номер символу – це початкова позиція, з якої потрібно почати пошук третього пробілу. (11 + 1 = 12)

  7. Знайдіть третє місце в клітинці A2, починаючи з дванадцятого положення, знайденого на кроці 6. (14)

  8. Знайдіть числове положення першого пробілу в A2. (8)

  9. Візьміть номер символу третього пробілу, знайденого на кроці 7, і відніміть номер символу першого пробілу, знайденого на кроці 6. Результатом буде кількість символів, які функція MID видобуває з текстового рядка, починаючи з дев'ятої позиції, знайденої на кроці 2.

  10. Прізвище

    Прізвище починається з п'яти символів справа (B) і закінчується першим символом справа (o). Для цієї формули потрібно вкласти функцію SEARCH, щоб знайти перший, другий і третій екземпляри простору.

    Формула видобуває п'ять символів у клітинку A2, починаючи з правого краю повного імені.

    Формула для відокремлення імені, прізвища та двох ініціалів середнього імені

    Щоб знайти значення для num_chars, використовуйте вкладені функції SEARCH і LEN:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (8)

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

  12. Знайдіть другий пробіл у клітинці A2, починаючи з дев'ятого положення (B), знайденого на кроці 2. (11)

  13. Додайте 1, щоб отримати положення символу після другого пробілу (G). Цей номер символу – це початкова позиція, з якої потрібно почати пошук третього екземпляра пробілу. (11 + 1 = 12)

  14. Знайдіть третє місце в клітинці A2, починаючи з дванадцятого положення (G), знайденого на кроці 6. (14)

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

У цьому прикладі прізвище стоїть перед ім’ям, а по батькові – у кінці. Кома позначає кінець прізвища, а пробіл відокремлює кожен компонент імені.

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

Формула

Результат (по батькові)

'=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)

  1. Ім’я

    Ім’я починається із сьомого символу зліва (W) і закінчується дванадцятим символом (другим пробілом). Оскільки ім’я знаходиться в центрі повного імені, для видобування імені слід скористатися функцією MID.

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

    Формула для відокремлення прізвища, після якого вказані ім’я й по-батькові

    Скористайтеся функцією SEARCH для пошуку значення для змінної start_num:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (6)

  2. Додайте 1, щоб отримати положення символу після першого пробілу (W). Цей порядковий номер положення відповідає початковому положенню імені. (6 + 1 = 7)

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (6)

  3. Додавання 1 для отримання положення символу після першого пробілу (W). Результатом є порядковий номер символу, починаючи з якого потрібно почати пошук другого пробілу. (6 + 1 = 7)

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

  4. Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (6)

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

  6. По батькові

    Ім’я по батькові починається з четвертого символу справа (B) і закінчується першим символом справа (h). У цій формулі використовуються вкладені функції SEARCH, які використовуються для пошуку першого та другого екземпляра пробілу в шостому та дванадцятому положеннях зліва.

    Формула видобуває чотири символи, починаючи справа.

    Формула для відокремлення прізвища, після якого вказані ім’я й по-батькові

    Використання вкладених функцій SEARCH і LEN для пошуку значення для змінної start_num:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (6)

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

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

  9. Підрахуйте загальну довжину текстового рядка у клітинці A2, а потім відніміть кількість символів зліва до другого екземпляра пробілу, знайдену на 3-му кроці. Результатом буде кількість символів, які видобуватимуться справа від повного імені. (12 – 16 = 4).

  10. Прізвище

    Прізвище починається з першого символу зліва (K) і закінчується четвертим символом (n). Формула видобуває чотири символів, починаючи зліва.

    Формула для відокремлення прізвища, після якого вказані ім’я й по-батькові

    Скористайтеся функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (6)

  11. Відніміть 2, щоб отримати числове положення кінцевого символу прізвища (n). Результатом буде кількість символів, які має видобути функція LEFT. (6 – 2 = 4)

У цьому прикладі використовується ім'я "Mary Kay" із двома частинами. Другий і третій пробіли відокремлюють кожен компонент імені.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Мері Кей Д. Андерсен

Ім'я двох частин

Формула

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

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)-(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;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))

  1. Ім’я

    Ім'я починається з першого символу зліва та закінчується дев'ятим символом (другим пробілом). Ця формула передбачає вкладення функції SEARCH для пошуку другого екземпляра пробілу зліва.

    Формула видобуває дев'ять символів, починаючи зліва.

    Формула для відокремлення імені, середнього імені, ініціалу середнього імені та прізвища

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (5)

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

  3. Знайдіть другий екземпляр пробілу в клітинці A2, починаючи з шостого положення (K), знайденого на кроці 2. Результатом буде кількість символів, які функція LEFT видобуває з текстового рядка. (9)

  4. По батькові

    Ім'я по батькові починається з десятої позиції (D) і закінчується дванадцятим положенням (третій пробіл). Для цієї формули потрібно вкласти функцію SEARCH, щоб знайти перший, другий і третій екземпляри простору.

    Формула видобуває два символи з середини, починаючи з десятої позиції.

    Формула для відокремлення імені, середнього імені, ініціалу середнього імені та прізвища

    Використовуйте вкладені функції SEARCH, щоб знайти значення для start_num:

    Знайдіть порядковий номер положення першого пробілу в A2, починаючи з першого символу зліва. (5)

  5. Додайте 1, щоб отримати символ після першого пробілу (K). Результатом є порядковий номер символу, починаючи з якого потрібно почати пошук другого пробілу. (5 + 1 = 6)

  6. Знайдіть позицію другого екземпляра пробілу в клітинці A2, починаючи з шостого положення (K), знайденого на кроці 2. Результатом буде кількість символів, які функція LEFT видобуває зліва. (9)

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

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення символу після другого пробілу (D). Результатом буде номер символу, з якого потрібно почати пошук третього пробілу. (10)

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

  9. Знайдіть порядковий номер положення символу після другого пробілу (D). Результат – це початкове положення середнього імені. (10)

  10. Візьміть номер символу третього пробілу, знайденого на кроці 6, і відніміть номер символу "D", знайдений на кроці 7. Результатом буде кількість символів, які функція MID видобуває з текстового рядка, починаючи з десятої позиції, знайденої на кроці 4. (12 - 10 = 2)

  11. Прізвище

    Прізвище починається з восьми символів справа. Для цієї формули потрібно вкласти функцію SEARCH, щоб знайти перший, другий і третій екземпляри простору в п'ятій, дев'ятій і дванадцятій позиціях.

    Формула видобуває з правого краю вісім символів.

    Формула для відокремлення імені, середнього імені, ініціалу середнього імені та прізвища

    Щоб знайти значення для num_chars, використовуйте вкладені функції SEARCH і LEN:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (5)

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

  13. Знайдіть другий пробіл у клітинці A2, починаючи з шостого положення (K), знайденого на кроці 2. (9)

  14. Додайте 1, щоб отримати положення символу після другого пробілу (D). Результат – початкове положення середнього імені. (9 + 1 = 10)

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

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

У цьому прикладі використовується прізвище з трьох частин: Barreto de Mattos. Перший пробіл позначає кінець імені та початок прізвища.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

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

Прізвище з трьох частин

Формула

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

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Результат (прізвище)

RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Ім’я

    Ім'я починається з першого символу зліва (P) і закінчується шостим символом (перший пробіл). Формула видобуває шість символів зліва.

    Формула для відокремлення імені та прізвища з трьох частин

    Скористайтеся функцією Search, щоб знайти значення для num_chars:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

  2. Прізвище

    Прізвище починається сімнадцяти символів справа (B) і закінчується першим символом справа (и). Формула видобуває сімнадцять символів справа.

    Формула для відокремлення імені та прізвища з трьох частин

    Скористайтеся функціями LEN і SEARCH, щоб знайти значення для num_chars.

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

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

У цьому прикладі використовується прізвище з двома частинами: van Eaton. Перший пробіл позначає кінець імені та початок прізвища.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

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

Прізвище із двома частинами

Формула

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

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Результат (прізвище)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Ім’я

    Ім'я починається з першого символу зліва (J) і закінчується восьмим символом (перший пробіл). Формула видобуває шість символів зліва.

    Формула для відокремлення імені та прізвища, яке складається із двох частин

    Скористайтеся функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

  2. Прізвище

    Прізвище починається з дев'ятого символу справа (v) і закінчується першим символом справа (n). Формула видобуває дев'ять символів справа від повного імені.

    Формула для відокремлення імені та прізвища, яке складається із двох частин

    Скористайтеся функціями LEN і SEARCH, щоб знайти значення для num_chars.

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

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

У цьому прикладі прізвище спочатку з'являється, а потім – суфікс. Кома відділяє прізвище та суфікс від імені та середнього імені.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Бекон молодший, Ден К.

Прізвище та суфікс спочатку з комою

Формула

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

'=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;SEARCH(" ;A2;1)+1))

=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;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))

  1. Ім’я

    Ім'я починається дванадцятим символом (D) і закінчується п'ятнадцятим символом (третій пробіл). Формула видобуває три символи, починаючи з дванадцятої позиції.

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

    Використовуйте вкладені функції SEARCH, щоб знайти значення для start_num:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

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

  3. Знайдіть другий пробіл у клітинці A2, починаючи з сьомого положення (J), знайденого на кроці 2. (11)

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

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть порядковий номер положення символу після другого пробілу (D). Результатом буде номер символу, з якого потрібно почати пошук третього пробілу. (12)

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

  6. Знайдіть порядковий номер положення символу після другого пробілу (D). Результат – це початкова позиція імені. (12)

  7. Візьміть номер символу третього пробілу, знайденого на кроці 6, і відніміть номер символу "D", знайдений на кроці 7. Результатом буде кількість символів, які функція MID видобуває з текстового рядка, починаючи з дванадцятого положення, знайденого на кроці 4. (15 - 12 = 3)

  8. По батькові

    Ім'я по батькові починається з другого символу справа (K). Формула видобуває два символи справа.

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

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

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

  10. Знайдіть другий пробіл у клітинці A2, починаючи з сьомого положення (J), знайденого на кроці 2. (11)

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

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

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

  14. Прізвище

    Прізвище починається з першого символу зліва (B) і закінчується шостим символом (перший пробіл). Таким чином, формула видобуває шість символів зліва.

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

    Скористайтеся функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

  15. суфікс

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

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

    Скористайтеся функцією SEARCH для пошуку значення для змінної start_num:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

  16. Додайте 1, щоб отримати символ після першого пробілу (J). Результат – початкова позиція суфікса. (6 + 1 = 7)

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

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

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

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

  20. Знайдіть порядковий номер положення першого пробілу. (6)

  21. Знайшовши перший пробіл, додайте 1, щоб знайти наступний символ (J), також знайдений на кроках 3 та 4. (7)

  22. Візьміть номер символу "", знайдений на кроці 6, і відніміть номер символу "J", знайдений на кроках 3 та 4. Результатом буде кількість символів, які функція MID видобуває з текстового рядка, починаючи із сьомого положення, знайденого на кроці 2. (10 - 7 = 3)

У цьому прикладі ім'я розташовано на початку рядка, а суфікс – у кінці, тому ви можете скористатися формулами, подібними до прикладу 2. Скористайтеся функцією LEFT, щоб видобути ім'я, функцією MID для видобування прізвища та функцією RIGHT для видобування суфікса.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Гері Альтман III

Ім'я та прізвище з суфіксом

Формула

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

'=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))

  1. Ім’я

    Ім'я починається з першого символу зліва (G) і закінчується п'ятим символом (першим пробілом). Таким чином, формула видобуває п'ять символів зліва від повного імені.

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

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (5)

  2. Прізвище

    Прізвище починається з шостого символу зліва (А) і закінчується одинадцятим символом (другим пробілом). Для цієї формули потрібно вкласти функцію SEARCH, щоб знайти позиції пробілів.

    Формула видобуває шість символів із середини, починаючи з шостого символу.

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

    Скористайтеся функцією SEARCH для пошуку значення для змінної start_num:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (5)

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

    Скористайтеся вкладеною функцією SEARCH для пошуку значення для змінної num_chars:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (5)

  4. Додайте 1, щоб отримати положення символу після першого пробілу (A). Результатом є порядковий номер символу, починаючи з якого потрібно почати пошук другого пробілу. (5 + 1 = 6)

  5. Знайдіть числове положення другого пробілу в клітинці A2, починаючи з шостого символу, знайденого на кроці 4. Цей номер символу – це кінцева позиція прізвища. (12)

  6. Знайдіть порядковий номер положення першого пробілу. (5)

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

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

  9. суфікс

    Суфікс починається з трьох символів справа. Для цієї формули потрібно вкласти функцію SEARCH, щоб знайти позиції пробілів.

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

    Щоб знайти значення для num_chars, використовуйте вкладені функції SEARCH і LEN:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (5)

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

  11. Знайдіть другий пробіл у клітинці A2, починаючи з шостого положення (A), знайденого на кроці 2. (12)

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

У цьому прикладі повне ім'я передує префіксу, і ви використовуєте формули, схожі на приклад 2: функція MID для видобування імені, функція RIGHT для видобування прізвища.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Пан Райан Іріг

З префіксом

Формула

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

'=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))

  1. Ім’я

    Ім'я починається з п'ятого символу зліва (R) і закінчується дев'ятим символом (другим пробілом). Формула вкласти search, щоб знайти позиції пробілів. Видобуває чотири символи, починаючи з п'ятої позиції.

    Формула для відокремлення імені перед префіксом

    Скористайтеся функцією SEARCH, щоб знайти значення для start_num:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (4)

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

    Щоб знайти значення для num_chars, скористайтеся вкладеною функцією SEARCH:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (4)

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

  4. Знайдіть числове положення другого пробілу в клітинці A2, починаючи з п'ятого символу, знайденого на кроках 3 та 4. Це номер символу є кінцевою позицією імені. (9)

  5. Знайдіть перший пробіл. (4)

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

  7. Візьміть номер символу другого пробілу, знайденого на кроці 5, а потім відніміть номер символу "R", знайдений на кроках 6 і 7. Результатом буде кількість символів, які функція MID видобуває з текстового рядка, починаючи з п'ятої позиції, знайденої на кроці 2. (9 - 5 = 4)

  8. Прізвище

    Прізвище починається з п'яти символів справа. Для цієї формули потрібно вкласти функцію SEARCH, щоб знайти позиції пробілів.

    Формула для відокремлення імені перед префіксом

    Щоб знайти значення для num_chars, використовуйте вкладені функції SEARCH і LEN:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (4)

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

  10. Знайдіть другий пробіл у клітинці A2, починаючи з п'ятого положення (R), знайденого на кроці 2. (9)

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

У цьому прикладі використовується прізвище з дефісом. Пробіл відокремлює кожен компонент імені.

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

Підказка.    Перш ніж вставляти дані на аркуш, установіть для ширини стовпців A та B значення 250.

Приклад імені

Опис

Джулі Taft-Rider

Прізвище з переносами

Формула

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

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Результат (прізвище)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Ім’я

    Ім'я починається з першого символу зліва та закінчується шостою позицією (перший пробіл). Формула видобуває шість символів зліва.

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

    Скористайтеся функцією SEARCH, щоб знайти значення num_chars:

    Знайдіть числове положення першого пробілу в A2, починаючи зліва. (6)

  2. Прізвище

    Все прізвище починається з десяти символів справа (T) і закінчується першим символом справа (r).

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

    Скористайтеся функціями LEN і SEARCH, щоб знайти значення для num_chars.

    Знайдіть числове положення пробілу в A2, починаючи з першого символу зліва. (6)

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

Розділення тексту на різні стовпці за допомогою майстра текстів

Потрібна додаткова довідка?

Потрібні додаткові параметри?

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.