1. Приклад 1. Ділимо текст з ПІБ за стовпцями за допомогою формул
2. Приступаємо до поділу першої частини тексту - Прізвища
3. Приступаємо до поділу другій частині тексту - Ім'я
4. Приступаємо до поділу третьої частини тексту - батькові
5. Приклад 2. Як розділити текст по стовпцях в Excel за допомогою формули

Раннє ми розглядали можливість розділити текст по стовпцях на прикладі поділу ПІБ на складові частини. Для цього ми використовували інструмент в Excel «Текст за стовпцями».

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

Приклад 1. Ділимо текст з ПІБ за стовпцями за допомогою формул

Якщо розглядати на прикладі поділу ПІБ, то розділити текст можна буде за допомогою текстових формул Excel, використовуючи функцію ПСТР і ШУКАТИ , Яку ми розглядали в попередніх статтях. У цьому випадку вам достатньо вставити дані в певний стовпець, а формули автоматично розділять текст так як вам необхідно. Давайте приступить до розгляду даного прикладу.

У нас є стовпець зі списком ПІБ, наше завдання розмістити прізвище, ім'я по батькові по окремих стовпців.

Спробуємо дуже докладно описати план дії і розіб'ємо рішення задачі на кілька етапів.

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

Отже, додамо стовпці позиція 1-го і 2-го прогалин. За допомогою функції ШУКАТИ, як ми вже розглядали в попередній статті знайдемо позицію першого прогалин. Для цього в комірці «H2» пропишемо формулу

= ШУКАТИ ( ""; A2; 1)

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

Тепер нам необхідно знайти порядковий номер другого пробілу. Формула буде така ж, але з невеликою відмінністю. Якщо прописати таку ж формулу, то функція знайде нам перший пробіл, а нам потрібен другий пробіл. Значить на необхідно поміняти третій аргумент у функції ШУКАТИ - початкова позиція - то є позиція з якої функція буде шукати шуканий текст. Ми бачимо, що другий пробіл знаходиться в будь-якому випадку після першого пробілу, а позицію першого пробілу ми вже знайшли, значить додавши 1 до позиції першого прогалин ми вкажемо функції ШУКАТИ шукати пробіл починаючи з першої літери після першого пропуску. Функція буде виглядати наступним чином:

= ШУКАТИ ( ""; A2; H2 + 1)

Далі протягуємо формулу і отримуємо позиції 1-го і 2-го пробілу.

Приступаємо до поділу першої частини тексту - Прізвища

Для цього ми скористаємося функцією ПСТР , Нагадаю синтаксис цієї функції:

= ПСТР (текст; начальная_позіція; чісло_знаков), де

1. текст - це ПІБ, в нашому прикладі це осередок A2;
2. начальная_позіція - в нашому випадку це 1, тобто починаючи з першої літери;
3. чісло_знаков - ми бачимо, що прізвище складається з усіх знаків, починаючи з першої літери і до 1-го пробілу. А позиція першого пробілу нам вже відома. Це і буде кількість знаків мінус 1 знак самого пробілу.

Формула буде виглядати наступним чином:

= ПСТР (A2; 1; H2-1)

Приступаємо до поділу другій частині тексту - Ім'я

Знову використовуємо функцію = ПСТР (текст; начальная_позіція; чісло_знаков), де

1. текст - це той же текст ПІБ, в нашому прикладі це осередок A2;
2. начальная_позіція - в нашому випадку Ім'я починається з першої буква після першого пробілу, знаючи позицію цієї прогалини отримуємо H2 + 1;
3. чісло_знаков - число знаків, тобто кількість букв в імені. Ми бачимо, що ім'я у нас знаходиться між двома пробілами, позиції яких ми знаємо. Якщо з позиції другого пробілу забрати позицію першого пробілу, то ми отримаємо різницю, яка і буде дорівнює кількості символів в імені, тобто I2-H2

Отримуємо підсумкову формулу:

= ПСТР (A2; H2 + 1; I2-H2)

Приступаємо до поділу третьої частини тексту - батькові

І знову функція = ПСТР (текст; начальная_позіція; чісло_знаков), де

1. текст - це той же текст ПІБ, в нашому прикладі це осередок A2;
2. начальная_позіція - батькові у нас знаходиться після 2-го прогалин, значить початкова позиція буде дорівнює позиції другого пробілу плюс один знак або I2 + 1;
3. чісло_знаков - в нашому випадку після Вітчизни ніяких знаків немає, тому ми просто може взяти будь-яке число, головне, щоб воно було більше можливої ​​кількості символів в батькові, я взяв цифру з великим запасом - 50

отримуємо функцію

= ПСТР (A2; I2 + 1; 50)

Далі виділяємо все три осередки і простягаємо формули вниз і отримуємо потрібний нам результат. На цьому можна закінчити, а можна проміжні розрахунки позиції прогалин прописати в самі формули розподілу тексту. Це дуже просто зробити. Ми бачимо, що розрахунок першого пробілу знаходиться в осередку H2 - ЗНАЙТИ ( ""; A2; 1), а розрахунок другого пробілу в осередку I2 - ЗНАЙТИ ( ""; A2; H2 +1). Бачимо, що у формулі осередку I2 зустрічається H2 міняємо її на саму формулу і отримуємо в осередку I2 вкладену формулу ШУКАТИ ( ""; A2; ШУКАТИ ( ""; A2; 1) +1)

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

