Excel має набір функцій TEXT, які можуть творити дива. За допомогою цих функцій можна виконувати всі види операцій з нарізуванням і кубиками тексту.
Одним із поширених завдань для людей, які працюють з текстовими даними, є вилучення підрядка в Excel (тобто отримання psrt тексту з клітинки).
На жаль, в Excel немає функції підрядка, яка б це легко робила. Однак це все ще можна зробити за допомогою текстових формул, а також деяких інших вбудованих функцій Excel.
Давайте спочатку поглянемо на деякі текстові функції, які ми будемо використовувати у цьому підручнику.
Функції Excel TEXT
Excel має ряд текстових функцій, які б спростили вилучення підрядка з вихідного тексту в Excel. Ось текстові функції Excel, які ми будемо використовувати у цьому посібнику:
- Функція RIGHT: Вилучає зазначену кількість символів з правого боку текстового рядка.
- Функція LEFT: Вилучає вказану кількість символів зліва від текстового рядка.
- Функція MID: Вилучає вказану кількість символів із зазначеної початкової позиції в текстовому рядку.
- Функція FIND: знаходить початкове положення зазначеного тексту в текстовому рядку.
- Функція LEN: Повертає кількість символів у текстовому рядку.
Видобування підрядка в Excel за допомогою функцій
Припустимо, у вас є набір даних, як показано нижче:
Це деякі випадкові (але супергеройські) ідентифікатори електронної пошти (за винятком мого), і в наведених нижче прикладах я покажу вам, як витягти ім’я користувача та доменне ім’я за допомогою текстових функцій у Excel.
Приклад 1 - Вилучення імен користувачів з ідентифікаторів електронної пошти
Під час використання текстових функцій важливо визначити шаблон (якщо він є). Це робить дійсно легким побудову формули. У наведеному вище випадку шаблон є знаком @ між ім'ям користувача та іменем домену, і ми будемо використовувати його як посилання для отримання імен користувачів.
Ось формула для отримання імені користувача:
= ВЛІВО (A2, FIND ("@", A2) -1)
У наведеній вище формулі використовується функція LEFT для вилучення імені користувача шляхом визначення позиції знака @ в ідентифікаторі. Це робиться за допомогою функції FIND, яка повертає позицію @.
Наприклад, у випадку [email protected], FIND (“@”, A2) поверне 11, що є його позицією у текстовому рядку.
Тепер ми використовуємо функцію LEFT для вилучення 10 символів зліва від рядка (на один менше значення, яке повертає функція LEFT).
Приклад 2 - Вилучення доменного імені з ідентифікаторів електронної пошти
Для отримання доменного імені може бути використана та сама логіка, яка використовується у наведеному вище прикладі. Незначна відмінність тут полягає в тому, що нам потрібно витягти символи з правого боку текстового рядка.
Ось формула, яка дозволить це зробити:
= ПРАВО (A2, LEN (A2) -FIND ("@", A2))
У наведеній вище формулі ми використовуємо ту саму логіку, але відрегулюємо її, щоб переконатися, що ми отримуємо правильний рядок.
Давайте знову візьмемо приклад [email protected]. Функція FIND повертає позицію знака @, яка в цьому випадку дорівнює 11. Тепер нам потрібно вилучити всі символи після @. Тому ми визначаємо загальну довжину рядка і віднімаємо кількість символів до @. Він дає нам кількість символів, які охоплюють доменне ім’я справа.
Тепер ми можемо просто скористатися функцією ПРАВО для отримання доменного імені.
Приклад 3 - Вилучення доменного імені з ідентифікаторів електронної пошти (без .com)
Щоб витягти підрядок із середини текстового рядка, вам потрібно визначити положення маркера безпосередньо перед і після підрядка.
Наприклад, у наведеному нижче прикладі, щоб отримати доменне ім’я без частини .com, маркером буде @ (що знаходиться безпосередньо перед доменним іменем) та. (що відразу після нього).
Ось формула, яка буде видобувати лише доменне ім'я:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Функція Excel MID витягує зазначену кількість символів із зазначеної вихідної позиції. У наведеному вище прикладі FIND (“@”, A2) +1 визначає початкове положення (яке знаходиться відразу після@), а FIND (“.”, A2) -FIND (“@”, A2) -1 визначає кількість символів між@"І".‘
Оновлення: Один із читачів William19 згадував, що вищенаведена формула не працюватиме, якщо в ідентифікаторі електронної пошти є крапка (.) (Наприклад, [email protected]). Отже, ось формула для вирішення таких випадків:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Використання тексту в стовпцях для вилучення підрядка в Excel
Використання функцій для вилучення підрядка в Excel має перевагу, оскільки є динамічним. Якщо змінити вихідний текст, формула автоматично оновить результати.
Якщо це те, що вам може не знадобитися, то використання функції «Текст у стовпці» може бути швидким і простим способом поділу тексту на підрядки на основі зазначених маркерів.
Ось як це зробити:
- Виділіть клітинки, у яких у вас є текст.
- Перейдіть до Дані -> Інструменти даних -> Текст у стовпці.
- У кроці 1 майстра перетворення тексту до стовпця виберіть Розділений та натисніть Далі.
- На кроці 2 встановіть прапорець Інше та введіть @ у полі праворуч від нього. Це буде нашим роздільником, який Excel буде використовувати для поділу тексту на підрядки. Попередній перегляд даних можна побачити нижче. Натисніть Далі.
- На кроці 3 загальні налаштування добре працюють у цьому випадку. Однак ви можете вибрати інший формат, якщо розділяєте числа/дати. За замовчуванням осередок призначення - це місце, де зберігаються вихідні дані. Якщо ви хочете зберегти вихідні дані недоторканими, змініть це на іншу клітинку.
- Натисніть кнопку Готово.
Це миттєво дасть вам два набори підрядків для кожного ідентифікатора електронної пошти, що використовується у цьому прикладі.
Якщо ви хочете ще більше розділити текст (наприклад, розділити batman.com на batman і com), повторіть той самий процес із ним.
Використання FIND і REPLACE для вилучення тексту з клітинки в Excel
Знайти і замінити може бути потужною технікою під час роботи з текстом у Excel. У наведених нижче прикладах ви дізнаєтесь, як використовувати FIND та REPLACE із символами підстановки, щоб робити дивовижні речі в Excel.
Дивитися також: Дізнайтеся все про символи підстановки в Excel.
Візьмемо ті ж приклади ідентифікаторів електронної пошти.
Приклад 1 - Вилучення імен користувачів з ідентифікаторів електронної пошти
Нижче наведено кроки для вилучення імен користувачів з ідентифікаторів електронної пошти за допомогою функцій пошуку та заміни:
- Скопіюйте та вставте вихідні дані. Оскільки функція "Знайти та замінити" працює та змінює дані, до яких вона застосовується, найкраще мати резервну копію вихідних даних.
- Виберіть дані та перейдіть до Домашня сторінка -> Редагування -> Знайти та вибрати -> Замінити (або скористайтеся комбінацією клавіш Ctrl + H).
- У діалоговому вікні Знайти та замінити введіть наступне:
- Знайти що: @*
- Замінити на: (залиште це поле порожнім)
- Натисніть Замінити все.
Це миттєво видалить весь текст перед символом @ в ідентифікаторах електронної пошти. Ви отримаєте результат, як показано нижче:
Як це працює ?? - У наведеному вище прикладі ми використовували комбінацію @ та *. Зірочка (*) - це символ підстановки, який представляє будь -яку кількість символів. Отже, @* означатиме текстовий рядок, який починається на @ і може мати будь -яку кількість символів після нього. Наприклад, у [email protected], @* буде @batman.com. Коли ми замінюємо @* порожнім, він видаляє всі символи після @(включаючи @).
Приклад 2 - Вилучення доменного імені з ідентифікаторів електронної пошти
За допомогою тієї ж логіки ви можете змінити критерій "Знайти що", щоб отримати доменне ім'я.
Ось кроки:
- Виберіть дані.
- Перейдіть додому -> Редагування -> Знайти та вибрати -> Замінити (або скористайтеся комбінацією клавіш Ctrl + H).
- У діалоговому вікні Знайти та замінити введіть наступне:
- Знайти що: *@
- Замінити на: (залиште це поле порожнім)
- Натисніть Замінити все.
Це миттєво видалить весь текст перед символом @ в ідентифікаторах електронної пошти. Ви отримаєте результат, як показано нижче:
