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

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

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

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

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

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

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

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

Опис

Ім’я

Середнє ім'я

Прізвище

суфікс

1

Іван Смоленко

Без середнього імені

Валерій

Коваль

2

Ерік с. Курджан

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

Ерік

S.

Kurjan

3

Ячайна б. г. Буено

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

Джанайна

B. Г.

Буено

4

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

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

Ольга

Степанівна

Канська

5

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

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

Mary Kay

D.

Andersen

6

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

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

Паула

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

7

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

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

Джеймс

Ван Ітен

8

Бекон JR., Ден к.

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

Ден

K.

Бейкон

Молодший.

9

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

З суфіксом

Гарі

Альтман

III

10

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

З префіксом

Райан

Ihріг

11

Джулі Тафт – Райдер

Прізвище в дефіс

Юлія

Тафт-Райдер

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

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

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

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

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

Опис

Іван Смоленко

Без середнього імені

Формула

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

' = LEFT (A2; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

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

' = RIGHT (A2; LEN (A2) – ПОШУК (""; 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; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

Результат (середня початкова)

' = MID (A2; ПОШУК (""; A2; 1) + 1; ПОШУК (""; A2; ПОШУК (""; A2; 1) + 1) – ПОШУК (""; A2; 1))

= MID (A2; ПОШУК (""; A2; 1) + 1, ПОШУК ("", A2, SEARCH (""; A2; 1) + 1) – ПОШУК (""; 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. Середнє ім'я

    По-батькові розпочнеться на шостому місці символу (а), а закінчується на восьме положення (друге місце). Ця формула включає вкладення функцій ПОШУКУ, щоб знайти другий екземпляр простору.

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

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

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

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

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

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

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

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

  5. Виконайте пошук другого екземпляра простору в клітинці a2, починаючи з шостого розташування (-ів), знайдених у кроці 4. Цей символ – це кінцева позиція середнього імені. 8

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

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

  8. Прізвище

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

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

    Друга функція ПОШУКУ у формулі для відокремлення перших, середніх і прізвищ
  9. Щоб знайти значення для num_chars, скористайтеся функцією LEN та вкладеним ПОШУКОМ.

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

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

  11. Виконайте пошук другого екземпляра простору в клітинці a2, починаючи з шостого розташування (-ів), знайдених на кроці 2. Цей символ – це кінцева позиція середнього імені. 8

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

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

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

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

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

Опис

Ячайна б. г. Буено

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

Формула

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

' = LEFT (A2; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

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

' = MID (A2; ПОШУК (""; A2; 1) + 1, ПОШУК (""; A2; ПОШУК (""; A2; ПОШУК (""; A2; 1) + 1) – ПОШУК (""; A2; 1))

= MID (A2; ПОШУК (""; A2; 1) + 1, ПОШУК (""; A2; ПОШУК (""; A2; SEARCH (""; A2; 1) + 1) – ПОШУК (""; A2; 1))

Формула

Прямий результат (прізвище)

' = RIGHT (A2; LEN (A2) – ПОШУК (""; A2; SEARCH (""; A2; SEARCH (""; A2; 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 для пошуку значення для змінної 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. Результат – кількість символів, які видобуває в текстовому рядку, починаючи з дев'ятої позиції, знайденої на кроці 2.

  10. Прізвище

    Останнє ім'я – початок п'яти символів із правого (B) і завершується першим символом справа (o). Ця формула включає вкладення ПОШУКУ, щоб знайти перший, другий і третій екземпляри простору.

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

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

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

    Знайдіть порядковий номер положення першого пробілу в 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; ПОШУК (""; A2; 1) + 1; ПОШУК (""; A2; ПОШУК (""; A2; 1) + 1) – ПОШУК (""; A2; 1))

= MID (A2; ПОШУК (""; A2; 1) + 1, ПОШУК ("", A2, SEARCH (""; A2; 1) + 1) – ПОШУК (""; A2; 1))

Формула

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

' = RIGHT (A2; LEN (A2) – SEARCH (""; A2; SEARCH (""; A2; 1) + 1))

= RIGHT (A2; LEN (A2) – SEARCH (""; A2; SEARCH (""; A2; 1) + 1))

Формула

Прямий результат (прізвище)

' = LEFT (A2; ПОШУК (""; 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; ПОШУК (""; A2; 1))

= LEFT (A2; SEARCH (""; A2; ПОШУК (""; A2; 1))

Формула

Результат (середня початкова)

' = MID (A2; ПОШУК (""; A2; SEARCH (""; A2; 1) + 1) + 1, ПОШУК ("", A2, SEARCH (""; A2; 1) + 1)) (1))) (ПОШУК (""); A2; ПОШУК (""; A2; 1) + 1).

= MID (A2; ПОШУК (""; A2; SEARCH (""; A2; 1) + 1) + 1, ПОШУК ("", A2, SEARCH (""; A2; 1) + 1)) (1))) (ПОШУК (""); A2; ПОШУК (""; A2; 1) + 1).

Формула

Прямий результат (прізвище)

' = RIGHT (A2; LEN (A2) – ПОШУК (""; A2; SEARCH (""; A2; SEARCH (""; A2; 1) + 1))

= RIGHT (A2; LEN (A2) – SEARCH (""; A2; SEARCH (""; A2; SEARCH (""; A2; 1) + 1); 1))

  1. Ім’я

    Перше ім'я почнеться з першого символу ліворуч і закінчується на дев'ятому символу (друге місце). Ця формула включає вкладення ПОШУКУ, щоб знайти другий екземпляр простору зліва.

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

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

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

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

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

  3. Виконайте пошук другого екземпляра простору в клітинці a2, починаючи з шостого положення (K), знайденого на кроці 2. Результат – кількість символів, які ЗАЛИШИЛИ з текстового рядка. 9

  4. Середнє ім'я

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

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

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

    Використовуйте вкладені функції ПОШУКУ, щоб знайти значення для аргументу поч _ позиція:

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

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

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

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

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

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

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

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

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

  11. Прізвище

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

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

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

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

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

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

  13. Знайдіть друге місце в клітинці a2, починаючи з шостої позиції (K), знайденого на кроці 2. 9

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

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

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

У цьому прикладі використано прізвище з трьох частин: Barrto de mattos. Перше місце позначає кінець імені, а ім'я – початку прізвища.

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

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

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

Опис

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

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

Формула

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

' = LEFT (A2; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

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

RIGHT (A2; LEN (A2) – ПОШУК (""; A2; 1))

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

  1. Ім’я

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

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

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

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

  2. Прізвище

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

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

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

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

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

У цьому прикладі використано прізвище з двох частин: Van Eaton. Перше місце позначає кінець імені, а ім'я – початку прізвища.

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

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

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

Опис

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

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

Формула

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

' = LEFT (A2; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

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

' = RIGHT (A2; LEN (A2) – ПОШУК (""; 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.

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

Опис

Бекон JR., Ден к.

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

Формула

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

' = MID (A2; ПОШУК (""; A2; ПОШУК (""; A2; 1) + 1) + 1, SEARCH (""; A2; SEARCH (""; A2; 1)): (""; A2; 1) + 1) – SEARCH (""; A2; ПОШУК (""; A2; 0))

= MID (A2; ПОШУК (""; A2; ПОШУК (""; A2; 1) + 1) + 1, SEARCH (""; A2; SEARCH (""; A2; 1)): ""; A2; 1) (1))