• Прізвище = ПСТР (A2; 1; H2 -1) отримуємо = ПСТР (A2; 1; ШУКАТИ ( ""; A2; 1) -1)
• Ім'я = ПСТР (A2; H2 +1; I2 -H2) ​​отримуємо = ПСТР (A2; ШУКАТИ ( ""; A2; 1) +1;
  ШУКАТИ ( ""; A2; ШУКАТИ ( ""; A2; 1) +1) -Знайти ( ""; A2; 1))
• По батькові = ПСТР (A2; I2 +1; 50) отримуємо = ПСТР (A2; ШУКАТИ ( ""; A2; ШУКАТИ ( ""; A2; 1) +1) +1; 50)

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

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

Приклад 2. Як розділити текст по стовпцях в Excel за допомогою формули

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

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

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

Для початку необхідно знайти спільну розділити, за яким ми будемо розбивати текст. У нашому випадку це кома, але наприклад в першому завданні ми ділили ПІБ і роздільник був пробіл. Наш другий приклад більш універсальний (більш зручний при великій кількості даних), так наприклад ми зручно могли б ділити не тільки ПІБ по окремих осередків, а ціле речення - кожне слово в окрему клітинку. Власне таке питання надійшов в коментарях, тому було вирішено доповнити цю статтю.

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

Тепер основна суть прийому.

Крок 1. У допоміжному стовпці знаходимо позицію першого роздільника за допомогою функції ШУКАТИ. Описувати детально функцію не буду, так як ми вже розглядали її раннє. Пропишемо формулу в D1 і протягнемо її вниз на всі рядки

= ШУКАТИ (B1; A1; 1)

Тобто шукаємо кому, в тексті, починаючи з позиції 1

Крок 2. Далі в осередку E1 прописуємо формулу для знаходження другого знака (в нашому випадку коми). Формула аналогічна, але з невеликими змінами.

= ШУКАТИ ($ B1; $ A1; D1 + 1)

По-перше: закріпимо стовпець шуканого значення і тексту, щоб при протягуванні формули вправо посилання на вічка не порушувався. Для цього потрібно написати долар перед стовпцем B і A - або вручну, або виділити A1 і B1, натиснути три рази клавішу F4, після цього посилання стануть не відносними, а абсолютними.

По-друге: третій аргумент - початок позиції ми розрахуємо як позиція попереднього роздільник (ми його знайшли вище) плюс 1 тобто D1 + 1 так як ми знаємо, що другий роздільник точно знаходиться після першого роздільника і нам його не потрібно враховувати.

Пропишемо формулу і протягнемо її вниз.

Крок 3. Що знаходяться позиції всіх інших роздільників. Для цього формулу знаходження другого роздільник (крок 2) протягнемо вправо на ту кількість осередків, скільки всього може бути окремо розбитих значень з невеликим запасом. Отримаємо всі позиції роздільників. Там де помилка # Знач означає що значення закінчилися і формула більше не знаходить роздільників. отримуємо наступне

Крок 4. Отделяем перше число від текст за допомогою функції ПСТР.

= ПСТР (A1; 1; D1-1)

Початкова позиція у нас 1, кількість знаків ми розраховуємо як позиція першого роздільника мінус 1: D1-1 простягаємо формулу вниз

Крок 5. Що знаходяться друге слово так само за допомогою функції ПСТР в осередку P1

= ПСТР ($ A1; D1 + 1; E1-D1-1)

Початкова позиція другого числа у нас починається після першої коми. Позиція першої коми у нас є в осередку D1, додамо одиницю і отримаємо початкову позицію нашого другого числа.

Кількість знаків це є різниця між позицією третього роздільник і другого і мінус один знак, тобто E1-D1-1

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

Крок 6. Протягнемо формулу отриману на кроці 5 вправо і вниз і отримаємо текст в окремих осередках.

Крок 7. У принципі завдання наша вже вирішена, але для краси все в тій же комірці P1 пропишемо формула відловлювати помилку замінюючи її порожнім значенням. Так само можна згрупувати і згорнути допоміжні стовпці, щоб вони не заважали. Отримаємо підсумкове рішення задачі

= ЕСЛІОШІБКА (ПСТР ($ A1; D1 + 1; E1-D1-1); "")

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

= ШУКАТИ ($ B1; $ A1; C1 + 1)

а першого тексту як

= ПСТР ($ A1; C1 + 1; D1-C1-1)

Після цього можна відразу протягувати формулу на інші значення. Саме цей варіант залишаю як приклад для скачування. В принципі файлом можна користуватися як шаблоном. У стовпець «A» вставляєте дані, в стовпці «B» вказуєте роздільник, протягуєте формули на потрібну кількість осередків і отримуєте результат.

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

Або друге рішення - це на кроці 3, коли ми складаємо формулу обчислення позицій роздільників доповнити її. Зробити перевірку, якщо помилка, то вказуємо свідомо велике число, наприклад 1000.

= ЕСЛІОШІБКА (ШУКАТИ ($ B1; $ A1; C1 + 1), 1000)

Таким чином останній текст буде розраховуватися починаючи від останньої коми до трохи менше 1000 знаків, тобто до кінця рядка, що нам і потрібно.

Обидва варіанти викладу для скачування.

Завантажити приклад: Як розділити текст по стовпцях за допомогою функціі_1.xlsx (Виправлено: доп поле)
Завантажити приклад: Як розділити текст по стовпцях за допомогою функціі_2.xlsx (Виправлено: свідомо велике число)