розділення тексту в Microsoft Excel

Зміст

• Спосіб 1: використання автоматичного інструменту
• Спосіб 2: Створення формули розділення тексту
  • Крок 1: розділення першого слова
  • Крок 2: розділення другого слова
  • Крок 3: розділення третього слова
• Питання та відповіді

Спосіб 1: використання автоматичного інструменту

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

1. З затиснутою лівою кнопкою миші виділіть всі осередки, текст яких хочете розділити на стовпці.

2. Після цього перейдіть на вкладку &171;дані&187; і натисніть кнопку &171;Текст за стовпцями&187; .

3. З'явиться вікно &171;майстри розділення тексту за стовпцями&187; , в якому потрібно вибрати формат даних &171; з роздільниками&187; . Роздільником найчастіше виступає пробіл, але якщо це інший розділовий знак, знадобиться вказати його в наступному кроці.

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

5. У завершальному кроці можна вказати новий формат стовпців і місце, куди їх необхідно помістити. Як тільки Налаштування буде завершено, натисніть & 171; Готово&187; для застосування всіх зміни.

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

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

Спосіб 2: Створення формули розділення тексту

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

Крок 1: розділення першого слова

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

1. Для зручності створимо три нові стовпці з підписами, куди будемо додавати розділений текст. Ви можете зробити так само або пропустити цей момент.



2. Виберіть клітинку, де ви хочете розташувати перше слово, і запишіть формулу =ЛЕВСИМВ( .

3. Після цього натисніть кнопку &171;аргументи функції & 187; , перейшовши тим самим в графічне вікно редагування формули.

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

5. Кількість знаків до пробілу або іншого роздільника доведеться порахувати, але вручну ми це робити не будемо, а скористаємося ще однією формулою & 8212; пошук() .

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

7. У полі & 171; Шуканий_текст & 187; просто поставте пробіл або використовуваний роздільник, оскільки це допоможе зрозуміти, де закінчується слово. В & 171; Текст_для_пошуку & 187; вкажіть ту саму оброблювану клітинку.

8. Натисніть на першу функцію, щоб повернутися до неї, і додайте в кінці другого аргументу -1 . Це необхідно для того, щоб формулою & 171; пошук&187; враховувати не шуканий пробіл, а символ до нього. Як видно на наступному скріншоті, в результаті виводиться прізвище без будь-яких пробілів, а це значить, що складання формул виконано правильно.

9. Закрийте редактор функції і переконайтеся в тому, що слово коректно відображається в новій комірці.

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

Повністю створена формула має вигляд =ЛЕВСИМВ(A1; пошук ( "" ; A1)-1) , Ви ж можете створити її за наведеною вище інструкції або вставити цю, якщо умови і роздільник підходять. Не забувайте замінити оброблювану осередок.

Крок 2: розділення другого слова

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

1. У цьому випадку основною формулою стане =ПСТР( &8212; запишіть її в такому вигляді, а потім переходите до вікна налаштування аргументів.



2. Дана формула буде шукати потрібний рядок в тексті, в якості якого і вибираємо осередок з написом для поділу.

3. Початкову позицію рядка доведеться визначати за допомогою вже знайомої допоміжної формули пошук() .

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

5. Поверніться до попередньої формули, де додайте до функції & 171; пошук&187; +1 в кінці, щоб починати рахунок з наступного символу після знайденого пробілу.

6. Зараз формула вже може почати пошук рядка з першого символу імені, але вона поки ще не знає, де його закінчити, тому в поле &171; Кількість_знаків&187; знову впишіть формулу пошук() .

7. Перейдіть до її аргументів і заповніть їх у вже звичному вигляді.

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

9. Перейдіть до створеної функції та заповніть її таким же чином.

10. Поверніться до першого"пошуку" і допишіть в &171; Нач_позиція & 187; +1 в кінці, адже для пошуку рядка потрібен не пробіл, а наступний символ.

11. Натисніть на корінь =PSTR і поставте курсор в кінці рядка & 171; Кількість_знаків&187; .

12. Допишіть там вираз - пошук ( "" ; A1)-1) для завершення розрахунків пробілів.

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

Формула вийшла велика, і не всі користувачі розуміють, як саме вона працює. Справа в тому, що для пошуку рядка довелося використовувати відразу кілька функцій, що визначають початкові і кінцеві позиції пробілів, а потім від них віднімався один символ, щоб в результаті ці самі прогалини не відображалися. В результаті формула така: =ПСТР (A1; пошук ( ""; A1) + 1; пошук ( ""; A1; пошук ( ""; A1) + 1) - пошук ( "" ; A1) -1) . Використовуйте її як приклад, замінюючи номер комірки з текстом.

Крок 3: розділення третього слова

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

1. У порожній клітинці для розташування майбутнього тексту напишіть =ПРАВСИМВ( і перейдіть до аргументів цієї функції.



2. В якості тексту вказуйте клітинку з написом для поділу.

3. Цього разу допоміжна функція для пошуку слова називається ДЛСТР (A1) , де A1 &8212; та сама клітинка з текстом. Ця функція визначає кількість знаків в тексті, а нам залишиться виділити тільки відповідні.

4. Для цього додайте -пошук() і перейдіть до редагування цієї формули.

5. Введіть вже звичну структуру для пошуку першого роздільника в рядку.

6. Додайте для початкової позиції ще один пошук() .

7. Йому вкажіть ту ж саму структуру.

8. Поверніться до попередньої формули &171;пошук&187; .

9. Додайте для його початкової позиції +1 .

10. Перейдіть до кореня формули ПРАВСИМВ і переконайтеся в тому, що результат відображається правильно, а вже потім підтверджуйте внесення змін. Повна формула в цьому випадку виглядає як =ПРАВСИМВ(A1;ДЛСТР(A1)-пошук(" ";A1;пошук(" ";A1)+1)) .

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