Формула

Результат (середня початкова)

' = RIGHT (A2; LEN (A2) – ПОШУК (""; A2; SEARCH (""; A2; SEARCH (""; A2; 1) + 1))

= RIGHT (A2; LEN (A2) – SEARCH (""; A2; SEARCH (""; A2; SEARCH (""; A2; 1) + 1); 1))

Формула

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

' = LEFT (A2; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

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

' = MID (A2; ПОШУК (""; A2; 1) + 1; (ПОШУК (""; A2; SEARCH (""; A2; 1) + 1) – 2) – ПОШУК (""; A2; 1))

= MID (A2; ПОШУК (""; A2; 1) + 1, (SEARCH (""; A2; SEARCH (""; A2; 1) + 1) – 2) – ПОШУК (""; A2; 1))

  1. Ім’я

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

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

    Використовуйте вкладені функції ПОШУКУ, щоб знайти значення для аргументу поч _ позиція:

    Знайдіть числове положення першого пробілу в клітинці 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. Результат – кількість символів, які видобуває в текстовому рядку, починаючи з дванадцятого положення, знайденого на кроці 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. Результат – кількість символів, які видобуває в текстовому рядку, починаючи з сьомого положення, знайденого на кроці 2. (10-7 = 3)

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

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

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

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

Опис

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

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

Формула

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

' = LEFT (A2; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

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

' = MID (A2; ПОШУК (""; A2; 1) + 1; ПОШУК (""; A2; ПОШУК (""; A2; 1) + 1) – (ПОШУК (""; A2; 1))

= MID (A2; ПОШУК (""; A2; 1) + 1, ПОШУК ("", A2, SEARCH (""; A2; 1) + 1) – (ПОШУК (""; A2; 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 для пошуку значення для змінної 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, а потім відніміть кількість символів "а", знайденої в кроків 6 і 7. Результат – кількість символів, які призводять до СЕРЕДИНИ, з текстового рядка, починаючи з шостої позиції, знайденої на кроці 2. (12 – 6 = 6)

  9. суфікс

    Суфікс має три символи, які починаються з правого боку. Ця формула включає вкладення ПОШУКУ, щоб знайти розташування пробілів.

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

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

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

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

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

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

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

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

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

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

Опис

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

З префіксом

Формула

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

' = MID (A2; ПОШУК (""; A2; 1) + 1; ПОШУК (""; A2; ПОШУК (""; A2; 1) + 1) – (ПОШУК (""; A2; 1))

= MID (A2; ПОШУК (""; A2; 1) + 1, ПОШУК ("", A2, SEARCH (""; A2; 1) + 1) – (ПОШУК (""; A2; 1))

Формула

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

' = RIGHT (A2; LEN (A2) – SEARCH (""; A2; SEARCH (""; A2; 1) + 1))

= RIGHT (A2; LEN (A2) – SEARCH (""; A2; SEARCH (""; A2; 1) + 1))

  1. Ім’я

    Перше ім'я почнеться в п'ятому символі зліва (R) і закінчується на дев'ятому символі (друге місце). Для ПОШУКУ розташування пробілів можна знайти формулу. Вона видобуває чотири символи, починаючи з п'ятої позиції.

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

    Скористайтеся функцією ПОШУКУ, щоб знайти значення для аргументу поч _ позиція:

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

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

    Використовуйте функцію вкладеної функції ПОШУКУ, щоб знайти значення для num_chars:

    Знайдіть числове положення першого пробілу в клітинці 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. Результат – кількість символів, які призводять до середнього значення з текстового рядка, починаючи з п'ятого положення, знайденого на кроці 2. (9-5 = 4)

  8. Прізвище

    Останнє ім'я – початок п'яти символів справа. Ця формула включає вкладення ПОШУКУ, щоб знайти розташування пробілів.

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

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

    Знайдіть числове положення першого пробілу в клітинці 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.

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

Опис

Джулі Тафт – Райдер

Прізвище в дефіс

Формула

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

' = LEFT (A2; ПОШУК (""; A2; 1))

= LEFT (A2; ПОШУК (""; A2; 1))

Формула

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

' = RIGHT (A2; LEN (A2) – ПОШУК (""; A2; 1))

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

  1. Ім’я

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

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

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

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

  2. Прізвище

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

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

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

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

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

Див. також

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

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×