Розділення тексту на різні стовпці з функціями

Розділення тексту на різні стовпці з функціями

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

Для виконання операцій із рядками тексту в даних можна скористатися функціями LEFT, MID, RIGHT, SEARCH і LEN. Наприклад, можна відокремити прізвище, ім’я та по батькові, що містяться в одній клітинці, серед трьох стовпців.

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

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

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

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

Опис

Ім’я

По батькові

Прізвище

Суфікс

1

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

По батькові не зазначено

Іван

Сміт

2

Ерік Kurjan с.

По батькові зазначено одним ініціалом

Eric

В.

Курганський

3

Janaina b. Буено г.

По батькові зазначено двома ініціалами

Жанна

Б. Г.

Борів

4

Канська, Ольга Степанівна

Прізвище на початку, відокремлене комою

Ольга

Степанівна

Канська

5

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

Складне подвійне ім’я

Анна Яся

Д.

Антонова

6

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

Складне потрійне прізвище

Паула

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

7

Андрій Ткачук-Мірошниченко

Складне подвійне прізвище

Євген

Волове Око

8

Бейкон мол., Ден к.

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

Ден

K.

Бейкон

мол.

9

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

Із суфіксом

Гарі

Альтман

III

10

Г Райан Ihrig

З префіксом

Тарас

Коваленко

11

Марія пані Косач

Подвійне прізвище, яке пишеться через дефіс

Марія

Пані-Косач

Примітка : У цих прикладах графічному об'єкті в повному імені відображається символ, який відповідні ПОШУКУ формули Search для.

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

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

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

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

Опис

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

По батькові не зазначено

Формула

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

'=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 для пошуку значення аргумента «кількість_символів»:

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

  2. Прізвище  

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

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

    Використання функцій SEARCH і LEN для пошуку значення аргументу «кількість_символів»:

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

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

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

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

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

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

Опис

Петро В. Курганський

По батькові зазначено одним ініціалом

Формула

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

'=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. Ім'я  

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

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

    Використання функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

    По батькові починається з 7-го символу з лівого краю (В) та закінчується 9-м (другий пробіл). У цій формулі використано вкладені функції SEARCH для знаходження другого пробілу.

    Ця формула видобуває 3 символи, починаючи з 7-ї позиції.

    формула видобування по батькові у прикладі 2: петро в. курганський

    Скористайтеся функцією SEARCH, щоб знайти значення аргумента «поч_поз»:

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

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

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

    Прізвище починається з 11-го символу з правого краю (К) та закінчується 1-м символом з правого краю (й). У цій формулі використано вкладені функції SEARCH для пошуку другого та третього пробілів (6-та та 9-та позиції зліва).

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

    Функція second ПОШУКУ формула для відокремлення ім'я, по батькові та прізвище
  9. Використання функції LEN і вкладеної функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

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

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

Опис

Жанна Б. Г. Борів

По батькові зазначено двома ініціалами

Формула

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

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

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

Формула

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

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

=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+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. Ім'я  

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

    формула видобування по батькові у прикладі 3: жанна б. г. борів

    Використання функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

    По батькові починається з 7-ї позиції (Б) і закінчується 12-ою (третій пробіл). У цій формулі використано вкладені функції SEARCH для пошуку першого, другого та третього пробілів у 6-й, 9-й та 12-й позиціях.

    Ця формула видобуває 5 символів, починаючи з 7-ї позиції.

    формула видобування по батькові у прикладі 3: жанна б. г. борів

    Скористайтеся функцією SEARCH, щоб знайти значення аргумента «поч_поз»:

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

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

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

  10. Прізвище  

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

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

    формула видобування по батькові у прикладі 3: жанна б. г. борів

    Використання функції LEN і вкладеної функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

Опис

Канська, Ольга Степанівна

Прізвище на початку, відокремлене комою

Формула

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

'=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. Ім'я  

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

    Ця формула видобуває 6 символів, починаючи з 10-ї позиції.

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

    Скористайтеся функцією SEARCH, щоб знайти значення аргумента «поч_поз»:

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

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

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

    По батькові починається з 10-го символу з правого краю (С) і закінчується першим символом із правого краю (а). У цій формулі використано вкладену функцію SEARCH для пошуку першого та другого пробілів, у 9-й та 15-й позиціях, починаючи з лівого краю.

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

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

    Використання вкладеної функції SEARCH і функції LEN для пошуку значення аргумента «поч_поз»:

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

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

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

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

  10. Прізвище  

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

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

    Використання функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

У цьому прикладі використовується ім’я, що складається із двох частин, Mary Kay. Компоненти імені відокремлює другий і третій пробіл.

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

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

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

Опис

Анна Ядвіга Д. Антонова

Складне подвійне ім’я

Формула

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

LEFT(A2, SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

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

Формула

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

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

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

Формула

Поточний результат (прізвище)

'=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

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

  1. Ім'я  

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

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

    формула видобування прізвища у прикладі 5: анна яся д. антонова

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

    формула видобування прізвища у прикладі 5: анна яся д. антонова

    Використання вкладених функцій SEARCH для пошуку значення аргумента «поч_поз»:

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

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

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

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

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

  11. Прізвище  

    Прізвище починається з 8-го символу з правого краю. У цій формулі використано вкладену функцію SEARCH для пошуку першого, другого й третього пробілів у 5-й, 9-й та 12-й позиціях відповідно.

    Формула витягає 8 символів, починаючи з правого краю.

    формула видобування прізвища у прикладі 5: анна яся д. антонова

    Використання функції LEN і вкладеної функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

Опис

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

Складне потрійне прізвище

Формула

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

'=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. Ім'я  

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

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

    Використання функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

  2. Прізвище  

    Прізвище починається з 17 символу з правого краю (Б) і закінчується 1-м символом з правого краю (с). Формула витягає 17 символів, починаючи з правого краю.

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

    Використання функцій SEARCH і LEN для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

Опис

Євген Волове Око

Складне подвійне прізвище

Формула

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

'=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. Ім'я  

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

    формула видобування прізвища у прикладі 7: євген волове око

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

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

  2. Прізвище  

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

    формула видобування прізвища у прикладі 7: євген волове око

    Використання функцій SEARCH і LEN для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

Опис

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

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

Формула

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

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

=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;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. Ім'я  

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

    Формула для відокремлення останні введіть ім'я та суфікс на початку, відокремлені комою

    Використання вкладених функцій SEARCH для пошуку значення аргумента «поч_поз»:

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

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

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

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

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

    Формула для відокремлення останні введіть ім'я та суфікс на початку, відокремлені комою

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

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

    Формула для відокремлення останні введіть ім'я та суфікс на початку, відокремлені комою

    Використання функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

  15. Суфікс

    Суфікс починається 8-м символом з лівого краю (м) та закінчується 11-м символом із лівого краю (крапка). Формула видобуває 4 символи, починаючи з 7-го символу.

    Формула для відокремлення останні введіть ім'я та суфікс на початку, відокремлені комою

    Скористайтеся функцією SEARCH, щоб знайти значення аргумента «поч_поз»:

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

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

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

Опис

Гарі Альтман 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. Ім'я  

    Ім’я починається з першого символу з лівого краю (Г) та закінчується 5-м символом (перший пробіл). Таким чином, формула видобуває 5 символів, починаючи з лівого краю.

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

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

  2. Прізвище  

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

    Формула видобуває 7 символів із середини рядка, починаючи з 6-го символу.

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

    Скористайтеся функцією SEARCH, щоб знайти значення аргумента «поч_поз»:

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

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

    Використання вкладених функцій SEARCH для пошуку значення аргумента «кількість_символів»:

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

    Суфікс починається 3-м символом із правого краю. У цій формулі використано вкладену функцію SEARCH для пошуку пробілів.

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

    Використання функції LEN і вкладеної функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

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

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

Опис

пан Тарас Коваленко

З префіксом

Формула

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

'=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, щоб знайти значення аргумента «поч_поз»:

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

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

    Використання вкладених функцій 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. Прізвище  

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

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

    Використання функції LEN і вкладеної функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

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

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

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

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

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

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

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

Опис

Марія Пані-Косач

Подвійне прізвище, яке пишеться через дефіс

Формула

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

'=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. Ім'я  

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

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

    Використання функції SEARCH для пошуку значення аргумента «кількість_символів»:

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

  2. Прізвище  

    Прізвище починається 10-м символом з правого краю (П) і закінчується 1-м символом з правого краю (ч).

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

    Використання функцій SEARCH і LEN для пошуку значення аргумента «кількість_символів»:

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

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

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

Див. також

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

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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×